DB2 10.5 for Linux, UNIX, and Windows

Handling an SQLException under the IBM Data Server Driver for JDBC and SQLJ

As in all Java™ programs, error handling for JDBC applications is done using try/catch blocks. Methods throw exceptions when an error occurs, and the code in the catch block handles those exceptions.

Procedure

The basic steps for handling an SQLException in a JDBC program that runs under the IBM® Data Server Driver for JDBC and SQLJ are:

Give the program access to the com.ibm.db2.jcc.DB2Diagnosable interface and the com.ibm.db2.jcc.DB2Sqlca class. You can fully qualify all references to them, or you can import them:
```
import com.ibm.db2.jcc.DB2Diagnosable;
import com.ibm.db2.jcc.DB2Sqlca;
```
Optional: During a connection to a data server, set the retrieveMessagesFromServerOnGetMessage property to true if you want full message text from an SQLException.getMessage call.
Optional: During a IBM Data Server Driver for JDBC and SQLJ type 2 connectivity connection to a DB2® for z/OS® data source, set the extendedDiagnosticLevel property to EXTENDED_DIAG_MESSAGE_TEXT (241) if you want extended diagnostic information similar to the information that is provided by the SQL GET DIAGNOSTICS statement from an SQLException.getMessage call.
Put code that can generate an SQLException in a try block.
In the catch block, perform the following steps in a loop:
1. Test whether you have retrieved the last SQLException. If not, continue to the next step.
2. Optional: For an SQL statement that executes on an IBM Informix® data source, execute the com.ibm.db2.jcc.DB2Statement.getIDSSQLStatementOffSet method to determine which columns have syntax errors.
  DB2Statement.getIDSSQLStatementOffSet returns the offset into the SQL statement of the first syntax error.
3. Optional: For an SQL statement that executes on an IBM Informix data source, execute the SQLException.getCause method to retrieve any ISAM error messages.
  1. If the Throwable that is returned by SQLException.getCause is not null, perform one of the following sets of steps:
    - Issue SQLException.printStackTrace to print an error message that includes the ISAM error message text. The ISAM error message text is preceded by the string "Caused by:".
    - Retrieve the error code and message text for the ISAM message:
      1. Test whether the Throwable is an instance of an SQLException. If so, retrieve the SQL error code from that SQLException.
      2. Execute the Throwable.getMessage method to retrieve the text of the ISAM message.
4. Check whether any IBM Data Server Driver for JDBC and SQLJ-only information exists by testing whether the SQLException is an instance of DB2Diagnosable. If so:
  1. Cast the object to a DB2Diagnosable object.
  2. Optional: Invoke the DB2Diagnosable.printTrace method to write all SQLException information to a java.io.PrintWriter object.
  3. Invoke the DB2Diagnosable.getThrowable method to determine whether an underlying java.lang.Throwable caused the SQLException.
  4. Invoke the DB2Diagnosable.getSqlca method to retrieve the DB2Sqlca object.
  5. Invoke the DB2Sqlca.getSqlCode method to retrieve an SQL error code value.
  6. Invoke the DB2Sqlca.getSqlErrmc method to retrieve a string that contains all SQLERRMC values, or invoke the DB2Sqlca.getSqlErrmcTokens method to retrieve the SQLERRMC values in an array.
  7. Invoke the DB2Sqlca.getSqlErrp method to retrieve the SQLERRP value.
  8. Invoke the DB2Sqlca.getSqlErrd method to retrieve the SQLERRD values in an array.
  9. Invoke the DB2Sqlca.getSqlWarn method to retrieve the SQLWARN values in an array.
  10. Invoke the DB2Sqlca.getSqlState method to retrieve the SQLSTATE value.
  11. Invoke the DB2Sqlca.getMessage method to retrieve error message text from the data source.
5. Invoke the SQLException.getNextException method to retrieve the next SQLException.

Example

The following code demonstrates how to obtain IBM Data Server Driver for JDBC and SQLJ-specific information from an SQLException that is provided with the IBM Data Server Driver for JDBC and SQLJ. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 1. Processing an SQLException under the IBM Data Server Driver for JDBC and SQLJ

import java.sql.*;                      // Import JDBC API package
import com.ibm.db2.jcc.DB2Diagnosable;  // Import packages for DB2       1 
import com.ibm.db2.jcc.DB2Sqlca;        // SQLException support
java.io.PrintWriter printWriter;        // For dumping all SQLException
                                        // information
String url = "jdbc:db2://myhost:9999/myDB:" +                           2 
  "retrieveMessagesFromServerOnGetMessage=true;";
                                  // Set properties to retrieve full message
                                  // text
String user = "db2adm"; 
String password = "db2adm";
java.sql.Connection con = 
  java.sql.DriverManager.getConnection (url, user, password) 
                                  // Connect to a DB2 for z/OS data source


…
try {                                                                    4 
  // Code that could generate SQLExceptions
 …
} catch(SQLException sqle) {
    while(sqle != null) {             // Check whether there are more    5a 
                                      // SQLExceptions to process
 //=====> Optional IBM Data Server Driver for JDBC and SQLJ-only
 // error processing
      if (sqle instanceof DB2Diagnosable) {                              5d 
      // Check if IBM Data Server Driver for JDBC and SQLJ-only
      // information exists
          com.ibm.db2.jcc.DB2Diagnosable diagnosable = 
            (com.ibm.db2.jcc.DB2Diagnosable)sqle;                        5d1 
          diagnosable.printTrace (printWriter, "");                      5d2 
          java.lang.Throwable throwable = 
            diagnosable.getThrowable();                                  5d3 
          if (throwable != null) {
            // Extract java.lang.Throwable information 
            // such as message or stack trace.
            …
          }
          DB2Sqlca sqlca = diagnosable.getSqlca();                       5d4 
                                            // Get DB2Sqlca object
          if (sqlca != null) {              // Check that DB2Sqlca is not null
            int sqlCode = sqlca.getSqlCode(); // Get the SQL error code  5d5 
            String sqlErrmc = sqlca.getSqlErrmc();                       5d6 
                                            // Get the entire SQLERRMC
            String[] sqlErrmcTokens = sqlca.getSqlErrmcTokens();
                                            // You can also retrieve the
                                            // individual SQLERRMC tokens
            String sqlErrp = sqlca.getSqlErrp();                         5d7 
                                            // Get the SQLERRP
            int[] sqlErrd = sqlca.getSqlErrd();                          5d8 
                                            // Get SQLERRD fields
            char[] sqlWarn = sqlca.getSqlWarn();                         5d9 
                                            // Get SQLWARN fields
            String sqlState = sqlca.getSqlState();                       5d10 
                                            // Get SQLSTATE
            String errMessage = sqlca.getMessage();                      5d11 
                                            // Get error message

            System.err.println ("Server error message: " + errMessage);
 
            System.err.println ("--------------- SQLCA ---------------");
            System.err.println ("Error code: " + sqlCode);
            System.err.println ("SQLERRMC: " + sqlErrmc);
            If (sqlErrmcTokens != null) {
              for (int i=0; i< sqlErrmcTokens.length; i++) {
                System.err.println ("  token " + i + ": " + sqlErrmcTokens[i]);
              }
            }
            System.err.println ( "SQLERRP: " + sqlErrp );
            System.err.println (
              "SQLERRD(1): " + sqlErrd[0] + "\n" +
              "SQLERRD(2): " + sqlErrd[1] + "\n" +
              "SQLERRD(3): " + sqlErrd[2] + "\n" +
              "SQLERRD(4): " + sqlErrd[3] + "\n" +
              "SQLERRD(5): " + sqlErrd[4] + "\n" +
              "SQLERRD(6): " + sqlErrd[5] );
            System.err.println (
              "SQLWARN1: " + sqlWarn[0] + "\n" +
              "SQLWARN2: " + sqlWarn[1] + "\n" +
              "SQLWARN3: " + sqlWarn[2] + "\n" +
              "SQLWARN4: " + sqlWarn[3] + "\n" +
              "SQLWARN5: " + sqlWarn[4] + "\n" +
              "SQLWARN6: " + sqlWarn[5] + "\n" +  
              "SQLWARN7: " + sqlWarn[6] + "\n" +
              "SQLWARN8: " + sqlWarn[7] + "\n" +
              "SQLWARN9: " + sqlWarn[8] + "\n" +
              "SQLWARNA: " + sqlWarn[9] );
            System.err.println ("SQLSTATE: " + sqlState);
                                            // portion of SQLException
          }
      sqle=sqle.getNextException();     // Retrieve next SQLException       5e 
    }
}