Solved

handling SQLException exceptions

Posted on 2004-08-18
14
817 Views
Last Modified: 2010-05-18
Dear Experts,
I would like to get an advice on the best way to handle sql exceptions. I am developing a realtime java application that will essentially access a mysql database, things are fine as long as the databse is up and running. I need to do somechanges so that any request gets buffered untill the databse is up again.
here is part of the code that does the connection through the jdbc to a mysql server

public String selectdata(String database, String query) {

  String url="jdbc:mysql://localhost/" + database;
  String data = "";
  int LineNumber = 0;
  Connection con;
  Statement stmt;
  try {

              Class.forName("com.mysql.jdbc.Driver");

   }catch (Exception e) {
     System.err.print("ClassNotFoundException:   " + e);
     System.err.println(e.getMessage());
   }
   try {
     con = DriverManager.getConnection (url, "username", "password");
     stmt = con.createStatement();
     ResultSet result = stmt.executeQuery(query);
     ResultSetMetaData rsmd = result.getMetaData();
       int columnCount = rsmd.getColumnCount();
       data = "";
       String rowData ="";
       String result1 ="";
     while (result.next()) {
          rowData = "";
          // get the data for each column in a row and
          // construct a string of separated column values
          for (int i = 1; i<= columnCount; i++) {
             result1 = result.getString(i);
              rowData = rowData + result1;
          }
          data = data + rowData;
     }
     stmt.close();
     con.close();
    }catch(SQLException ex) {
      System.err.print("SQLException: " + ex);
    }
    return (data);
  }

Thanks
0
Comment
Question by:saedpalnet
  • 6
  • 4
  • 3
14 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 11830355
What I would do is to have a instance boolean variable named "alive" and when an SQL exception occurs I would set it to false (inside the catch), otherwise I would set it to true.

Then before each call to the selectData method, I would check this variable if it is true. If it is then proceed as normal, if it is not then hold and try to obtain connection to the database. If you are able to connect (just connect, no need to send an SQL statement) then call the selectData. A pseudo code could look like

private static boolean alive = true;

if (alive)
{
    selectData();
}
else
{
    try to obtain connection to db
    if connection to db is successful then
        alive = true;
}
...
...

selectData()
{
   // do database stuff.
   ...
   catch (SQLException e)
   {
      alive = false;
   }
}
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11831290
Hi,

The code should close the statement/connection in the case where there is an exception - use a "finally" block to do this...

Connection conn = null;
Statement stmt = null;
try {
...
}
catch( SQLException e ) {
    // handle exception here...
}
finally {
    if( stmt != null ) {
        try {
            stmt.close();
        }
        catch( Exception e ) { System.err.println( "Warning - failed to close statement!" ); e.printStackTrace(); }
    }
    if( conn != null ) {
        try {
            conn.close();
        }
        catch( Exception e ) { System.err.println( "Warning - failed to close connection!" ); e.printStackTrace(); }
    }

Without doing this, if there are a lot of requests to this method you will run out of available connections with the database until the garbage collector runs...

Back to the main question though - what are you wanting to queue - if it is just a request to some data from outside, perhaps the method should just propagate an exception indicating the database connection is unavailable, and have the client calling functionality doing something useful.

Do you want the calling application to be blocked forever waiting for the database to be contactable again - I don't think that this would be too good from a realtime perspective, so the exception should really just be propagated to the caller, so a correct decision can be made there as to what to do if the database is not available.

However, if the task being performed to the database is actually a write comming in from some external trigger that cannot be stored in the calling program (possibly a bad example might be a simple syslog program writting logs to the database), then the request should be modeled somehow (a class developed to wrap the data to be written) - if a connection to the database is unavailable, add this request to a (synchronized) queue. When connectivity to the database is available again, flush any requests queued before processing the latest one... Actually, these two could be split completely, whereby the database writer just processes requests on the queue, and the request method just adds entries onto the queue... No snippet here, as this is actually not what the question code snippet was for...
0
 

Author Comment

by:saedpalnet
ID: 11838838
but I do close the connection after the I get the data (though its not done inside finally statement!).
anyways, I thought that there could be a solution where I can block the whole process (object)till I get a valid connection, and use a timer to check the connection periodically (say every 30 seconds).
this process is irreversable, its a perpaid service and the request is coming from a different application which does some sort of a billing operation and then dumps the request to my side. so when I get the request, I have to make sure I send some useful data for each request I receive.
0
 
LVL 35

Expert Comment

by:girionis
ID: 11838914
If you follow my suggestion you can have the time set the "alive" variable as sonn as the database is alive.
0
 

Author Comment

by:saedpalnet
ID: 11839746
ok girionis,
I am afraid I didnt get you here. when the selectData is called, it will just set the falg if an exception occured and nothing else done to make sure I will recall the select data function for this certain request when I maintain a connection to the database. what am I missing here?
 
0
 
LVL 35

Assisted Solution

by:girionis
girionis earned 60 total points
ID: 11839778
Before any call to selectData() method you will need to check the flag. If it is true then proceed. If it is false then hold. You can hold by having the current thread (lets call it thread A for clarity) sleep for an amount of seconds. If an exception occurs in your selectData() method then you will need to set the flag to false. After that the call to selectData() won't happen unless the flag is set to true. Have a separate TimerTask that loads up the db driver and tries to connect to the database. Just connect, do not send any SQL statements. If the connection is successful then set the flag to true, otherwise leave it as it is. Now when the thread A (the current thread) wakes up it will check for the flag. If it is true then proceed with the selectData() call otherwise keep waiting (sleeping).
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Accepted Solution

by:
cjjclifford earned 90 total points
ID: 11840146
Hi,

My point is that there might be an exception thrown before your code gets to the close(), so the connection is left open.

It looks like you should abstract the requests coming in, and queue them up (ordered). Then have a seperate thread that does the actual processing of these requests (including the database interaction) - in this way, when the request will only get handled when a database connection is available. If you use JMS to provide the queuing (you can even use persistent JMS queue, in case of application failover), you can set the (JMS) transaction handling to manual, only acknowledging the message from the queue when the database interaction is successful - if this fails, the message remains on the queue...

If you don't want to go to full JMS, a simple queue implemented with a Collections.synchronizedList( new ArrayList() ), with requests being simply List.add()'ed onto the queue, and have a thread processing the requests -

List requestQueue =  Collections.synchronizedList( new ArrayList() );

// to add to the queue:
requestQueue.add( new QueuedRequest( ... ) );
// Notify, as the reading thread may be wait()ing
synchronized( requestQueue ) {
     requestQueue.notifyAll();
}
// ...

new Thread( new Runnable() {
    public void run() {
        while( true ) {
        while( requestQueue.size() == 0 ) {
            synchronized( requestQueue ) { requestQueue.wait(); }
        }
        synchronized( requestQueue ) {
            // your own implementation of a Request... (get the head of the list)
            QueuedRequest request = (QueuedRequest)requestQueue.get(0);
            try {
                 // process the request including JDBC calls
                 processRequest( request );
                 // Only remove from queue if successful
                 requestQueue.remove( 0 );
            }
            catch( Exception e ) {
                 // This method periodically attempts to create a connection to database, returning when successful!
                 waitForJDBCConnectionAvailable();
            }
    }
}
0
 
LVL 35

Expert Comment

by:girionis
ID: 11840195
> My point is that there might be an exception thrown before your code gets to the close(), so the connection is left open.

You can still close it in the finally block. If an exception is thrown there you can always set the connection object to null.
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11840432
that was my point - the original snippet closed in the try{} block, I was just pointing out that it would be better done in a finally{} block...
If there is a problem closing the connection, it will eventually get garbage collected anyway (the object was method local in the snippet), so setting it to null is a little redundant (although, it might speed up garbage collection a little, not sure how this works with attributes created locally in methods (on the stack?))

Cheers,
C.
0
 
LVL 35

Expert Comment

by:girionis
ID: 11840456
Yes you are right, the connection is local to the method so it will get destroyed anyway when the method exits.
0
 

Author Comment

by:saedpalnet
ID: 11960681
thanks for the help. I will try to take the best out of the two suggestions.
regards
0
 
LVL 35

Expert Comment

by:girionis
ID: 11960892
:)
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 11961294
thanks for the points!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now