This discussion is archived
13 Replies Latest reply: Jul 12, 2012 7:06 PM by EJP RSS

Oracle connection get closed

947469 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    944466 wrote:
    connection is never closed
    Are you absolutely certain of this?
  • 2. Re: Oracle connection get closed
    rp0428 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    getConn().close();
    Here you are closing the connection, when the method exits. Not when the program exits.
  • 6. Re: Oracle connection get closed
    947469 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Try to close connection in **finally block** also....
  • 9. Re: Oracle connection get closed
    947469 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Up until this line...
    stmt.execute();     //here comes the exception!
    you aren't getting any SQL Exceptions?
  • 11. Re: Oracle connection get closed
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    My fault.
    OK. The performTask() method should be synchronized, as it relies on shared state (the connection).

Legend

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