Mailing-List: contact user-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Commons Users List" <user@commons.apache.org>
Received-SPF: pass (athena.apache.org: message received from 54.191.145.13
 which is an MX secondary for user@commons.apache.org)
In-Reply-To: <5537A2F4.4030304@gmail.com>
References: 
 <OF77A65094.47B7FE09-ON65257E2F.00414893-65257E2F.0043A2D8@in.ibm.com>
 <5537A2F4.4030304@gmail.com>
To: "Commons Users List" <user@commons.apache.org>
MIME-Version: 1.0
Subject: Re: [dbcp]Closed Connection or Exhausted Resultset error
Message-ID: 
 <OF57905704.DF53AFC2-ON65257E35.0021A1AF-65257E35.00235B9A@in.ibm.com>
From: Rohini T Nagaraj <rohini.tn@in.ibm.com>
Date: Tue, 28 Apr 2015 11:56:16 +0530
Content-Type: multipart/alternative;
 boundary="=_alternative 00235B9865257E35_="

--=_alternative 00235B9865257E35_=
Content-Type: text/plain; charset="US-ASCII"

Hi,
We are using the DBCP -org.apache.commons-dbcp-1.2.2.jar for connection 
pooling. Connection pool is created as shown in below code and had set the 
connection pool setting  (removed evictable time settings). There is no 
issue in getting a connection. This part of the code works fine.

          GenericObjectPool connectionPool = new GenericObjectPool();
          connectionPool.setMaxActive(25); 
        connectionPool.setMaxIdle(25); 
        connectionPool.setTestOnReturn(false);
        connectionPool.setTestOnBorrow(true);
        connectionPool.setTestWhileIdle(false);
        GenericKeyedObjectPoolFactory statementPoolFactory = new 
GenericKeyedObjectPoolFactory(null
,-1,GenericKeyedObjectPool.WHEN_EXHAUSTED_FAIL,0,1,dbEndPt.getMaxPreparedStatement());
        ConnectionFactory connectionFactory = new 
DataSourceConnectionFactory(ds);
        boolean defaultReadOnly = false;
        CIPoolableConnectionFactory poolableConnectionFactory = new  
CIPoolableConnectionFactory(connectionFactory,connectionPool,statementPoolFactory,getValidationQuery(dbEndPt),defaultReadOnly,
false); 
        PoolingDataSource pds = new 
DelegatePoolingDataSource(connectionPool);
        pds.setAccessToUnderlyingConnectionAllowed(true);
          pds.getConnection();


Later when we run this part of the code , it takes almost 12mins to get 
the resultset. Since customer has 37k packages ,it might have taken this 
time. But after that we get the error as Closed Resultset and it does not 
enter the while loop.This happens only when we use datasource connection 
pooling. Otherwise this part of the code works fine in a standalone 
program and takes 10mins to get the resultset.Please suggest us connection 
pool settings to handle the 37k packages on the Oracle side.Thanks.

                ResultSet rs = metaData.getProcedureColumns(package_name, 
schema, storedProcedure, null ); 
 
 
              logger.warning("Got resultset.Is ResultSet closed = "
+rs.isClosed()+"\n");

              if (rs != null){

 
                  logger.warning("rs is not equal to NULL.Is ResultSet 
closed = "+rs.isClosed()+"\n");

              while( rs.next() ) {

                        //code to get the params
                }

ERROR:
Apr 27, 2015 9:17:36 AM com.approuter.framework.util.ResourceBundleHelper 
getString
WARNING: Could not find the resource menu.edit.label in bundle: 
orchestration using the resource bundle service. Fallback to old style

Apr 27, 2015 9:29:07 AM 
com.approuter.studio.connectors.database.util.DBHelper 
getStoredProcedureMetaData
WARNING: Got resultset.Is ResultSet closed = false

Apr 27, 2015 9:29:07 AM 
com.approuter.studio.connectors.database.util.DBHelper 
getStoredProcedureMetaData
WARNING: rs is not equal to NULL.Is ResultSet closed = true

java.sql.SQLException: Closed Resultset: next
                 at 
oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:565)
                 at 
com.approuter.studio.connectors.database.util.DBHelper.getStoredProcedureMetaData(DBHelper.java:1052)//this 
line points to the whileloop


Thanks and Regards,
Rohini T Nagaraj,
WebSphere CastIron QA Team,
 IBM INDIA PRIVATE LIMITED,
DC1-3A-003,DLF IT PARK,Chennai - 600089
Extn # : 21820 and Mobile #: 9962020675


From:   Phil Steitz <phil.steitz@gmail.com>
To:     Commons Users List <user@commons.apache.org>
Date:   04/22/2015 07:03 PM
Subject:        Re: [dbcp]Closed Connection or Exhausted Resultset error


On 4/22/15 5:18 AM, Rohini T Nagaraj wrote:
> Hi All,
>
> The Oracle DB has 37k packages. The issue is seen after running the 
below 
> query from the program or when using 
> DatabaseMetadata.getProcedureColumns()  in a program . We are seeing 
these 
> errors  "Exhausted Resultset (SQL Code =99999 and vendor code=17,011) " 
or 
> "Closed Connection :next (SQL code=08003 , Vendor code=17,008)".

Please post a stack trace showing one or both of these errors.
>
> Something happening to connection or resultset getting closed after 
> running the query/getProcedureColumns call. We have investigated further 

> and see this issue of Exhausted Resultset/Connection getting closed only 

> when we use the datasource connection pool logic.
> When we run a standalone java program with the query/getProcedureColumns 

> call , it took 10mins for the customer to get the output and there were 
no 
> issues.We want the same behavior with datasource connection pool logic .
>
> 
> We are using Tomcat sevrer and 
> org.apache.commons.pool.impl.GenericObjectPool connection pool.We have 
> these connection pool settings done. 

Are you using DBCP or are you just trying to pool the connections
manually using Commons Pool?  What version of pool and / or DBCP? 
How exactly are you creating the connection pool?
>
> connectionPool.setMaxActive(25);
> connectionPool.setMaxIdle(1);
> connectionPool.setMinEvictableIdleTimeMillis(120000);
> connectionPool.setTimeBetweenEvictionRunsMillis(1000);
> connectionPool.setTestOnReturn(false);
> connectionPool.setTestOnBorrow(true);
> connectionPool.setTestWhileIdle(false); 

Why do you have maxIdle set to 1? That is going to cause connections
to get closed when they are returned whenever there is one idle
connection already in the pool.  This will effectively defeat the
purpose of the pool.  Also, with such rigid control on idle
connections, why do you also have the evictor configured to run? 
Every second?  Unless you have special reasons for these settings, I
would change maxIdle to 25 and drop the minEvictableIdleTimeMillis
and timeBetweenEvictionRunsMillis (default will be to have no
evictor runs).
>
> Hence please let us know if there is any fine tuning to be done to make 
> query or DatabaseMetadata.getProcedureColumns() to work when we use 
> datasource connection pool logic.Please suggest us any other timeout or 
if 
> there is anything else which can help us in this scenario.Thanks
> 
>
> SQL QUERY :
> SELECT * FROM SYS.ALL_ARGUMENTS WHERE PACKAGE_NAME = 'PKG_TEST' AND 
> OBJECT_NAME = 'PRC_INSERT' AND OWNER = 'APPS' ORDER BY SEQUENCE
>
> ERROR:
> java.sql.SQLRecoverableException: Closed Connection: next
> at 
> 
oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:233)
>
>
>
> Thanks and Regards,
> Rohini T Nagaraj,
> WebSphere CastIron QA Team,
>  IBM INDIA PRIVATE LIMITED,
> DC1-3A-003,DLF IT PARK,Chennai - 600089
> Extn # : 21820 and Mobile #: 9962020675


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


--=_alternative 00235B9865257E35_=--