You use WHENEVER to define the reaction to statements terminated with an SQLSTATE ≠ '00000' and ≠ '01 xxx’.
WHENEVER is not an executable statement.
You can specify the WHENEVER statement more than once in a program. The specifications made in a WHENEVER statement are valid for all subsequent SQL and utility statements in the program text (after all includes have been inserted) until the next WHENEVER statement for the same error class.
WHENEVER...CONTINUE is valid before the first WHENEVER statement.
WHENEVER
{ SQLERROR | NOT FOUND }
{ CONTINUE | { GOTO | GO TO }[:] label }
SQLERROR
Define handling of:
SQLSTATE ≠ “00000”, “01xxx” and “02xxx”.
NOT FOUND
Define handling of:
SQLSTATE = “02xxx”.
CONTINUE
After SQLERROR or NOT FOUND, the program is continued with the next statement. You can use CONTINUE to cancel a previously defined action for the same error class.
If the program section for error handling includes SQL statements, this section should be introduced by a WHENEVER statement with a CONTINUE clause. This avoids endless loops if the error occurs again.
label
Label in an ESQL program.
This clause corresponds to a branch statement in the host language (e.g. GO TO in COBOL).
label must conform to the naming conventions for labels of the host language involved (see the “ ESQL-COBOL for SESAM/SQL-Server” manual).
After SQLERROR or NOT FOUND, the program is continued at the location indicated by label.
The colon : is only supported for reasons of upward compatibility.
Examples
Continue the program with the paragraph SQLERR following a statement that ends with an SQLSTATE ≠'00000', '01xxx' or '02xxx'.
WHENEVER SQLERROR GOTO sqlerr
This example demonstrates how to use the WHENEVER statement when reading a cursor table with FETCH.
Before positioning the cursor, you define a label to cater for situations where the specified cursor position does not exist.
The cursor is positioned on the next row within a loop.
The program works its way through the cursor table reading each row until it reaches the end of the table.
If the specified position does not exist, a corresponding SQLSTATE is set and the program is continued from the label defined in the WHENEVER statement.
The action defined for error handling is cancelled at the label.
WHENEVER NOT FOUND GOTO F-4
F-2.
FETCH cur_contacts INTO :LNAME,
:FIRSTNAME INDICATOR :IND_FIRSTNAME,
:DEPT INDICATOR :IND_DEPT
F-3.
Output row, go to F-2
F-4.
WHENEVER NOT FOUND CONTINUE