Mailing-List: contact user-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Commons Users List" <user@commons.apache.org>
Received-SPF: softfail (athena.apache.org: transitioning domain of
 phil.steitz@gmail.com does not designate 54.191.145.13 as permitted sender)
Message-ID: <553FCA3C.4070104@gmail.com>
Date: Tue, 28 Apr 2015 10:58:20 -0700
From: Phil Steitz <phil.steitz@gmail.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10;
 rv:31.0) Gecko/20100101 Thunderbird/31.6.0
MIME-Version: 1.0
To: Commons Users List <user@commons.apache.org>
Subject: Re: [dbcp]Closed Connection or Exhausted Resultset error
References: 
 <OF77A65094.47B7FE09-ON65257E2F.00414893-65257E2F.0043A2D8@in.ibm.com>
 <5537A2F4.4030304@gmail.com>
 <OF57905704.DF53AFC2-ON65257E35.0021A1AF-65257E35.00235B9A@in.ibm.com>
 <553F8E87.20300@gmail.com>
 <OF107BF828.88F2C225-ON65257E35.005B064E-65257E35.005C4389@in.ibm.com>
In-Reply-To: 
 <OF107BF828.88F2C225-ON65257E35.005B064E-65257E35.005C4389@in.ibm.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit

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 <phil.steitz@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 
>> 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.steitz@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-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