Stored Procedures in ES2

[JoelHH]

Hello,

I tried using the Call Stored Procedure service from JDBC > Foundation in Livecycle ES, but was unable to get a return value from them.  There is a forum post about someone having a similar problem here: http://forums.adobe.com/message/2325143 , so I ended up going the execute script route and it worked fine.

I'm upgrading from ES to ES2 and found that the script that I made no longer works.  The resultset that is being returned is empty and an error is thrown.  If I put the resultset into a try, catch block, I can see that the stored procedure runs on the server but doesn't receive anything (so I know the connection to the database and calling the stored procedure both are working).  I tried playing around with the resultset but am not getting anywhere.  Was there some sort of change to the execute script in ES2 or has there been a change in Java in 1.6.0.14? 

Also, I tried using the Call Stored Procedure service again in ES2 but it looks like it works in the exact same way as in ES.

Any help would be greatly appreciated.

[Jasmin_Charbonn]

"I'm upgrading from ES to ES2 and found that the script that I made nolonger works.  The resultset that is being returned is empty and  an error is thrown"

What is the error?

The code in the Execute Script service is just standard JDBC code. I don't believe anything has changed with the Execute Script service.

It could be beacuse the java version. Can you run the same code in a plain java application and see if you get the same results?

Jasmin

[JoelHH]

The error that I receive is the following:

java.lang.RuntimeException: Sourced file: inline evaluation of: ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.S . . . '' : Typed variable declaration : Method Invocation proc_stmt.executeQuery : at Line: 15 : in file: inline evaluation of: ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.S . . . '' : proc_stmt .executeQuery ( )

Target exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

.....

Caused by: Sourced file: inline evaluation of: ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.S . . . '' : Typed variable declaration : Method Invocation proc_stmt.executeQuery : at Line: 15 : in file: inline evaluation of: ``import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.S . . . '' : proc_stmt .executeQuery ( )

Target exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

....

Line 15 is where the executeQuery() takes place in the Java code.

I am going to have to adjust the java code in order to test it in a seperate application because it is using a context lookup in order to get the datasource.  The code that I am using is similar to the code that you posted http://forums.adobe.com/message/2326314#2326314 .  I will do that and let you know of the result.  If that is the case, do you know of another way to be able to call the stored procedure from execute script that would work?

Thanks.

[Jasmin_Charbonn]

If it works from the standalone application, then you could built a custom component to make the call.

I'm still not sure as to why the code would work in a stand alone app and not in the Execute Script, but if it's the case, then the custom component should give you the same result as the stand alone app.

Let me know and if it works, then we can discuss more about custom components.

Jasmin

[JoelHH]

I was able to figure out what the problem is when testing from a standalone application.  I tried using different versions of Java and JDBC and found that it was a problem with JDBC.  I was originally using the JDBC driver 1.1, but all later versions have this in their release notes:

AVOID CREATING TEMPORARY TABLES via PreparedStatement or CallableStatement CLASSES

If the application creates temporary tables via PreparedStatement or CallableStatement when executing a query, these temporary tables may be dropped after the query executes.

To work around this issue, either execute such queries by using the Statement class or; if you must use the PreparedStatement class or the CallableStatement class, create a table in the database and delete that table when it is no longer needed.

The stored procedure I had was returning a temporary table that had the data in it.  Changing the stored procedure to use output variables fixed the problem.

Thanks for your help Jasmin.