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.

Topics

• Scope of WHENEVER Statements
• Changing Error-handling Routines
• Limitations of WHENEVER Statements