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 SQLERRORis activated whenSQLCODEis less than zero, indicating that a statement failed.WHENEVER SQLWARNINGis activated whenSQLCODEcontains a value from 1 to 99, inclusive, indicating that while a statement executed, there is some question about the way it succeeded.WHENEVER NOT FOUNDis activated whenSQLCODEis 100, indicating that end-of-file was reached during aFETCHorSELECT.
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 CONTINUEat 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.