This discussion is archived
1 2 Previous Next 24 Replies Latest reply: May 22, 2013 10:54 AM by Joe Weinstein RSS

JDBC:SQLException: The connection is closed

1007782 Newbie
Currently Being Moderated
Hi,
I maintain a standalone java applicaiton. In that I use Universal connection pool to maintain JDBC connections. I have set the following connection pool properties. I am able to get the connection from pool and return back to pool but sometimes it gives error. Could you please help me, how to resolve this issue.?

This error I get when try to get the resultset metadata.

Error stacktrace
2013-04-30 18:46:27,036 [pool-1032-thread-1] ERROR (ApplicationLogger.java:50) SSH_ErrorLogger -
java.sql.SQLException: The connection is closed: The connection is closed
     at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:526)
     at oracle.ucp.util.UCPErrorHandler.newSQLException(UCPErrorHandler.java:510)
     at oracle.ucp.jdbc.proxy.ResultSetProxyFactory.throwSQLException(ResultSetProxyFactory.java:229)
     at oracle.ucp.jdbc.proxy.ResultSetProxyFactory.invoke(ResultSetProxyFactory.java:187)
     at $Proxy2.getMetaData(Unknown Source)
     at com.fedex.emea.rih.util.ssh.utils.DBHelper.executeQueryOnce(DBHelper.java:237)


Connection pool properties
// Validate the connection while borrowing
pds.setValidateConnectionOnBorrow(true);
     
// this is timeout period any connection to remove pool after creation of 300 secs
pds.setMaxConnectionReuseTime(300);
          
// this is timeout period for idle "available connections" to close and remove pool
pds.setInactiveConnectionTimeout(60);


Thanks.

Edited by: 1004779 on May 8, 2013 5:57 AM

Edited by: 1004779 on May 8, 2013 6:05 AM

Edited by: 1004779 on May 8, 2013 6:10 AM
  • 1. Re: JDBC:SQLException: The connection is closed
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    EJP wrote:
    Bug in the 'Universal connection pool'. Off topic for here, sorry. Try the supplier.
    Oracle is the supplier for UCP.

    http://docs.oracle.com/cd/E11882_01/java.112/e12265/intro.htm
  • 2. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    Are you sure this getMetaData() call is never made after
    a 60-second period of non-use of the connection?
  • 3. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    Thanks alot for your reply.

    As per the Oracle UCP Guide, setMaxConnectionReuseTime(60); method applies only to available connection. Borrowed connection will be inactive once it returned to pool.


    --- Reference from UCP Guide --
    Setting the Inactive Connection Timeout
    The inactive connection timeout specifies how long an available connection can remain
    idle before it is closed and removed from the pool. This timeout property is only
    applicable to available connections and does not affect borrowed connections. This
    property helps conserve resources that are otherwise lost on maintaining connections
    that are no longer being used. The inactive connection timeout (together with the
    maximum pool size) allows a connection pool to grow and shrink as application load
    changes.
    The inactive connection timeout value represents seconds. A value of 0 indicates that
    the feature is disabled. The default value is set to 0. The following example
    demonstrates configuring an inactive connection timeout:
    pds.setInactiveConnectionTimeout(60);
    ----
  • 4. Re: JDBC:SQLException: The connection is closed
    rp0428 Guru
    Currently Being Moderated
    >
    As per the Oracle UCP Guide, setMaxConnectionReuseTime(60); method applies only to available connection. Borrowed connection will be inactive once it returned to pool.
    >
    That wasn't the question.

    As one of the JDBC developers I'm pretty sure that Joe understands that.

    The question was:
    >
    Are you sure this getMetaData() call is never made after
    a 60-second period of non-use of the connection?
    >
    Just because you 'close' a connection and return it to the pool doesn't mean your code doesn't then try to use the connection afterward. I believe Joe is saying that could cause the exception you are getting.,
  • 5. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    Thanks, RP.
    Actually either one for now. could the code do either:

    get result set, do other things or sleep for 60 seconds, call getMetaData()

    or

    get result set, do other things, including closing the connection, call getMetaData()

    I am not fluent in the internals of UCP, but from a general sense of what pool
    systems can do, as well as what applications can do, the current exception might
    be caused be either of these cases. If the OP can take 5 minutes to run a repro
    that causes both of these cases, we may find some good new data. I wouldn't
    be shocked to hear that a given piece of official documentation might be wrong...
  • 6. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    I am pretty sure that the call to resultSet getmetadata is within 60 sec of time. The code is simple, getting the prepared statement from connection object, setting parameters to it and executeQuery to get the resultset.

    I am getting the error from the second line of below code and the first line executeQuery() call looks ok.

    Code:
         rSet = statement.executeQuery();
         ResultSetMetaData metaData = rSet.getMetaData();

    Thanks your very much for your help to resolve my problem.
  • 7. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    OK, then that's more interesting. Tell us how complicated your query is. I
    suspect a sporadic DBMS problem executing the query that may cause the
    driver to have to close the connection internally because of what it got or
    didn't get during the executeQuery() call. This might leave evidence in
    the DBMS error log. Also, update your JDBC driver to the latest available
    that is appropriate for your DBMS. If you open an official support case they
    may give you driver-level debug info that would make the issue clearer.

    Is it always happening for the same query, or has it happened for different
    queries?
  • 8. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    The query is simple. when I run the query, it gives results in milliseconds, the results of this query is two records always(kind of collecting last two samples). I use ojdbc14 jar for this. The meta-inf is like below

    Manifest-Version: 1.0
    Specification-Title: Oracle JDBC driver classes for use with JDK14
    Sealed: true
    Created-By: 1.4.2_14 (Sun Microsystems Inc.)
    Implementation-Title: ojdbc14.jar
    Specification-Vendor: Oracle Corporation
    Specification-Version: Oracle JDBC Driver version - "10.2.0.4.0"
    Implementation-Version: Oracle JDBC Driver version - "10.2.0.4.0"
    Implementation-Vendor: Oracle Corporation
    Implementation-Time: Sat Feb 2 11:40:29 2008

    Name: oracle/sql/converter/
    Sealed: false

    Name: oracle/sql/
    Sealed: false

    Name: oracle/sql/converter_xcharset/
    Sealed: false
  • 9. Re: JDBC:SQLException: The connection is closed
    Joe Weinstein Expert
    Currently Being Moderated
    That is certainly very old. See the official docs for the latest
    driver jar you can use with your JVM and DBMS version. Update
    it, but also check your DBMS error log, just to cover all the
    bases.
  • 10. Re: JDBC:SQLException: The connection is closed
    rp0428 Guru
    Currently Being Moderated
    >
    I use ojdbc14 jar for this.
    >
    WHY? What possible reason could you have for using such an ancient jar file for JDBC when you say you are using Universal Connection Pool?

    Here is the download link for 'JDBC, SQLJ, Oracle JPublisher and Universal Connection Pool (UCP)'
    http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

    Notice the downloads available for JDBC:
    >
    JDBC Driver Downloads
    Oracle Database 11g Release 2 (11.2.0.3), (11.2.0.2.0), (11.2.0.1.0) drivers
    Oracle Database 11g Release 1 (11.1.0.7), (11.1.0.6) drivers
    Oracle Database 10g Release 2 (10.2.0.5), (10.2.0.4), (10.2.0.3), (10.2.0.2), (10.2.0.1.0) drivers
    Oracle Database 10g (10.1.0.5), (10.1.0.4), (10.1.0.2.0) drivers
    Oracle9i Release 2 (9.2.0.8), (9.2.0.5), (9.2.0.4), (9.2.0.3), & (9.2.0.1) drivers
    Oracle9i Release 1 (9.0.1.4) & (9.0.1) drivers
    Oracle8i Release 2 (8.1.7)
    >
    Now notice the downloads available for UCP:
    >
    Universal Connection Pool (UCP)
    Oracle Database 11g Release 2 (11.2.0.3), (11.2.0.2), (11.2.0.1.0)
    Oracle Database 11g Release 1 (11.1.0.7)
    >
    Do you see ANY releases there for older versions of the DB (even 10g)? Does that tell you anything about what JDBC jar you should be using? Do you think UCP has been tested with 10g?

    Upgrade your JDBC jar file and see if the problem recurs.
  • 11. Re: JDBC:SQLException: The connection is closed
    1007782 Newbie
    Currently Being Moderated
    In my application we connect to different databases, one of them is 8i and other one is 11g. Because of oracle8i, we still use ojdbc14.jar. This is going to be retire soon. If this is going to be root cause of the error then I have to wait until we retire oracle 8i. :)

    Thanks a lot for your help.

    Kind Regards,
    Jay.
  • 12. Re: JDBC:SQLException: The connection is closed
    gimbal2 Guru
    Currently Being Moderated
    So don't 'upgrade' the JDBC driver yet but do perform a test run using it to at least be sure that resolves something. Otherwise you're going to delay treating this as an actual problem, retire Oracle 8, upgrade your JDBC driver and then come to the conclusion you've still got dark clouds looming over your head, this time with far more pressure attached to it.
  • 13. Re: JDBC:SQLException: The connection is closed
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    UCP is specifically designed to work with any driver - not just Oracle ones.
    And this is a sporadic error where the application otherwise works and where the error is a "connection is closed".

    It seems really unlikely to me that the cause of this could be an older driver.

    On the other hand misusing a connection (nothing to do with the pool) in a threaded application WOULD cause that problem at some point. And it would be sporadic.
  • 14. Re: JDBC:SQLException: The connection is closed
    939520 Explorer
    Currently Being Moderated
    You mention you connect to two different databases (8i and 11g). Can you determine which one causes your error? It may help narrow down the problem.

    One possible solution is to use two different drivers in your program. One to use for your 8i database, and another for your 11g database.
    I don't know if this is possible since I never tried it.
1 2 Previous Next

Legend

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