WHENEVER Statements

Go Up to Standard Error Handling

The WHENEVER statement enables all SQL errors to be handled with a minimum of coding. WHENEVER statements specify error-handling code that a program should execute when SQLCODE indicates errors, warnings, or end-of-file. The syntax of WHENEVER is:

EXEC SQL
WHENEVER {SQLERROR | SQLWARNING | NOT FOUND}
{GOTO label | CONTINUE};

After WHENEVER appears in a program, all subsequent SQL statements automatically jump to the specified code location identified by <label> when the appropriate error or warning occurs.

Because they affect all subsequent statements, WHENEVER statements are usually embedded near the start of a program. For example, the first statement in the following C code’s main() function is a WHENEVER that traps SQL errors:

main()
{
EXEC SQL
WHENEVER SQLERROR GOTO ErrorExit;
. . .
Error Exit:
if (SQLCODE)
{
print_error();
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT;
exit(1);
}
}
. . .
print_error()
{
printf("Database error, SQLCODE = %d\n", SQLCODE);
}

Up to three WHENEVER statements can be active at any time:

WHENEVER SQLERROR is activated when SQLCODE is less than zero, indicating that a statement failed.
WHENEVER SQLWARNING is activated when SQLCODE contains a value from 1 to 99, inclusive, indicating that while a statement executed, there is some question about the way it succeeded.
WHENEVER NOT FOUND is activated when SQLCODE is 100, indicating that end-of-file was reached during a FETCH or SELECT.

Omitting a statement for a particular condition means it is not trapped, even if it occurs. For example, if WHENEVER NOT FOUND is left out of a program, then when a FETCH or SELECT encounters the end-of-file, SQLCODE is set to 100, but program execution continues as if no error condition has occurred.

Error conditions also can be overlooked by using the CONTINUE statement inside a WHENEVER statement:

. . .
EXEC SQL
WHENEVER SQLWARNING
CONTINUE;
. . .

This code traps SQLCODE warning values, but ignores them. Ordinarily, warnings should be investigated, not ignored.

Important:
Use WHENEVER SQLERROR CONTINUE at the start of error-handling routines to disable error handling temporarily. Otherwise, there is a possibility of an infinite loop; should another error occur in the handler itself, the routine will call itself again.

Scope of WHENEVER Statements

WHENEVER only affects all subsequent SQL statements in the <module>, or source code file, where it is defined. In programs with multiple source code files, each module must define its own WHENEVER statements.

Changing Error-handling Routines

To switch to another error-handling routine for a particular error condition, embed another WHENEVER statement in the program at the point where error handling should be changed. The new assignment overrides any previous assignment, and remains in effect until overridden itself. For example, the following program fragment sets an initial jump point for SQLERROR conditions to ErrorExit1, then resets it to ErrorExit2:

EXEC SQL
WHENEVER SQLERROR
GOTO ErrorExit1;
. . .
EXEC SQL
WHENEVER SQLERROR
GOTO ErrorExit2;
. . .

Limitations of WHENEVER Statements

There are two limitations to WHENEVER. It:

Traps errors indiscriminately. For example, WHENEVER SQLERROR traps both missing databases and data entry that violates a CHECK constraint, and jumps to a single error-handling routine. While a missing database is a severe error that may require action outside the context of the current program, invalid data entry may be the result of a typing mistake that could be fixed by reentering the data.
Does not easily enable a program to resume processing at the point where the error occurred. For example, a single WHENEVER SQLERROR can trap data entry that violates a CHECK constraint at several points in a program, but jumps to a single error-handling routine. It might be helpful to allow the user to reenter data in these cases, but the error routine cannot determine where to jump to resume program processing.

Error-handling routines can be very sophisticated. For example, in C or C++, a routine might use a large CASE statement to examine SQLCODE directly and respond differently to different values. Even so, creating a sophisticated routine that can resume processing at the point where an error occurred is difficult. To resume processing after error recovery, consider testing SQLCODE directly after each SQL statement, or consider using a combination of error-handling methods.

Advance To:

Testing SQLCODE Directly