how do I handle SQLExceptions?

[verduka fox]

Hello,
I have a JavaBean in my project (non-EJB) that handles all of my database calls. All database calls are done via stored procedures to Sybase 12. I use jConnect 5.2 as the JDBC driver.
I want to expand my exception handling of SQLExceptions. Currently, I have each call to a stored procedure in a separate method. Each method uses a try/catch/finally block. The resultset, statement, and connection are closed in the finally block. If a SQLException occurs, I catch it as:

catch(SQLException sqle)
{
   throw new MyProjectException(EXCEPTION_MESSAGE + "getInformation(): " + sqle.getMessage());
}

with EXCEPTION_MESSAGE defined as:

private static final String EXCEPTION_MESSAGE = "SQLException occurred in Service.";

I would like to expand this catch block to do the following:
-- display the error code and SQL state in addition to the message
-- iterate through the SQLExceptions since there could be multiple chained together
I have also heard of SQLWarnings but I don't know how to "get" them. Can anyone please expand on this?
Furthermore, I would like to know if I can pass the SQLException to a private method to handle the above requirements instead of putting the code in the catch block of each method which calls a stored procedure. This would be much more efficient, as I have many methods.
Taking a try at this, I think that the code I would need to implement would be something like the following:

private void handleSQLException(SQLException sqle, String methodName) throws MyProjectException
{
   StringBuffer sb = new StringBuffer();
   sb.append(EXCEPTION_MESSAGE);
   sb.append(methodName);
   sb.append(": ");
   try
   {
      while (sqle != null)
      {
         sb.append(sqle.getMessage());
         sb.append(". Error Code: ");
         sb.append(sqle.getErrorCode());
         sb.append(". SQL State: ");
         sb.append(sqle.getSQLState());
         sb.append("\n");
         sqle = sqle.getNextException();
      }
      throw new MyProjectException(sb.toString());
   }
   catch(SQLException e)
   {
      // throw whatever I have
      throw new MyProjectException(sb.toString());
   }
}

If anyone could comment on the sample code I have written (if this is even possible) and expand on SQLWarnings, I would appreciate it. Thank you for your time.
[ February 24, 2003: Message edited by: verduka fox ]
[ February 25, 2003: Message edited by: verduka fox ]

[verduka fox]

If anyone can comment on the issues above, I'd really appreciate it.
I'm also wondering if putting this code in a separate method is "legal", could I put this separate code in a different class than the current one? My Service class where all of the database stored procedures are called is the child of a Database class in my utility package. I'd prefer to put the code for handling the exception in this utility package so that it can be implemented in all of my projects without copying and pasting the code.
Thanks.

[verduka fox]

Can anyone comment on this please? Thanks.

[Rodney Woodruff]

Maybe the following articles will help:
http://www.javaworld.com/javaworld/javatips/jw-javatip91.html
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&th=c1dc4c73a04774cd&rnum=7

[Paulo Salgado]

Verduka,
I'd say passing an Exception to a method should work as it is an object like any other. I was wondering though, why did you put your while loop within a try-catch block checking for an SQLException.
You can also check for a null SQLException parm before you go ahead and handle it.
And you can make your method to return a formatted String error message to the calling method, and it will throw the exception you desire. Or you can even put your method in a separate class that formats error messages based on the Exception's it receives.
Just comments, as you asked for.
By the way, are the chained SQLExceptions set in your Stored Procedures ? Never used that before...

[verduka fox]

Paulo,
I put a while loop within a try-catch block because there may be multiple SQLExceptions chained together, and I'd like to get as much information as possible. This seems to give more information than just looking at the SQLException that is passed into the method.
Passing the SQLException to a method to handle seems to be working.
Now I have a question about the finally block of my try-catch-finally. I have the same code nearly everywhere:

finally
{
   try
   {
      if (rs != null)
      {
         rs.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
   try
   {
      if (stmt != null)
      {
         stmt.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
   try
   {
      if (conn != null)
      {
         conn.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
}

Is it possible to write a common method to handle this? What I have in mind is:

private void handleFinally(ResultSet rs, Statement stmt, Connection conn)
{
   try
   {
      if (rs != null)
      {
         rs.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
   try
   {
      if (stmt != null)
      {
         stmt.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
   try
   {
      if (conn != null)
      {
         conn.close();
      }
   }
   catch(SQLException ignored)
   {
      // ignore
   }
}

If this would work, I would then overload this method, as I sometimes have only a statement and a connection (no resultset). Would this work the same as having the code in each method?
Thanks.

[Paulo Salgado]

Hello Verduka.
I think your idea will work fine, can't see a reason why not.
But just going back a little bit, about my question regarding the while within the try-catch block. My point was that I couldn't see any method within the while loop that would throw an SQLException. For instance, getting an SQLState from an SQLException shouldn't throw another SQLException. Therefore you wouldn't need the try-catch block. Do you agree ?
Paulo
[ March 05, 2003: Message edited by: Paulo Salgado ]
[ March 05, 2003: Message edited by: Paulo Salgado ]