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"?>
<datasources>
<local-tx-datasource>
<jndi-name>TestDS</jndi-name>
<connection-url>jdbc:db2:mydb2y</connection-url>
<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>10</idle-timeout-minutes>
<max-pool-size>20</max-pool-size>
<min-pool-size>10</min-pool-size>
<!-- Use the security domain defined in conf/login-config.xml -->
<security-domain>TestDbRealm</security-domain>
</local-tx-datasource>
</datasources>

Any help is greatly appreciated.

Ken

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?

Thanks, ken