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.