Help - JDBC, ConnectionPool and Exhausted Resultsets

From: The Nomad <nobody_at_nowhere.com>
Date: Fri, 24 Aug 2001 06:46:04 GMT
Message-ID: <MKmh7.60963$2d6.10405988@typhoon.tampabay.rr.com>

Hey all,

I'm currently using Oracle 8i, and I have an application that is using the built-in Oracle Connection Pool classes (OracleConnectionPoolDataSource). All of my retrievals in the database are via stored procedures.

I have a situation where I need to call a stored procedure while processing a resultset (a returned cursor from a stored function). But, if I'm using Connection Pooling, Oracle fails with the following:

SQLMSG: Exhausted Resultset
VENDOR: 17011 However, if I'm not using connection pools, this works fine.

Here is a *contrived* example that illustrates the problem:

  public Map getUserIds() throws RepositoryException{     Map rtn = null;
    try {

      Connection con = JNDIConnectionPool.getPooledConnection(driverName);
      try {
        CallableStatement stmt = con.prepareCall(GETUIDS);
        try {
          setupStmt(stmt);
          ResultSet rs = processQuery(stmt);
          try {
            rtn = new HashMap();
            long tmpInt;
            while (rs.next()) {
              tmpInt = rs.getInt(1);
              String uName = getUserName(tmpInt);
              rtn.put(uName, tmpInt);
            }
          } finally {
            rs.close();
          }
        } finally {
          stmt.close();
        }
      } finally {
        con.close();
      }


    } catch (SQLException ex) {

        outSqlException(ex, "Getting Uids");


    } catch (JNDIConnectionPoolException ex) {

      throw new RepositoryException("No Connection");

    }
    return rtn;
  }

  public String getUserName(int usrId) throws RepositoryException{     String result = null;

     try {
      Connection con =
          JNDIConnectionPool.getPooledConnection(driverName);
        try {
          CallableStatement stmt =
              con.prepareCall("{call USERMAP_GETUSERNAME(?, ?)}");
          try {
            stmt.setInt(1, usrId);
            stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
            stmt.executeUpdate();
            result = stmt.getString(2);
            if ( stmt.wasNull() ) {
              result = null;
            }
          } finally {
            stmt.close();
          }
      } finally {
        con.close();
      }

    }
    catch (SQLException ex) {

        outSqlException(ex, "Getting User Name");     }
    catch (JNDIConnectionPoolException ex) {       throw new RepositoryException("No Connection");     }
   return result;
  }

---

CREATE OR REPLACE FUNCTION GETUIDS RETURN TYPESPKG.USERIDSCURTYPE AS   c1 TYPESPKG.USERIDSCURTYPE;
BEGIN
  OPEN c1 FOR SELECT USERID FROM USERMAP;   RETURN c1;
END;
/

CREATE OR REPLACE PROCEDURE GETUSERNAME(IUSERID IN USERMAP.USERID%TYPE,     UNAME OUT USERMAP.USERNAME%TYPE) AS
BEGIN
  SELECT USERNAME INTO UNAME FROM USERMAP WHERE USERID = IUSERID; EXCEPTION
  WHEN NO_DATA_FOUND THEN
    UNAME := NULL;
END;
/

---

Can anybody help?

Thanks,

    Marc Received on Fri Aug 24 2001 - 01:46:04 CDT