?
Solved

JDBC problem while closing connection...

Posted on 2003-03-28
7
Medium Priority
?
325 Views
Last Modified: 2012-05-04
I have this Java app. which connect an Oracle database using JDBC. I want my application to be able to recover from a possible database-is-down. Thus I did this :  Whenever the database is down and my application tries to insert data, a SQLException is thrown. My app captures that SQLException and tries to close the connection. Of course, since the database is down this will also fail. Then, it makes is JDBC connection equals "NULL" and starts looping trying to build a new connection until a valid new connection is acquired. Eventually the database will come back alive and our Java app will resume its normal operation with this new connection. However, as soon as it tries to use the new connection a SQLException is thrown: "java.sql.SQLException: Io exception: End of TNS data channel", and th app goes back to try to regain a new connection. This cycle will go forever without ever getting a JDBC connection that works. According to our Oracle guy, it seems that eventhough our Java app gets a new connection, it uses the same session while connecting the database, but because the database went down and back up that session is not valid any more.

How can I get around this????? (without restarting our Java app. If our Java app is restarted everything will work fine, but I want it to handle this by itself and regain the database connection without having to restart the app)

How can I get around this?????
thanks in advance
0
Comment
Question by:dyma82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 1

Author Comment

by:dyma82
ID: 8224542
This is my "catch" code where I handle the exception:

          catch (SQLException sqlexception)
        {
              log.error("##### SQLException on DB conn. #####");
               log.error(sqlexception.toString());
               String err = " while closing connection.";
               boolean connected = false;
               int d = 1;
               while(!connected)
               {
                   
                    try
                    {
                         Thread.sleep(15000);
                         if(conn != null)
                         {
                              System.out.println("Attempting to close JDBC connection......!!!");
                              conn.close();
                              conn = null;
                         }
                         else log.error("##### DB Connection conn is unexpectedly NULL");
                         err = "while reconnecting to the DB";
                         System.out.println("Trying to reconnect DB. Attempt -> "+d++);
                         conn = DBManager.getConnection(EPoliceServerResources.EPOLICE_JDBC_ORACLE_URL, EPoliceServerResources.EPOLICE_JDBC_ORACLE_USER, EPoliceServerResources.EPOLICE_JDBC_ORACLE_PWD);
                         connected = true;
                         //System.out.println("Reconnecting the DB.............................!!!");
                    }
                    catch (SQLException ex2)
                    {
                         System.out.println("SQLException while reconnecting");
                          log.error("SQLException "+err);
                          conn = null;
                    }
                    catch(InterruptedException ie)
                    {
                         System.out.println("InterruptedException while reconnecting");
                         log.error("InterruptedException "+ie);
                         
                    }
                }
        }
0
 
LVL 9

Expert Comment

by:Venci75
ID: 8225117
can you post the exception stack trace?
Do you use statements created with the "closed" connection?
0
 
LVL 1

Author Comment

by:dyma82
ID: 8225639
After the line : conn = DBManager.getConnection(...); succesfully returns a connnection into "conn" , the app uses this connection to insert data into the DB, and that's where it fails again and goes back to the error handling rutine for it to happen all over again. It seems like it gets a valid connection to the DB, but some of the settings for that connection are wrong (some kind of session being kept from the connection to the DB before it was bounced), so when it tries to use that connection a new SQLException is thrown.

If I restart the application everything works fine. It is like the JDBC driver is caching something from the old connection, and eventhough, the app creates a new connection it does not get rid of this old settings, so the new connection fails again.

Any idea???
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 9

Expert Comment

by:Venci75
ID: 8225681
which is the line of code that uses this connection?
post the exception stacktrace:
exception.printStackTrace()
0
 
LVL 1

Author Comment

by:dyma82
ID: 8226779
Trace:

java.sql.SQLException: Io exception: End of TNS data channel
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
     at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:324)
     at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1616)
     at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1527)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java(Compiled Code))
     at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java(Compiled Code))
     at com.mdpd.epolice.common.db.LoadData.insert(LoadCADData.java(Compiled Code))
     at com.mdpd.epolice.server.mobilemessaging.DataLoader.processMessage(CadDataLoader.java:151)
     at com.mdpd.epolice.server.mobilemessaging.GenericMQServer.processMessageLoop(GenericMQServer.java:223)
     at com.mdpd.epolice.server.mobilemessaging.DataLoader.main(CadDataLoader.java:76)
0
 
LVL 9

Accepted Solution

by:
Venci75 earned 200 total points
ID: 8237677
you have a prepared stament that uses the old (broken) connection:

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java(Compiled Code))
at com.mdpd.epolice.common.db.LoadData.insert(LoadCADData.java(Compiled Code))
at com.mdpd.epolice.server.mobilemessaging.DataLoader.processMessage(CadDataLoader.java:151)
at com.mdpd.epolice.server.mobilemessaging.GenericMQServer.processMessageLoop(GenericMQServer.java:223)
at com.mdpd.epolice.server.mobilemessaging.DataLoader.main(CadDataLoader.java:76)

can you re-create it with the new connection?
Which is your class:
DataLoader, GenericMQServer or LoadData?
0
 
LVL 1

Author Comment

by:dyma82
ID: 8240065
Yep. Thanks.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question