You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Rohini T Nagaraj <ro...@in.ibm.com> on 2015/04/22 14:18:48 UTC
[dbcp]Closed Connection or Exhausted Resultset error
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)".
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.
connectionPool.setMaxActive(25);
connectionPool.setMaxIdle(1);
connectionPool.setMinEvictableIdleTimeMillis(120000);
connectionPool.setTimeBetweenEvictionRunsMillis(1000);
connectionPool.setTestOnReturn(false);
connectionPool.setTestOnBorrow(true);
connectionPool.setTestWhileIdle(false);
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
Re: [dbcp]Closed Connection or Exhausted Resultset error
Posted by Phil Steitz <ph...@gmail.com>.
On 4/28/15 9:47 AM, Rohini T Nagaraj wrote:
> Hi ,
>
> We use org.apache.commons-dbcp-1.2.2.jar and
> com.springsource.org.apache.commons.pool-1.4.0.jar. Looks like we should
> be using a java less than 1.4 as per this link
> http://commons.apache.org/proper/commons-dbcp/. But we are using java 1.7.
> Please confirm if this incompatibility is causing this issue. Here is the
> full method for review.Thanks.
That would be a problem. Given that your code compiles, I suspect
what is going on is that you are somehow accessing connections
directly (not actually using DBCP), which will cause all kinds of
problems. You need to both upgrade dbcp and pool versions and use
DBCP to actually manage connections, resultsets, etc.
Phil
>
> public static java.util.List<StoredProcedureTableData>
> getStoredProcedureMetaData(String package_name,String schema, String
> storedProcedure, String encoding) {
> DatabaseMetaData metaData =
> DBConnectionManager.getDatabaseMetaData();
> java.util.List<StoredProcedureTableData> list = new
> ArrayList<StoredProcedureTableData>();
> String parameterName;
> String dataType;
> String type;
> String size;
> short shortType;
> StoredProcedureTableData data;
> ResultSet rs = null;
>
> try {
> storedProcedure=storedProcedure.substring(package_name.length()+1);
> rs = metaData.getProcedureColumns(package_name, schema,
> storedProcedure, null ); //$NON-NLS-1$
> 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() ) {
> logger.warning("In While loop.Is ResultSet closed =
> "+rs.isClosed()+"\n");
> parameterName = rs.getString( 4 ); // COLUMN_NAME (7) for
> query
> parameterName = getValidParameterName( parameterName );
> logger.warning("The Parametername = "+parameterName);
> logger.warning("After 1st param.Is ResultSet closed =
> "+rs.isClosed()+"\n");
> dataType = getDataType( rs.getInt( 6 ), rs.getString( 7 )
> ); //DATA_TYPE, TYPE_NAME
> shortType = rs.getShort( 5 ); //COLUMN_TYPE
> type = getColumnType( shortType );
> logger.warning("The DataType = "+dataType);
> logger.warning("The Type = "+type+" TYPE_RETURN =
> "+TYPE_RETURN);
> size = Integer.toString( rs.getInt( 8 ) );
> logger.warning("The Size = "+size);
> short scale = rs.getShort( 10 ); //(18) for query
> logger.warning("The Scale = "+scale);
> if( scale > 0 ) {
> size = size + "," + scale; //$NON-NLS-1$
> }
> data = new StoredProcedureTableData( true, parameterName,
> dataType, type, encoding, size , TYPE_RETURN.equals(type));
> list.add( data );
> }
> rs.close();
> rs = null;
> }
> }catch( SQLException e ) {
> connectionError( e );
> e.printStackTrace();
> }finally {
> if (rs != null) {
> try {
> rs.close();
> logger.warning("In finally.Is ResultSet closed =
> "+rs.isClosed()+"\n");
> } catch (SQLException e) {
> e.printStackTrace();
> }
> }
> rs = null;
> }
> return list;
> }
>
>
>
> 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 <ph...@gmail.com>
> To: Commons Users List <us...@commons.apache.org>
> Date: 04/28/2015 07:14 PM
> Subject: Re: [dbcp]Closed Connection or Exhausted Resultset error
>
>
>
> 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 <ph...@gmail.com>
>> To: Commons Users List <us...@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
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp]Closed Connection or Exhausted Resultset error
Posted by Rohini T Nagaraj <ro...@in.ibm.com>.
Hi ,
We use org.apache.commons-dbcp-1.2.2.jar and
com.springsource.org.apache.commons.pool-1.4.0.jar. Looks like we should
be using a java less than 1.4 as per this link
http://commons.apache.org/proper/commons-dbcp/. But we are using java 1.7.
Please confirm if this incompatibility is causing this issue. Here is the
full method for review.Thanks.
public static java.util.List<StoredProcedureTableData>
getStoredProcedureMetaData(String package_name,String schema, String
storedProcedure, String encoding) {
DatabaseMetaData metaData =
DBConnectionManager.getDatabaseMetaData();
java.util.List<StoredProcedureTableData> list = new
ArrayList<StoredProcedureTableData>();
String parameterName;
String dataType;
String type;
String size;
short shortType;
StoredProcedureTableData data;
ResultSet rs = null;
try {
storedProcedure=storedProcedure.substring(package_name.length()+1);
rs = metaData.getProcedureColumns(package_name, schema,
storedProcedure, null ); //$NON-NLS-1$
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() ) {
logger.warning("In While loop.Is ResultSet closed =
"+rs.isClosed()+"\n");
parameterName = rs.getString( 4 ); // COLUMN_NAME (7) for
query
parameterName = getValidParameterName( parameterName );
logger.warning("The Parametername = "+parameterName);
logger.warning("After 1st param.Is ResultSet closed =
"+rs.isClosed()+"\n");
dataType = getDataType( rs.getInt( 6 ), rs.getString( 7 )
); //DATA_TYPE, TYPE_NAME
shortType = rs.getShort( 5 ); //COLUMN_TYPE
type = getColumnType( shortType );
logger.warning("The DataType = "+dataType);
logger.warning("The Type = "+type+" TYPE_RETURN =
"+TYPE_RETURN);
size = Integer.toString( rs.getInt( 8 ) );
logger.warning("The Size = "+size);
short scale = rs.getShort( 10 ); //(18) for query
logger.warning("The Scale = "+scale);
if( scale > 0 ) {
size = size + "," + scale; //$NON-NLS-1$
}
data = new StoredProcedureTableData( true, parameterName,
dataType, type, encoding, size , TYPE_RETURN.equals(type));
list.add( data );
}
rs.close();
rs = null;
}
}catch( SQLException e ) {
connectionError( e );
e.printStackTrace();
}finally {
if (rs != null) {
try {
rs.close();
logger.warning("In finally.Is ResultSet closed =
"+rs.isClosed()+"\n");
} catch (SQLException e) {
e.printStackTrace();
}
}
rs = null;
}
return list;
}
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 <ph...@gmail.com>
To: Commons Users List <us...@commons.apache.org>
Date: 04/28/2015 07:14 PM
Subject: Re: [dbcp]Closed Connection or Exhausted Resultset error
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 <ph...@gmail.com>
> To: Commons Users List <us...@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
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp]Closed Connection or Exhausted Resultset error
Posted by Phil Steitz <ph...@gmail.com>.
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 <ph...@gmail.com>
> To: Commons Users List <us...@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
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp]Closed Connection or Exhausted Resultset error
Posted by Rohini T Nagaraj <ro...@in.ibm.com>.
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 <ph...@gmail.com>
To: Commons Users List <us...@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
Re: [dbcp]Closed Connection or Exhausted Resultset error
Posted by Phil Steitz <ph...@gmail.com>.
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