Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

JDBC problem while closing connection...

Posted on 2003-03-28
7
Medium Priority
?
335 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month10 days, 23 hours left to enroll

572 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