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 =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-unsubscr...@commons.apache.org > For additional commands, e-mail: user-h...@commons.apache.org > > > --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@commons.apache.org For additional commands, e-mail: user-h...@commons.apache.org