Unanswered: Stored Procedure returning Empty Result Set
I hope someone can help, please.
I am running an application on Linux in Jboss 4 (with Tomcat 5) and using DB2Connect to run stored procedures. I can execute the stored procedure one time successfully. The next time I try to execute it (in the web app), I get this error:
18:44:32,862 ERROR [StudentDaoDB2Impl] Caught SQLException: java.sql.CallableStatement.executeQuery() was called but no result set was returned. Use java.sql.CallableStatement.executeUpdate() for non-queries.
If I use the jmx-console to flush the connection pool, or change the idle timeout to a shorter time and wait for it to time out, then I can use the stored procedures again. I've tried fooling around with holdability, auto commit, transaction isolation with no success. Auto commit returns true, holdability is HOLD_CURSORS_OVER_COMMIT and transaction isolation is READ_COMMITTED.
I appears that the connections are not being re-used maybe?
Here is my jdbc datasource:
<?xml version="1.0" encoding="UTF-8"?>
<!-- Use the security domain defined in conf/login-config.xml -->
More information. This is DB2 v 8.1 - I'm not sure what the fixpack is.
I've tried putting into my dao subsequent try blocks that execute the stored procedure. I close all (rs, stmt, conn) and then set it all up to run again after it has successfully ran. -- It gives me the same error Empty ResultSet returned. It's as though DB2 Connect is still holding onto the old ResultSet -- perhaps between the DB2Connect machine and the DB2 Database on the mainframe?
If I don't release the connection, then each time the class that executes the stored procedure is run, it succeeds by getting the next connection -- until all connections have been used - as suspected.
Any ideas why the connection can't be reused even when it's been closed? Do I need some other parameters on the connect string or more attributes in the datasource?