Exception-Handling Basics

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

SQLJ and 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, which will be available to you if you import the java.sql.* package.

Example: Exception Handling

This example demonstrates the kind of basic exception-handling 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 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.* ;
import oracle.sqlj.runtime.Oracle;

// iterator for the select

#sql iterator MyIter (String ITEM_NAME);

public 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

Errors originating in the SQLJ runtime are listed in "Runtime Messages".

Errors originating in the Oracle JDBC driver are listed in the Oracle8i JDBC Developer's Guide and Reference.

Errors originating in the Oracle RDBMS are listed in the Oracle8i Error Messages reference.

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.

You can also retrieve error information using the SQLException class getMessage(), getErrorCode(), and getSQLState() methods, as described in the next section.

Printing the error text as in this example prints the error message with some additional text, such as "SQLException".

Retrieving SQL States and Error Codes

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

• getMessage()

  If the error originates in the SQLJ runtime or JDBC driver, this method returns the error message with no prefix. If the error originates in the RDBMS, it returns the error message prefixed by the ORA number.

• getErrorCode()

  If the error originates in the SQLJ runtime, this method returns no meaningful information. If the error originates in the JDBC driver or RDBMS, it returns the five-digit ORA number.

• getSQLState()

  If the error originates in the SQLJ runtime, this method returns a five-digit code indicating the SQL state. If the error originates in the JDBC driver, it returns no meaningful information. If the error originates in the RDBMS, it returns the five-digit SQL state. Your code should be prepared to handle a null return.

The following example 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); 
}
...

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.)

For batch-enabled environments, there is also the standard java.sql.BatchUpdateException subclass. See "Error Conditions During Batch Execution" for further discussion.

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.

---