Errors and warnings in procedures and triggers

Chapter 20. Using Procedures, Triggers, and Batches
Errors and warnings in procedures and triggers

After an application program executes a SQL statement, it can examine a return code. This return code indicates whether the statement executed successfully or failed and gives the reason for the failure. The same mechanism can be used to indicate the success or failure of a CALL statement to a procedure.

SQL Anywhere supports the SQLCODE and SQLSTATE status descriptions. For full descriptions of SQLCODE and SQLSTATE error and warning values and their meanings, see the chapter "SQL Anywhere Database Error Messages". Whenever a SQL statement is executed, a value is placed in special procedure variables called SQLSTATE and SQLCODE. That value indicates whether or not there were any unusual conditions encountered while the statement was being performed. You can check the value of SQLSTATE or SQLCODE in an IF statement following a SQL statement, and take actions depending on whether the statement succeeded or failed.

For example, the SQLSTATE variable can be used to indicate if a row is successfully fetched. The TopCustomerValue procedure presented in section "Using cursors on SELECT statements in procedures" used the SQLSTATE test to detect that all rows of a SELECT statement had been processed.

Possible values for the SQLSTATE and SQLCODE variables are listed in the chapter "SQL Anywhere Database Error Messages".

Default error handling in procedures and triggers

This section describes how SQL Anywhere handles errors that occur during a procedure execution, if you have no error handling built in to the procedure.

If you want to have different behavior from that described in this section, you can use exception handlers, described in "Using exception handlers in procedures and triggers". Warnings are handled in a slightly different manner from errors: for a description, see "Default handling of warnings in procedures and triggers".

There are two ways of handling errors without using explicit error handling:

Default error handling The procedure or trigger fails and returns an error code to the calling environment.
ON EXCEPTION RESUME If the ON EXCEPTION RESUME clause is included in the CREATE PROCEDURE statement, the procedure carries on executing after an error, resuming at the statement following the one causing the error.

Default error handling

Generally, if a SQL statement in a procedure or trigger fails, the procedure or trigger terminates execution and control is returned to the application program with an appropriate setting for the SQLSTATE and SQLCODE values. This is true even if the error occurred in a procedure or trigger invoked directly or indirectly from the first one. In the case of a trigger, the operation causing the trigger is also undone and the error is returned to the application.

The following demonstration procedures show what happens when an application calls the procedure OuterProc and OuterProc in turn calls the procedure InnerProc, which then encounters an error.

     CREATE PROCEDURE OuterProc()
     BEGIN
         MESSAGE 'Hello from OuterProc.';
         CALL InnerProc();
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE,' in OuterProc.'
     END
     CREATE PROCEDURE InnerProc()
         BEGIN
             DECLARE column_not_found
                 EXCEPTION FOR SQLSTATE '52003';
             MESSAGE 'Hello from InnerProc.';
             SIGNAL column_not_found;
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE, ' in InnerProc.';
     END

Notes

The DECLARE statement in InnerProc declares a symbolic name for one of the predefined SQLSTATE values associated with error conditions already known to the database engine. The DECLARE statement does not take any other action.
The MESSAGE statement sends a message to the database engine or server message window.
The SIGNAL statement generates an error condition from within the InnerProc procedure.

The following statement executes the OuterProc procedure:

     CALL OuterProc();

The message window of the database engine then displays the following:

Hello from OuterProc.

Hello from InnerProc.

No statements following the SIGNAL statement in InnerProc are executed: InnerProc immediately passes control back to the calling environment, which in this case is the procedure OuterProc. The error condition is returned to the calling environment to be handled there. For example, the ISQL utility handles the error by displaying a message window describing the error.

The TRACEBACK function provides a list of the statements that were executing when the error occurred. You can use the TRACEBACK function from ISQL by typing the following statement:

     SELECT TRACEBACK(*)

Error handling with ON EXCEPTION RESUME

If the ON EXCEPTION RESUME clause is included in the CREATE PROCEDURE statement, the procedure does not return control to the calling environment when an error occurs. Instead, it continues executing, resuming at the statement after the one causing the error.

The following demonstration procedures show what happens when an application calls the procedure OuterProc; and OuterProc in turn calls the procedure InnerProc, which then encounters an error. These demonstration procedures are based on those used earlier in this section:

     CREATE PROCEDURE OuterProc()
     ON EXCEPTION RESUME
     BEGIN
         MESSAGE 'Hello from OuterProc.';
         CALL InnerProc();
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE,' in OuterProc.';
     END;
     
     CREATE PROCEDURE InnerProc()
     ON EXCEPTION RESUME
     BEGIN
         DECLARE column_not_found
             EXCEPTION FOR SQLSTATE '52003';
         MESSAGE 'Hello from InnerProc.';
         SIGNAL column_not_found;
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE, ' in InnerProc.';
     END

The following statement executes the OuterProc procedure:

     CALL OuterProc();

The message window of the database engine then displays the following:

Hello from OuterProc.

Hello from InnerProc.

SQLSTATE set to 52003 in InnerProc.

SQLSTATE set to 52003 in OuterProc.

Default handling of warnings in procedures and triggers

Warnings are handled differently from errors. While the default action for errors is to set a value for the SQLSTATE and SQLCODE variables, and return control to the calling environment, the default action for warnings is to set the SQLSTATE and SQLCODE values and continue execution of the procedure.

The following demonstration procedures illustrate default handling of warnings. These demonstration procedures are based on those used in "Default error handling in procedures and triggers". In this case, the SIGNAL statement generates a row not found condition, which is a warning rather than an error.

     CREATE PROCEDURE OuterProc()
     BEGIN
         MESSAGE 'Hello from OuterProc.';
         CALL InnerProc();
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE,' in OuterProc.';
     END
     CREATE PROCEDURE InnerProc()
     BEGIN
         DECLARE row_not_found
             EXCEPTION FOR SQLSTATE '02000';
         MESSAGE 'Hello from InnerProc.';
         SIGNAL row_not_found;
         MESSAGE 'SQLSTATE set to ',
         SQLSTATE, ' in InnerProc.';
     END

The following statement executes the OuterProc procedure:

     CALL OuterProc();

The message window of the database engine then displays the following:

Hello from OuterProc.

Hello from InnerProc.

SQLSTATE set to 02000 in InnerProc.

SQLSTATE set to 02000 in OuterProc.

The procedures both continued executing after the warning was generated, with SQLSTATE set to the value set by the warning (02000).

Using exception handlers in procedures and triggers

It is often desirable to intercept certain types of errors and handle them within a procedure or trigger, rather than pass the error back to the calling environment. This is done through the use of an exception handler.

An exception handler is defined with the EXCEPTION part of a compound statement (see "Using compound statements"). The exception handler is executed whenever an error occurs in the compound statement. Unlike errors, warnings do not cause exception handling code to be executed. Exception handling code is also executed if an error is encountered in a nested compound statement or in a procedure or trigger that has been invoked anywhere within the compound statement.

The demonstration procedures used to illustrate exception handling are based on those used in "Default error handling in procedures and triggers". In this case, additional code is added to handle

the column not found

error in the InnerProc procedure.

     CREATE PROCEDURE OuterProc()
     BEGIN
         MESSAGE 'Hello from OuterProc.';
         CALL InnerProc();
         MESSAGE 'SQLSTATE set to ',
             SQLSTATE,' in OuterProc.'
     END
     CREATE PROCEDURE InnerProc()
     BEGIN
         DECLARE column_not_found
             EXCEPTION FOR SQLSTATE '52003';
         MESSAGE 'Hello from InnerProc.';
         SIGNAL column_not_found;
         MESSAGE 'Line following SIGNAL.';
         EXCEPTION
             WHEN column_not_found THEN
                 MESSAGE 'Column not found handling.';
             WHEN OTHERS THEN
         RESIGNAL ;
     END

The EXCEPTION statement declares the exception handler itself. The lines following the EXCEPTION statement are not executed unless an error occurs. Each WHEN clause specifies an exception name (declared with a DECLARE statement) and the statement or statements to be executed in the event of that exception. The WHEN OTHERS THEN clause specifies the statement(s) to be executed when the exception that occurred is not in the preceding WHEN clauses.

In this example, the statement RESIGNAL passes the exception on to a higher-level exception handler. RESIGNAL is the default action if WHEN OTHERS THEN is not specified in an exception handler.

The following statement executes the OuterProc procedure:

     CALL OuterProc();

The message window of the database engine then displays the following:

Hello from OuterProc.

Hello from InnerProc.

Column not found handling.

SQLSTATE set to 00000 in OuterProc.

Notes

The lines following the SIGNAL statement in InnerProc are not executed; instead, the EXCEPTION statements are executed.
As the error encountered was a
column not found
error, the MESSAGE statement included to handle the error is executed, and SQLSTATE is reset to zero (indicating no errors).
After the exception handling code is executed, control is passed back to OuterProc, which proceeds as if no error was encountered.
You should not use ON EXCEPTION RESUME together with explicit exception handling. The exception handling code is not executed if ON EXCEPTION RESUME is included.
If the error handling code for the
column not found
exception is simply a RESIGNAL statement, control is passed back to the OuterProc procedure with SQLSTATE still set at the value 52003. This is just as if there were no error handling code in InnerProc. As there is no error handling code in OuterProc, the procedure fails.

Exception handling and atomic compound statements

When an exception is handled inside a compound statement, the compound statement completes without an active exception and the changes before the exception are not undone. This is true even for atomic compound statements. If an error occurs within an atomic compound statement and is explicitly handled, some but not all of the statements in the atomic compound statement are executed.

Nested compound statements and exception handlers

The code following a statement that causes an error is not executed unless an ON EXCEPTION RESUME clause is included in a procedure definition.

You can use nested compound statements to give you more control over which statements are and are not executed following an error.

The following demonstration procedure illustrates how nested compound statements can be used to control flow. The procedure is based on that used as an example in "Default error handling in procedures and triggers".

     CREATE PROCEDURE InnerProc()
     BEGIN
         DECLARE column_not_found
             EXCEPTION FOR SQLSTATE VALUE '52003';
             MESSAGE 'Hello from InnerProc';
             SIGNAL column_not_found;
                 MESSAGE 'Line following SIGNAL'
         EXCEPTION
             WHEN column_not_found THEN
                 MESSAGE 'Column not found handling';
             WHEN OTHERS THEN
                 RESIGNAL;
             MESSAGE 'Outer compound statement';
     END

The following statement executes the InnerProc procedure:

     CALL InnerProc();

The message window of the database engine then displays the following:

Hello from InnerProc

Column not found handling

Outer compound statement

When the SIGNAL statement that causes the error is encountered, control passes to the exception handler for the compound statement, and the Column not found handling message is printed. Control then passes back to the outer compound statement and the Outer compound statement message is printed.

If an error other than column not found is encountered in the inner compound statement, the exception handler executes the RESIGNAL statement. The RESIGNAL statement passes control directly back to the calling environment, and the remainder of the outer compound statement is not executed.