Stored Procedure Usage in Adobe LiveCycle ES

[fireworks12]

We have a number of stored procedures in production that we would like to use with LC.  I reviewed the JDBC types and I don't see any way to get LC to accept the results of a call to a stored procedure, the stored procedure service only has an output for the number of rows affected.  In fact the only JDBC service I see that even handles results from a data base is the Multi Query XML one.

Is it possible to use stored procedures that return actual results in LC and if so how?

[_Nith_]

I'm not sure about this, but you can try to use the Multi Query component as:

{CALL SP_NAME(param1,param2)}

Nith

[Jasmin_Charbonn]

You are correct. LC can get the results of a stored procedure.

This has been brought up to our attention and we're looking into including this functionality in the future.

In the mean time you can use the Script service or use a built a custom component.

Sorry.

Jasmin

[Srini_Dhulipall]

Hi Jasmin,

    Do you mean to say that LC can get the return Parameter value from a function written in Oracle? or this is an issue and we can expect this in the future release?

    We are trying to make a call to a Oracle function which accepts one STRING IN parameter and one CLOB datatype as IN parameter and returns a STRING return parameter.We have written a simple java code to achieve this but we are still having issues.

Thanks

Srini

[Jasmin_Charbonn]

I'm really sorry. I made a typo.

I meant to say "LC can't get the results of a stored procedure"!

Jasmin

[Srini_Dhulipall]

Thank you Jasmine for the clarification.. Do you know of any alternative way of capturing the return parameter value from the Stored Procedure? We are trying to put the java code in the execute script service but we are still experiencing issues.

Thanks

Srini

[_Nith_]

Please post your java code for a review.

Nith

[Jasmin_Charbonn]

I've done this in a Script before and it worked.

Here's an example:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.sql.CallableStatement;

CallableStatement proc_stmt = null;

InitialContext context = new InitialContext();
Connection connection = ((DataSource)context.lookup("java:/IDP_DS")).getConnection();

proc_stmt = connection.prepareCall("{ call GetAllItems() }");

try {
ResultSet results = proc_stmt.executeQuery();

int recordCount = 0;
while(results.next())
{

     recordCount++;
}

System.out.println("Exectued the query fine with number record: " + recordCount);

} catch(Exception ex) {
     ex.printStackTrace();
}
results.close();
proc_stmt.close();
connection.close();

[Srini_Dhulipall]

Jasmin,

          Thank you for your help and time..I will try this code and let you know the progress.

Thanks

Srini

[YaserMM]

Hello Jasmin

The "Results" variable need to declared out of the try and catch block, otherwise it won't be recognized when it comes to "close" at the end.

Here is a modified version of your script.

Greetings,

Yasser

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import java.sql.CallableStatement;

CallableStatement proc_stmt = null;

InitialContext context = new InitialContext();
Connection connection = ((DataSource)context.lookup("java:/IDP_DS")).getConnection();

proc_stmt = connection.prepareCall("{ call CalculateTotal() }");

ResultSet results;
try
{
results = proc_stmt.executeQuery();
if (results.next())
  patExecContext.setProcessDataStringValue("/process_data/@Total",results.getString(1));

}
catch(Exception ex)
{
     ex.printStackTrace();
}
results.close();
proc_stmt.close();
connection.close();