How to Handle SQL Exceptions - dummies

How to Handle SQL Exceptions

By Allen G. Taylor

When trying to determine the source of an SQL error and SQLSTATE indicates an exception condition by holding a value other than 00000, 00001, or 00002, you may want to handle the situation in one of the following ways:

  • Return control to the parent procedure that called the subprocedure that raised the exception.

  • Use a WHENEVER clause to branch to an exception-handling routine or perform some other action.

  • Handle the exception on the spot with a compound SQL statement. A compound SQL statement consists of one or more simple SQL statements, sandwiched between BEGIN and END keywords.

The following is an example of a compound-statement exception handler:

  (:cal) ;
 SIGNAL ValueOutOfRange ;
 MESSAGE 'Process a new calorie value.'
  WHEN ValueOutOfRange THEN
   MESSAGE 'Handling the calorie range error' ;

With one or more DECLARE statements, you can give names to specific SQLSTATE values that you suspect may arise. The INSERT statement is the one that might cause an exception to occur. If the value of :cal exceeds the maximum value for a SMALLINT data item, SQLSTATE is set to “73003”. The SIGNAL statement signals an exception condition. It clears the top diagnostics area.

It sets the RETURNED_SQLSTATE field of the diagnostics area to the SQLSTATE for the named exception. If no exception has occurred, the series of statements represented by the MESSAGE ‘Process a new calorie value’ statement is executed. However, if an exception has occurred, that series of statements is skipped, and the EXCEPTION statement is executed.

If the exception was a ValueOutOfRange exception, then a series of statements represented by the MESSAGE ‘Handling the calorie range error’ statement is executed. The RESIGNAL statement is executed if the exception isn’t a ValueOutOfRange exception.

RESIGNAL merely passes control of execution to the calling parent procedure. That procedure may have additional error-handling code to deal with exceptions other than the expected value-out-of-range error.