1 2 Previous Next 24 Replies Latest reply: May 22, 2013 12:54 PM by Joe Weinstein-Oracle RSS

    JDBC:SQLException: The connection is closed

    1007782

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