Exception-Handling Basics

This section covers the basics of handling exceptions in your SQLJ application, including requirements for error-checking.

SQLJ/JDBC Exception-Handling Requirements

Because SQLJ executable statements result in JDBC calls through sqlj.runtime, and JDBC requires SQL exceptions to be caught or thrown, SQLJ also requires SQL exceptions to be caught or thrown in any block containing SQLJ executable statements. Your source code will generate errors during compilation if you do not include appropriate exception-handling.

Handling SQL exceptions requires the java.sql.SQLException class. You can either import it as follows, or fully qualify its name whenever you need it:

import java.sql.SQLException;        // or optionally java.sql.*

Example: Exception Handling

This example demonstrates the kind of basic exception-handling that is required of SQLJ applications, with a main method with a try/catch block, and another method which is called from main and throws exceptions back to main when they are encountered.

/* Import SQLExceptions class.  The SQLException comes from
   JDBC. Executable #sql clauses result in calls to JDBC, so methods
   containing executable #sql clauses must either catch or throw
   SQLException.  
 */
import java.sql.SQLException ;
import oracle.sqlj.runtime.Oracle;

// iterator for the select

#sql iterator MyIter (String ITEM_NAME);

class TestInstallSQLJ 
{
  //Main method
  public static void main (String args[]) 
  {
    try { 
      /* if you're using a non-Oracle JDBC Driver, add a call here to
         DriverManager.registerDriver() to register your Driver
      */

      // set the default connection to the URL, user, and password
      // specified in your connect.properties file
      Oracle.connect(TestInstallSQLJ.class, "connect.properties");

      TestInstallSQLJ ti = new TestInstallSQLJ();
      ti.runExample(); 
    } catch (SQLException e) { 
      System.err.println("Error running the example: " + e);
    }

  } //End of method main

  //Method that runs the example
  void runExample() throws SQLException
  {
      //Issue SQL command to clear the SALES table
    #sql { DELETE FROM SALES };
    #sql { INSERT INTO SALES(ITEM_NAME) VALUES ('Hello, SQLJ!')};

    MyIter iter;
    #sql iter = { SELECT ITEM_NAME FROM SALES };

    while (iter.next()) {
      System.out.println(iter.ITEM_NAME());
    }
  }
}

Processing Exceptions

This section discusses ways to process and interpret exceptions in your SQLJ application. During runtime, exceptions may come from any of the following:

• SQLJ runtime

• JDBC driver

• RDBMS

Printing Error Text

The example in the previous section showed how to catch SQL exceptions and output the error messages, which is repeated again here:

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
}
...

This will print the error text from the SQLException object.

For error messages from the SQLJ runtime, see "Runtime Messages". You can also retrieve SQL state information, as described below.

For error messages from the Oracle JDBC driver, see the Oracle8i JDBC Developer's Guide and Reference.

For error messages from the Oracle RDBMS, which will be preceded by the prefix ORA-xxxxx, where xxxxx is a five-digit error code, see the Oracle8i Error Messages reference.

Retrieving SQL States and Error Codes

The java.sql.SQLException class and subclasses include the methods getSQLState(), getErrorCode(), and getMessage(). Depending on where the exception came from and how error conditions are implemented there, these methods may provide additional information as follows.

For exceptions from the Oracle SQLJ runtime:

• getSQLState() returns a five-digit string containing the SQL state.

• getErrorCode() returns 0 (no meaningful information).

• getMessage() returns an error message (with no prefix).

For exceptions from the Oracle JDBC drivers:

• getSQLState() returns null (no meaningful information).

• getErrorCode() returns 0 (no meaningful information).

• getMessage() returns an error message (with no prefix).

For exceptions from the RDBMS:

• getSQLState() returns an empty string (no meaningful information).

• getErrorCode() returns the Oracle error code, which is the xxxxx portion of the ORA-xxxxx prefix. (For example, this would return 942 for the message prefixed by ORA-00942.)

• getMessage() returns an error message, including the ORA-xxxxx prefix.

For example, you can use the following error processing. This prints the error message as in the preceding example, but also checks the SQL state.

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
      String sqlState = e.getSQLState();
      System.err.println("SQL state = " + sqlState); 
}
...

SQL states and error messages for SQLJ runtime errors are documented in "Runtime Messages".

Using SQLException Subclasses

For more specific error-checking, use any available and appropriate subclasses of the java.sql.SQLException class.

SQLJ provides one such subclass, the sqlj.runtime.NullException class, which you can catch in situations where a null value might be returned into a Java primitive variable. (Java primitives cannot handle nulls.)

When you use a SQLException subclass, catch the subclass exception first, before catching a SQLException, as in the following example:

...
try {
...
} catch (SQLNullException ne) {
     System.err.println("Null value encountered: " + ne); }
  catch (SQLException e) { 
     System.err.println("Error running the example: " + e); }
...

This is because a subclass exception can also be caught as a SQLException. If you catch SQLException first, then execution would not drop through for any special processing you want to use for the subclass exception.

---