13 Replies Latest reply: Jul 12, 2012 9:06 PM by EJP RSS

    Oracle connection get closed

    947469

      I am facing a very strange behavior. My java application create a connection (java.sql.Connection) against an Oracle db, successfully. When I try to execute a statement, I got a:

      java.sql.SQLRecoverableException: Connection closed

      exception. This is how the application creates the connection (as in the old times):

      +conn = DriverManager.getConnection(
      "jdbc:oracle:thin:@localhost:1521:orcl", "usr", "pwd");+

      connection is never closed (almost intentionally) since before the program exits (in a finally block, as teacher told us ;)). If I use this method to obtain a connection,
      +
      protected Connection getConn() throws Exception {
      if(conn == null || conn.isClosed()) {
      conn = DriverManager.getConnection(
      "jdbc:oracle:thin:@localhost:1521:orcl", "usr", "pwd");
      }
      return conn;
      }
      +
      everything works fine. As I get the connection, the method verify it's close and recreate a new one. I can't figure out what's happen. Why the connection close? TIA.

      Francesco

        • 1. Re: Oracle connection get closed
          sabre150
          944466 wrote:
          connection is never closed
          Are you absolutely certain of this?
          • 2. Re: Oracle connection get closed
            rp0428
            Welcome to the forum!

            >
            I can't figure out what's happen
            >
            Neither can we unless you post the code you are actually using. When you do post use code tags (see the FAQ for formatting options that can be used) to make the code readable; your post has '+' signs and is not formatted.

            Also post the 4 digit Oracle version you are using and the version of the JDBC jar file.
            • 3. Re: Oracle connection get closed
              947469
              Yes. I'm sure about the exception. Here is the code:
              public abstract class ReaderGeneric {
                   
                   
                   private Properties properties;
                   private Logger logger;
                   private Connection conn;
                   private HashMap<String, FileField> template;
                   
                   public ReaderGeneric(String propertyfile) throws Exception {
                        
                        properties = new Properties();
                        try {
                             properties.load(new FileInputStream(new File(propertyfile)));
                             PropertyConfigurator.configure(properties);
                                  logger = Logger.getLogger(this.getClass().getName());
                             conn = DriverManager.getConnection(properties.getProperty("dbdriver")
                                       +properties.getProperty("dbhost")+":"
                                       +properties.getProperty("dbport")+":"
                                       +properties.getProperty("dbistance"), 
                                       properties.getProperty("username"), 
                                       properties.getProperty("password"));
                        } catch (FileNotFoundException e) {
                             logger.fatal(e);
                             throw e;
                        } catch (IOException e) {
                             logger.fatal(e);
                             throw e;
                        } catch (SQLException e) {
                             logger.fatal(e);
                             throw e;
                        }
                   }
                   
                   protected Properties getProperties() {
                        return properties;
                   }
              
                   protected Logger getLogger() {
                        return logger;
                   }
              
                   protected Connection getConn() throws Exception {
                        //if you uncomment the following code everything works fine.
                        //The method check if connection is closed or null and, if it is true, a new connection is created.
              //          if(conn == null || conn.isClosed()) {
              //               conn = DriverManager.getConnection(properties.getProperty("dbdriver")
              //                         +properties.getProperty("dbhost")+":"
              //                         +properties.getProperty("dbport")+":"
              //                         +properties.getProperty("dbistance"), 
              //                         properties.getProperty("username"), 
              //                         properties.getProperty("password"));
              //          }
                        return conn;
                   }
              
                   protected HashMap<String, FileField> getTemplate() {
                        return template;
                   }
              
                   ...
                   
                   public abstract void performTask() throws Exception;
              }
              
              
              public class ReaderArticoli extends ReaderGeneric {
                   
                   public SapReaderArticoli(String propertyfile) throws Exception {
                        super(propertyfile);
                   }
              
                   ...
                   
                   @Override
                   public void performTask() throws Exception {
                        String insert;
                        String insertfields;
                        String insertvalues;
                        String line;
                        PreparedStatement stmt = null;
                        MDC.put("ProcID", System.currentTimeMillis());
                        getLogger().setLevel(Level.toLevel(getProperties().getProperty("loglevel")));
                        getLogger().info("JOB START at "+new Date(System.currentTimeMillis()).toString());
                        BufferedReader br = null;
                        try {
                             
                             br = new BufferedReader(new FileReader(new File(getProperties().getProperty("articolifiletoread"))));
                             while((line = br.readLine()) != null) {
                                  //     per ogni riga di Articoli.txt inserire una riga in tabella IAR_ImportazioneArticoli
                                  insert = "insert into tblArticoli";
                                  try {
                                       
                                       //create insert statement
                                       
                                       insert = insert + "(" + insertfields + ") values ("+insertvalues+")";
                                       getLogger().debug("insert statement: "+insert);
                                       stmt = getConn().prepareStatement(insert);
                                       stmt.execute();     //here comes the exception!
                                       stmt.close();
                                  } catch (SQLException e) {
                                       getLogger().debug(e);
                                       if(stmt != null) stmt.close();
                                  } catch (Exception e) {
                                       getLogger().error(e);
                                       if(stmt != null) stmt.close();
                                  }
                             }
                             br.close();
                             getConn().close();
                             getLogger().info("JOB END at "+new Date(System.currentTimeMillis()).toString());
                             MDC.remove("ProcID");
                        } catch (SQLException e) {
                             getLogger().fatal(e);
                             getConn().close();
                             throw e;
                        }
                   }
              }
              Thanks in advance.

              Francesco

              Edited by: 944466 on 12-lug-2012 2.57
              • 4. Re: Oracle connection get closed
                rp0428
                Please edit your last post and surround the code with code tags to preserve the formatting and make it readable as documented in the FAQ.
                • 5. Re: Oracle connection get closed
                  EJP
                  getConn().close();
                  Here you are closing the connection, when the method exits. Not when the program exits.
                  • 6. Re: Oracle connection get closed
                    947469
                    Thankyou for the reply. I don't think so. Please read again my post (now it's more readable :) ). The exception occurs before I close the connection with that instruction.

                    Francesco
                    • 7. Re: Oracle connection get closed
                      EJP
                      Please read my posts. The error occurs the second and siubsequent times through this code, because the input has already been closed by the first time through. As I said. Or, as I also said, you are closing it somewhere else. Or both. As I also said.
                      • 8. Re: Oracle connection get closed
                        910900
                        Try to close connection in **finally block** also....
                        • 9. Re: Oracle connection get closed
                          947469
                          My fault. I was unclear and I miss some infos. The exception occurs the first time the method performTask() is executed. I said erroneously:
                          since before the program exits
                          that's not correct. I mean since before the method exits, and the method is performTask() . performTask() it's not executed a
                          second time.
                          • 10. Re: Oracle connection get closed
                            903917
                            Up until this line...
                            stmt.execute();     //here comes the exception!
                            you aren't getting any SQL Exceptions?
                            • 11. Re: Oracle connection get closed
                              jschellSomeoneStoleMyAlias
                              944466 wrote:
                              it's not executed a second time.
                              Then if you put the following after EACH close then it will not change anything.

                              conn = null
                              • 12. Re: Oracle connection get closed
                                rp0428
                                >
                                The exception occurs the first time the method performTask() is executed
                                >
                                Maybe so but I don't see how it can be occuring on the 'execute' like you said.
                                stmt = getConn().prepareStatement(insert);
                                stmt.execute();     //here comes the exception!
                                If the connect is closed at the 'execute' it would have to be closed for the 'prepareStatement' and that would throw an Exception
                                >
                                Throws:
                                SQLException - if a database access error occurs or this method is called on a closed connection
                                >
                                Add some additional code to test for valid connections and statements to find out where the problem is occuring.
                                • 13. Re: Oracle connection get closed
                                  EJP
                                  My fault.
                                  OK. The performTask() method should be synchronized, as it relies on shared state (the connection).