Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

Stored Procedure Usage in Adobe LiveCycle ES

fireworks12
Level 2
Level 2

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?

9 Replies
_Nith_
Level 10
Level 10

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
Level 10
Level 10

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
Level 10
Level 10

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
Level 10
Level 10

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
Level 10
Level 10

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_
Level 10
Level 10

Please post your java code for a review.

Nith

Jasmin_Charbonn
Level 10
Level 10

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
Level 10
Level 10

Jasmin,

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

Thanks

Srini

YaserMM
Level 1
Level 1

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();