1 2 Previous Next 18 Replies Latest reply on Oct 10, 2011 9:41 AM by 838458

    Closed connection error when connection isn't being closed

    709026

      Hi,
      Currently I am trying to connect to an Oracle 11g database via JDBC. However, everytime I run the code I get the following error:

      java.sql.SQLException: Closed Connection
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:653)
      at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:338)
      at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
      at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:133)
      at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)

      The error comes from running the below code.

      public static Connection connect(String url, String username, String password) {
      Connection conn = null;
      //String url = "";

      // Load the driver
      try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      } catch (ClassNotFoundException cnfe) {
      System.err.println("Oracle driver class cannot be found");
      cnfe.printStackTrace();
      System.exit(1);
      }

      // Get the connection
      try{
      conn = DriverManager.getConnection(url, username, password); *// This is the offending line*
      } catch (SQLException sqle) {
      System.err.println("Problem connecting, possibly incorrect " +
      "username/password or problem communicating with the database");
      sqle.printStackTrace();
      System.exit(1);
      }java.sql.SQLException: Closed Connection
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:653)
      at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:338)
      at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
      at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:133)
      at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)

      // Turn auto commit off
      try{
      conn.setAutoCommit(false);
      } catch (SQLException sqle) {
      System.err.println("java.sql.SQLException: Closed Connection // This is the offending line
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:439)
      at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:653)
      at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:338)
      at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
      at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:133)
      at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
      at java.sql.DriverManager.getConnection(DriverManager.java:582)
      at java.sql.DriverManager.getConnection(DriverManager.java:185)Problem turning off auto commit");
      sqle.printStackTrace();
      System.exit(1);
      }

      return conn;

      }

      but the connection is never closed, this is the first time connect is called so it is never opened either.
      // This is the offending line
      The URL I am using to connect is:
      jdbc:oracle:oci8:@mydatabase:1158

      The database is stored on a server. I have previously tested the code on a machine with the database stored locally and had no problems. However, ran into this problem now when the database is external. Incase this information helps the software is being run under Linux via Netbeans. The previous testing was performed using Netbeans under Windows.

      Any help would be much appreciated.

      Thanks,
      Martin

        • 1. Re: Closed connection error when connection isn't being closed
          708885
          you put in the connection url the database server ip instead the database server name or add the server domain name in the url?
          • 2. Re: Closed connection error when connection isn't being closed
            709026
            Thanks for the fast reply.

            I tried changing it to:
            jdbc:oracle:oci8:@130.159.185.76:1158:database_name

            and just:
            jdbc:oracle:oci8:@130.159.185.76:1158

            but both are giving the same errors.

            Thanks again,
            Martin
            • 3. Re: Closed connection error when connection isn't being closed
              Joe Weinstein-Oracle
              Hi. I have seen this inscrutable exception before. It is because you are trying
              to connect with the driver in the OCI mode, and the driver jar being used,
              (typically in the WLS server\lib directory) is not compatible with the version
              of the OCI client libraries being used.
              While the driver in the thin (all-Java) mode is stable and easy to use/swap,
              running anywhere by itself with any hardware or JVM, the OCI mode is much
              more config-dependent. The ideal solution is to use the thin mode of connection.
              You can even retain the use of the tnsnames.ora file with the thin driver. If,
              however, you are convinced you need the OCI mode, then you must ensure that
              the exact right driver version is used, based on your OCI version.
              Joe
              • 4. Re: Closed connection error when connection isn't being closed
                709026
                Hi Joe,
                Thanks very much for that reply. I've tried out your suggestion. However, I am now getting a new error:

                java.sql.SQLRecoverableException: Io exception: Connection reset

                from the same line. I don't require to connect in OCI mode. I just need to connect to the database create some tables, insert data into those tables and also query them.

                Any help anyone could give me on fixing this problem would be really appreciated.

                Thanks again,
                Martin

                Edited by: user10668541 on 02-Jul-2009 02:21
                • 5. Re: Closed connection error when connection isn't being closed
                  Joe Weinstein-Oracle
                  SHow the full stack trace. That seems to be another issue entirely.
                  • 6. Re: Closed connection error when connection isn't being closed
                    709026
                    Here is the whole stack trace.

                    java.sql.SQLRecoverableException: Io exception: Connection reset
                    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:281)
                    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:118)
                    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:224)
                    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:296)
                    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:611)
                    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:455)
                    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:494)
                    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:199)
                    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)
                    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:494)
                    at java.sql.DriverManager.getConnection(DriverManager.java:582)
                    at java.sql.DriverManager.getConnection(DriverManager.java:185)

                    Thanks,
                    Martin
                    • 7. Re: Closed connection error when connection isn't being closed
                      103698
                      The "Connection Reset" may be due to network problem or the oracle driver problem. I used a correct version of driver and the problem was gone.

                      I encounter similar "Closed Connection" exception in 10g while using createStatement or executeUpdate and jdbc:oracle:thin:@127.0.0.1@ORCL. I don't know why it sometimes happens, even I explicitly call getConnection from my Connection Pool.

                      I'm trying to set values for LD_LIBRARY_PATH, ORACLE_SID and ORACLE_HOME environment variables, but not sure the result is OK or not.
                      • 8. Re: Closed connection error when connection isn't being closed
                        709026
                        Thanks for all the replies. It's much appreciated.

                        I've checked my drivers and Oracle version and everything should be fine. I'm using the latest version of the Oracle 11g database with the JDBC Java 1.6 Oraclle 11g driver. The version of Java being used is 6 as I checked that as well just incase it was a stupid mistake.

                        I'm guessing it's a network problem then?

                        Thanks,
                        Martin
                        • 9. Re: Closed connection error when connection isn't being closed
                          586307
                          As told by Joe.weinstein, you look at WLS server\lib , ORACLE_HOME/jdbc/lib, Jlib folders and find compare if drivers are all latest including ora1i8n.jar in jlib folder of oracle home.
                          odbc6.jar in all lib folders or odbc6_g.jar (in Weblogic /servers...ext folder) should be used. Then you won't get such errors.
                          • 10. Re: Closed connection error when connection isn't being closed
                            724394
                            I know it's late but I found this thread because I had the same problem. It was not for connecting remotely but to a local database, though.

                            My problem was that ORACLE_HOME was not set when running the java application accessing the database. I got the following Exception:

                            java.sql.SQLException: Closed Connection
                            at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:689)
                            at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:401)
                            at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
                            at oracle.jdbc.driver.T2CConnection.<init>(T2CConnection.java:148)
                            at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
                            at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
                            at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:280)
                            at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:207)
                            ...

                            When setting (and exporting!) ORACLE_HOME before running the program, the problem was gone.
                            Hope this helps future users.
                            • 11. Re: Closed connection error when connection isn't being closed
                              553198
                              Hi,

                              We also just upgraded from ojdb5.jar to ojdbc6.jar as a part of upgrading our application to Java 6.

                              And we are facing the same problem. But the scenario it happens is when we insert a BLOB of size 1 meg.

                              And one other thing is that this problem does not happen in Windows , happens in Solaris.

                              Any lead on this will be helpful.

                              -D
                              • 12. Re: Closed connection error when connection isn't being closed
                                781890
                                See this relevant post - I was facing same problem and was able run it after adding argument as -Djava.security.egd=file:///dev/urandom while running the program.

                                http://kr.forums.oracle.com/forums/thread.jspa?messageID=3699989
                                • 13. Re: Closed connection error when connection isn't being closed
                                  EJP
                                  You have two lines marked as 'the offending line'. Which is it?

                                  Your exception handling/System.exit() coding is poor practice. It should be simply this:
                                  try
                                  {
                                    Class.forName(...);
                                     conn = DriverManager.getConnection(...);
                                     conn.setAutoCommit(false):
                                  }
                                  catch (SQLException exc)
                                  {
                                   // ...
                                  }
                                  catch (ClassNotFoundException exc)
                                  {
                                   // ...
                                  }
                                  etc.
                                  There is no point in the first catch and the second try: this kind of structure just presents coding problems. Put all the connection related code inside the same 'try' block.
                                  • 14. Re: Closed connection error when connection isn't being closed
                                    838458
                                    Hi i am using the following code and i am getting the same error ....
                                    public class DBConnection {
                                         
                                         private static Connection con=null;
                                         
                                         public DBConnection() {
                                                   System.out.println("In db Connection..");
                                         }
                                         
                                         public static Connection getConnectionObj(){
                                              if(con==null){
                                                   con= new DBConnection().getConnection();
                                                   System.out.println("In db Connection.. if");
                                                   return con;
                                              }
                                              else{
                                                   System.out.println("In db Connection..else");
                                                   return con;
                                              }
                                              
                                         }
                                         
                                         private Connection getConnection(){
                                              Properties prop=new Properties();
                                              FileWriter fw=null;
                                              FileInputStream fis=null;
                                              try{
                                                             
                                                           File file=new File("Connection.properties");
                                                   fis=new FileInputStream(file);
                                                   prop.load(fis);
                                                   System.out.println(prop.getProperty("DriverName"));
                                                   System.out.println(prop.getProperty("URL"));
                                                   Class.forName(prop.getProperty("DriverName"));
                                                   System.out.println(prop.getProperty("DriverName"));
                                                   System.out.println(prop.getProperty("URL"));
                                                   con=DriverManager.getConnection(prop.getProperty("URL"),prop.getProperty("UserName"),prop.getProperty("Password"));
                                                   fis.close();
                                              }catch(Exception e){
                                                   e.printStackTrace();
                                                   if(fw!=null){
                                                        try {
                                                             fw.close();
                                                        } catch (IOException e1) {
                                                             // TODO Auto-generated catch block
                                                             e1.printStackTrace();
                                                        }
                                                   }
                                                   if(fis!=null){
                                                        try {
                                                             fis.close();
                                                        } catch (IOException e1) {
                                                             // TODO Auto-generated catch block
                                                             e1.printStackTrace();
                                                        }
                                                   }
                                              }
                                              return con;
                                         }
                                    }
                                    I am not using the connection pool.
                                    For the first request it is working fine but later request's it is giving the closed connection error.


                                    Thanks in advance.

                                    Edited by: EJP on 10/10/2011 20:28: added {noformat}
                                    {noformat} tags: please use them                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                    1 2 Previous Next