This discussion is archived
1 2 Previous Next 24 Replies Latest reply: May 22, 2013 10:54 AM by Joe Weinstein Go to original post RSS
  • 15. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    I use connection pooling for 11g database. I have redeployed the one of the application(which only uses 11g database) with latest driver jar, seems its working fine. It is running since two days and not reported this error.

    It seems, the issue is with the usage of older driver jar file. Thanks one and all.

    I will mark this question as answered.
  • 16. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    Hi,
    I am still facing the same problem. so I try to simulate the problem and I succeed in that. I doubt the "setMaxConnectionReuseTime()" property of a pool.

    I use the latest jar ojdbc6.jar and latest ucp.jar(11.2.0.2) to test this.

    Much appreciated for your reply.

    Test Results:_

    Pass Scenario:

    Fri May 17 12:20:58 CEST 2013 - Connection Pool Initialized.
    Fri May 17 12:21:00 CEST 2013 - Connection obtained from UniversalConnectionPool

    Fri May 17 12:21:02 CEST 2013 - Sleeping here for 29 seconds. less than the[setMaxConnectionReuseTime].
    Fri May 17 12:21:31 CEST 2013 - No of Columns: 1
    Fri May 17 12:21:31 CEST 2013 - Connection returned to the UniversalConnectionPool

    Fail Scenario:

    Fri May 17 12:24:06 CEST 2013 - Connection Pool Initialized.
    Fri May 17 12:24:08 CEST 2013 - Connection obtained from UniversalConnectionPool

    Fri May 17 12:24:10 CEST 2013 - Sleeping here for 35 seconds. more than the[setMaxConnectionReuseTime].
    Fri May 17 12:24:45 CEST 2013 - Exception:Message: The connection is closed: The connection is closed
    Fri May 17 12:24:45 CEST 2013 - Connection returned to the UniversalConnectionPool

    java.sql.SQLException: The connection is closed: The connection is closed
         at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:473)
         at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:457)
         at oracle.ucp.jdbc.proxy.ResultSetProxyFactory.invoke(ResultSetProxyFactory.java:200)
         at $Proxy2.getMetaData(Unknown Source)
         at com.fedex.emea.util.jdbc.JDBCUCPoolingTest.poolJDBCConnection(JDBCUCPoolingTest.java:81)
         at com.fedex.emea.util.jdbc.JDBCUCPoolingTest.main(JDBCUCPoolingTest.java:29)


    Test Program:



    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Calendar;

    import oracle.ucp.jdbc.PoolDataSource;
    import oracle.ucp.jdbc.PoolDataSourceFactory;

    public class JDBCUCPoolingTest {
         
         JDBCUCPoolingTest(){
              try{
                   //Load the Oracle JDBC driver
                   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
              }catch(Exception exception){
                   exception.printStackTrace();
              }
         }

         public static void main(String[] args) {
              try {
                   String jdbcURL = "jdbc:oracle:thin:USERNAME/PASSWORD@SERVERNAME:PORT:SID";
                   poolJDBCConnection(jdbcURL);
              } catch (Exception e) {
                   e.printStackTrace();
              }
         }
         
         private static PoolDataSource initialize(String jdbcURL) throws Exception {
              
              //Create pool-enabled data source instance.
              PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
                   
              //set the connection properties on the data source.
              pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
              pds.setURL(jdbcURL);
              //pds.setUser(userName);
              //pds.setPassword(password);
              
              // Naming the Connection pool
              pds.setConnectionPoolName("JDBC_UCP");

              // Validate the connection while borrowing
              pds.setValidateConnectionOnBorrow(true);
                   
              // this is timeout period any connection to remove pool after creation of 30 secs
              pds.setMaxConnectionReuseTime(30);
                   
              // this is timeout period for idle "available connections" to close and remove pool
              pds.setInactiveConnectionTimeout(60);
              
              System.out.println(Calendar.getInstance().getTime()+" - Connection Pool Initialized.");
              
              return pds;
         }

         private static void poolJDBCConnection(String jdbcURL) throws Exception {
              
              Connection conn = null;
              try{
                   PoolDataSource pds = initialize(jdbcURL);
                   String sql = "SELECT SYSDATE FROM DUAL";

                   //Get a database connection from the datasource.
                   conn = pds.getConnection();
                   System.out.println(Calendar.getInstance().getTime()+" - Connection obtained from " +"UniversalConnectionPool\n");
                   
                   //do some work with the connection.
                   Statement stmt = conn.createStatement();
                   ResultSet rs = stmt.executeQuery(sql);
                   
                   System.out.println(Calendar.getInstance().getTime()+" - Sleeping here for 35 seconds. more than the[setMaxConnectionReuseTime]. ");
                   Thread.sleep(35000);
                   
                   ResultSetMetaData rsMeta = rs.getMetaData();
                   
                   int noof_cols = rsMeta.getColumnCount();
                   System.out.println(Calendar.getInstance().getTime()+" - No of Columns: "+noof_cols);
                   
                   while(rs.next()){
                        for(int i=0;i<noof_cols;i++){
                             rs.getObject(i+1);
                        }
                   }
              }catch(SQLException e){
                   System.out.println(Calendar.getInstance().getTime()+" - Exception:Message: "+e.getMessage());
                   throw e;
              }finally{
                   //Close the Connection.
                   conn.close();
                   conn=null;
                   System.out.println(Calendar.getInstance().getTime()+" - Connection returned to the " +"UniversalConnectionPool\n");
              }
         }
         
    }
  • 17. Re: JDBC:SQLException: The connection is closed
    gimbal2 Guru
    Currently Being Moderated
    you are working on the assumption that this is caused by something on the Java side. But we're talking about the Oracle DBMS with a million configuration properties here, who's to say it is not the DBMS that is actually closing the connections? I think at this point it is more prudent that a DBA examines the DBMS logs to see what is the truth.
  • 18. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    Hi,
    The normal way of getting connection works fine with the same database even after sleeping for 60 seconds. Much appreciated for your response.

    Looks like from the database side there is no property set to auto close connection.

    Test Results:_
    Fri May 17 13:01:01 CEST 2013 - Connection obtained.
    Fri May 17 13:01:02 CEST 2013 - Output: 2013-05-17 13:01:00
    Fri May 17 13:01:02 CEST 2013 - Sleeping here for 60 seconds.
    Fri May 17 13:02:02 CEST 2013 - No of Columns: 1


    Program:_
         private static void basicJDBCConnection(String jdbcURL) throws Exception {
              String sql = "SELECT SYSDATE FROM DUAL";
              Connection con = null;
              Statement statement = null;
              ResultSet rs = null;
              try{
                   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                   Class.forName("oracle.jdbc.driver.OracleDriver");
                   con = DriverManager.getConnection(jdbcURL);
                   statement = con.createStatement();
                   rs = statement.executeQuery(sql);
                   System.out.println(Calendar.getInstance().getTime()+" - Connection obtained. ");
                   while(rs.next()){                    
                        System.out.println(Calendar.getInstance().getTime()+" - Output: "+rs.getString(1));
                   }
                   System.out.println(Calendar.getInstance().getTime()+" - Sleeping here for 60 seconds. ");
                   Thread.sleep(60000);

                   ResultSetMetaData rsMeta = rs.getMetaData();
                   
                   int noof_cols = rsMeta.getColumnCount();
                   System.out.println(Calendar.getInstance().getTime()+" - No of Columns: "+noof_cols);
                   
                   while(rs.next()){
                        for(int i=0;i<noof_cols;i++){
                             rs.getObject(i+1);
                        }
                   }
                   
              }catch(Exception exception){
                   System.out.println(Calendar.getInstance().getTime()+" - Exception:Message: "+exception.getMessage());
                   throw exception;
              }finally{
                   try{
                        if(rs!=null)rs.close();
                        if(statement!=null)statement.close();
                        if(con!=null)con.close();
                   }catch(Exception exception){
                        throw exception;
                   }
              }
         }

    Edited by: 1004779 on May 17, 2013 4:04 AM
  • 19. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    I am going to guess the problem is pds.setMaxConnectionReuseTime(30), which apparently
    is ridding the pool of the connection (closing it) whether the connection is still in use by
    your application or not. Try setting that higher and lower to see if that's the issue, and
    check the UCP docs to see if it says that's what UCP will do.
  • 20. Re: JDBC:SQLException: The connection is closed
    rp0428 Guru
    Currently Being Moderated
    >
    I am going to guess the problem is pds.setMaxConnectionReuseTime(30), which apparently
    is ridding the pool of the connection (closing it) whether the connection is still in use by
    your application or not.
    >
    Maybe you are thinking of the 'setTimeToLiveConnectionTimeout'. That sets an absoluted limit and causes the connection to be closed whether it is in use or not,.

    See the Oracle UCP docs
    http://docs.oracle.com/cd/E11882_01/java.112/e12265/optimize.htm#CFHBJBCI
    >
    Setting the Time-To-Live Connection Timeout

    The time-to-live connection timeout enables borrowed connections to remain borrowed for a specific amount of time before the connection is reclaimed by the pool. This timeout feature helps maximize connection reuse and helps conserve systems resources that are otherwise lost on maintaining connections longer than their expected usage.

    Note:

    UCP for JDBC either cancels or rolls back connections that have local transactions pending before reclaiming connections for reuse.
    >


    The 'setMaxConnectionReuseTime' setting shouldn't cause the connection to be closed until it is returned to the pool.
    >
    Setting the Maximum Connection Reuse Time

    The maximum connection reuse time allows connections to be gracefully closed and removed from the pool after a connection has been in use for a specific amount of time. The timer for this property starts when a connection is physically created. Borrowed connections are closed only after they are returned to the pool and the reuse time has been exceeded.

    Note:

    The maximum connection reuse time is different from the time-to-live connection timeout. The time-to-live connection timeout starts when a connection is borrowed from the pool; while, the maximum connection reuse time starts when the connection is physically created. In addition, with a time-to-live timeout, a connection is closed and returned to the pool for reuse if the timeout expires during the borrowed period. With maximum connection reuse time, a connection is closed and discarded from the pool after the timeout expires. See Setting the Time-To-Live Connection Timeout.
  • 21. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    Yes, but I am asking the OP to do that test, to verify if UCP acts correctly
    or not with respect to the setting he made. UCP may have a bug.
  • 22. Re: JDBC:SQLException: The connection is closed
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    gimbal2 wrote:
    you are working on the assumption that this is caused by something on the Java side. But we're talking about the Oracle DBMS ...
    Oracle plays oddly with errors but I believe "The connection is closed: The connection is closed" means that the connection was closed on the client side.
  • 23. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    Is there any work around for this? is it a UCP bug?
  • 24. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    If you have done my test with UCP, and you are now sure that the setting is causing the
    connection to be closed out from under you, and you have already checked that you're
    running the latest UCP, you should open an official support case.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points