Mailing-List: contact user-help@commons.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Commons Users List" <user@commons.apache.org>
Received-SPF: pass (athena.apache.org: message received from 54.191.145.13
 which is an MX secondary for user@commons.apache.org)
In-Reply-To: <553F8E87.20300@gmail.com>
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>
To: "Commons Users List" <user@commons.apache.org>
MIME-Version: 1.0
Subject: Re: [dbcp]Closed Connection or Exhausted Resultset error
Message-ID: 
 <OF107BF828.88F2C225-ON65257E35.005B064E-65257E35.005C4389@in.ibm.com>
From: Rohini T Nagaraj <rohini.tn@in.ibm.com>
Date: Tue, 28 Apr 2015 22:17:51 +0530
Content-Type: multipart/alternative;
 boundary="=_alternative 005C438865257E35_="

--=_alternative 005C438865257E35_=
Content-Type: text/plain; charset="US-ASCII"

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 <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


--=_alternative 005C438865257E35_=--