Handling SQL Exceptions

SQL Exception Handling is a mechanism that deals with runtime errors occurring during a database or SQL-related function.

WebLogic maintains a log file named wl-domain in the admin domain directory under a directory called logs. Exceptions that occur while trying to create a connection pool, or MultiPool, and exceptions that occur on server startup, are logged in this file. However, during the course of your application development, many more errors will be occurring. These errors will have to be handled in order to ensure the smooth running of your application.

Exceptions in the case of JDBC applications can be handled at two places, the first being the database end itself, and the second being the application end. It is good practice to handle exceptions at both ends. The programming industry's best practices for Java applications say that each code block should be enclosed in a try-catch-finally block. Ideally, the try block contains the code to be executed, the catch block contains the steps to be taken if an error condition occurs (for example, if it logs the specific error stack trace in a log file and rolls back any inconsistent data), and the finally block, in the case of JDBC applications, should close or release connections to the database, if possible.

The Java API provides JDBC support classes, which contain a few classes supporting JDBC core functionalities. One of these is the java.sql.SQLexception class. This class, which extends the java.lang.Exception, helps to identify more information about the database SQL errors.

Using this API, you can obtain the following information:

• SQL error code: This gives the error code thrown by the database. Because this error code is specific to the DBMS, to get the details of this error code you will need to look up the documentation of the DBMS. To do this, use the method getErrorCode().

• SQL error message: This is the description of the error message. The error code obtained above may be pretty vague because it is specific to the database. However, the getMessage() method gives a sufficiently descriptive message.

• SQL state: The XOPEN SQL specifications define a set of values for an error, based on the XOPEN SQL state conventions. Using the getSQLState() method, you can get the value of this SQLState string.

Note

X/Open is a worldwide group of system vendors, independent software vendors, and major end users who have worked toward developing a vendor-independent, open operating environment based on certain default and international standards, which are called the X/Open portability guide or XPG. This group is striving to achieve database access standardization.

These values are obtained in the catch block. Besides these, the methods of java.lang.Exception also can be used to get a list of exceptions.

In a scenario where you may want the user to be notified of what the error is, but you do not wish to stop the application's progress, getNextException() can be used. This is similar to getting a stack trace of the errors occurring without stopping the progress of the application.

The following code snippet shows how the previous methods are used:

 try{         ...        //database related  function like obtaining a connection to the        //database, etc } catch(SQLException sqlEx){        while(!sqlEx==null){               System.out.println("The SQL Error Code is:"                       + sqlEx.getErrorCode());               System.out.println("The SQL Error Message is:"                       + sqlEx.getMessage());               System.out.println("The SQL State String is:"                       + sqlEx.getSQLState());               sqlEx= sqlEx.getNextException();        } } finally{        if(conn!=null){               try{               conn.close();               }               catch(Exception e){                      System.out.println("error occurring while attempting to                              close connection:");                      System.out.println(e.getMessage());                      System.out.println(e.printStackTrace());               }               } } 

A few commonly occurring errors that may result in exceptions are:

• The table you are trying to access through your application may be opened elsewhere through another application, and a lock on the table has been issued. Normally, the DBMS manages the locks and multiple accesses to the database. However, if you have explicitly opened the table from some other application, you may encounter this error.

• The table name, schema name, or column names have been misspelled.

• In case of inserts, if you try to insert duplicates into a column that has the unique constraint, or if you try to ignore inserting a value into a not nulls field with no default value specified, you could get errors on your inserting function.

• In batch updates, a select statement will return a SQLException because only statements returning an update count can be executed.