On 4/27/15 11:26 PM, Rohini T Nagaraj wrote:
> Hi,
> We are using the DBCP -org.apache.commons-dbcp-1.2.2.jar for connection
> pooling.
I don't think so. You are using RS.isClosed below, which is JDK
1.6+. So assume you are using DBCP 1.4? Pls verify DBCP and pool
version.
> 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
I am having a hard time piecing this together. Pls include omitted
code. Does the code call rs.next within the body of the loop as well?
Phil
>
>
> 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]
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
