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 <[email protected]>
To: Commons Users List <[email protected]>
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 <[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]
