Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Location
    Berkeley, CA
    Posts
    2

    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"?>
    <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


  2. #2
    Join Date
    Dec 2005
    Location
    Berkeley, CA
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •