Managing Errors and Warnings | JDBC 3: Java Database Connectivity

JDBC uses the exception mechanism to pass error or warning information to programs. This mechanism signals an abnormal condition that may be handled to prevent a program termination. The exception mechanism is used when a recoverable error occurs and it must be caught to correct the situation.

SQLException

When a database access error occurs, a SQLException is thrown that provides information on the error. Each SQLException provides several kinds of information:

A description of the error, used as the Java Exception message.
A SQLstate string that conforms to the XOPEN SQLstate conventions.
A vendor-specific error code, usually the actual DBMS error code.
A chain to a next exception, if any.

SQLExceptions can occur when trying to connect to a database with an invalid login or password or when trying to access a nonexistent database object such as a table, a table’s column, or a stored procedure. They can also occur during SQL updates and inserts when values provided for the columns are too large, for example, or when types don’t match or can’t be converted implicitly. There can be several other reasons for SQLExceptions to occur; you should refer to the driver’s documentation for specific exception codes and exception messages.

The following methods may be used within a program on a SQLException object:

SQLException Methods

String getMessage(); String getSQLState(); int getErrorCode(); SQLException getNextException();

The message that is used as the Java exception message is available via the method in the first line of the preceding and is returned as a string.

The method getSQLstate in the second line of the preceding conforms to the XOPEN SQLstate definition.

The method in the third line of the preceding returns the vendor-specific exception code. You should refer to the driver documentation for more information about the possible error codes.

The method in the last line of the preceding, SQLException getNextException();, is used to get the SQLException chained to the current one. Indeed, several SQLExceptions can be chained together when a problem causes other problems to arise within the database or driver.

In all the earlier listing examples in this chapter, you are able to catch SQLExceptions without doing anything special after you catch them. The next example, Listing 6-27, shows how to use exception information. Because exceptions signal an abnormal condition, you use the provided information to try to continue the program execution.

Listing 6-27: Catching SQLExceptions

// catching SQLExceptions import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                              DriverManager.getConnection(url,                             "javauser", "hotjava");                           Statement myStatement = myConnection.createStatement();                           ResultSet rs = myStatement.executeQuery(                             "SELECT * FROM employees");                           // ...                           myStatement.close();                           myConnection.close();                  }                  catch(SQLException ex)                  {                           // there may be multiple error objects                           // chained together                           System.out.println("\n*** SQLException caught ***\n");                           while (ex != null)                           {                                   System.out.println("SQLState: "                                      + ex.getSQLState());                                   System.out.println("Message: "                                      + ex.getMessage());                                   System.out.println("Vendor Code: "                                      + ex.getErrorCode());                                   System.out.println("");                                   ex.getNextException();                           }                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } }

SQLWarning

The SQLWarning class provides information on database access warnings. Such warnings are silently chained to the class instance whose method caused it to be reported until they are fetched with the following method.

SQLWarnings can happen when implicit data type conversions occur during a SQL insert or update, for example, and when the conversions cause data precision loss. If decimal digits are lost, the driver may throw a SQLWarning. This is driver dependent, so I recommend that you verify which SQLWarnings can be thrown in the manuals of your specific JDBC driver.

Connection, Statement, or ResultSet’s getWarnings Method

SQLWarning getWarnings();

SQLWarning getWarnings() returns a SQLWarning object or null if no warning occurred.

The SQLWarning class, which extends SQLException, provides the following methods.

SQLWarning’s Methods

String getMessage(); String getSQLState(); int getErrorCode(); SQLWarning getNextWarning();

The warning message is available via the method in the first line of the preceding and is returned as a string.

The method getSQLstate in the second line of the preceding conforms to the XOPEN SQLstate definition.

The method in the third line of the preceding returns the vendor-specific warning code. Refer to the vendor’s documentation for more information.

This method in the last line of the preceding, SQLWarning getNextWarning();, is used to get the SQLWarning chained to the current one.

The example in Listing 6-28 shows how to check for warnings. You explicitly call the getWarnings() method on the Connection, statement, and result set objects. The code for checkWarnings() is a generic method to handle all warnings that may occur during the program execution.

Listing 6-28: Checking SQLWarnings

// checking SQLWarnings import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                              DriverManager.getConnection(url,                           "javauser", "hotjava");                           checkWarnings(myConnection.getWarnings());                           Statement myStatement = myConnection.createStatement();                           checkWarnings(myStatement.getWarnings());                           ResultSet rs = myStatement.executeQuery(                             "SELECT * FROM employees");                           checkWarnings(rs.getWarnings());                           // ...                           myStatement.close();                           myConnection.close();                  }                  catch(SQLException ex)                  {                           // there may be multiple error objects                           // chained together                           System.out.println("\n*** SQLException caught ***\n");                           while (ex != null)                           {                                   System.out.println("SQLState: "                                      + ex.getSQLState());                                   System.out.println("Message: "                                      + ex.getMessage());                                   System.out.println("Vendor Code: "                                      + ex.getErrorCode());                                   System.out.println("");                                   ex.getNextException();                           }                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          }          private static void checkWarnings(SQLWarning warn) throws SQLException          {                  if (warn != null)                  {                           // there may be multiple warnings chained                           // together                           System.out.println("\n*** SQLWarning caught ***\n");                           while (warn != null)                           {                                   System.out.println("SQLState: "                                      + warn.getSQLState());                                   System.out.println("Message: "                                      + warn.getMessage());                                   System.out.println("Vendor Code: "                                      + warn.getErrorCode());                                   System.out.println("");                                   warn.getNextWarning();                           }                  }          } }

Data truncation

When JDBC unexpectedly truncates data on a read, a data truncation warning is reported. When it occurs on a write, a data truncation exception is thrown. In both cases, the SQLstate is set to 01004.

The following set of methods is available to discover what happened:

DataTruncation’s Methods

int getDataSize(); int getTransferSize(); int getIndex(); boolean getParameter(); boolean getRead();

The method in the first line of the preceding returns the number of bytes that should have been transferred. It returns -1 if the size is unknown. The size may be an approximation if data conversions occurred.

The method in the second line of the preceding returns the number of bytes actually transferred. A -1 means that the size is unknown.

The method in the third line of the preceding gets the index of the column or parameter that was truncated. A -1 means that the index is unknown, in which case the next two methods in this list should be ignored.

boolean getParameter(); returns true if the value truncated was passed through a statement’s parameter. It returns false if it was returned by a column.

boolean getRead(); returns true if the data truncation occurred on a database read. It returns false if the data was truncated on a write. Listing 6-29 shows how to catch a data truncation exception.

Listing 6-29: Catching a Data Truncation Exception

... ... { try {          // initiate a connection,          // then execute a statement          // ... } catch(DataTruncation ex) {          System.out.println("\n*** DataTruncationexception caught ***\n");          int idx = ex.getIndex();          System.out.print("Index: " + idx);          if (idx != -1)          {                  if (ex.getParameter())                  {                           // the truncation happened                           // in a parameter                           System.out.print(" of the set of parameters");                  }                  else                  {                           // the truncation happened                           // on a resultset column                           System.out.print(" in the resultset");                  }          }          if (ex.getRead())          {                  // the truncation happened                  // on a read                  System.out.println(" was truncated on a read");          }          else          {                  // the truncation happened                  // on a write                  System.out.println(" was truncated on a write");          }          System.out.println("It was: " + ex.getDataSize() + " bytes long");          System.out.println("Actual size: " + ex.getTransferSize()             + " bytes transferred");          System.out.println(""); } ... ...