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 phil.ste...@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 =9 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.
Re: [dbcp]Closed Connection or Exhausted Resultset error
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.ListStoredProcedureTableData
getStoredProcedureMetaData(String package_name,String schema, String
storedProcedure, String encoding) {
DatabaseMetaData metaData =
DBConnectionManager.getDatabaseMetaData();
java.util.ListStoredProcedureTableData list = new
ArrayListStoredProcedureTableData();
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 phil.ste...@gmail.com
To: Commons Users List user@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
Re: [dbcp]Closed Connection or Exhausted Resultset error
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.ListStoredProcedureTableData
getStoredProcedureMetaData(String package_name,String schema, String
storedProcedure, String encoding) {
DatabaseMetaData metaData =
DBConnectionManager.getDatabaseMetaData();
java.util.ListStoredProcedureTableData list = new
ArrayListStoredProcedureTableData();
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 phil.ste...@gmail.com
To: Commons Users List user@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
How can I restart a Commons Daemon Service Runner via API?
Hi, How can I restart a Commons Daemon Service Runner from Java inside? Is there a simple API? or can I request the service name to restart it with an external process? Volker
