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 <[email protected]>
To: Commons Users List <[email protected]>
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: [email protected]
For additional commands, e-mail: [email protected]
