Error Handling with the Whenever Statement

Specifies the error condition. Valid conditions are: sqlwarning, sqlerror, sqlmessage, dbevent, and not found.

Specifies the action to be taken. Valid actions are: continue, stop, goto a COBOL paragraph name, and call a COBOL paragraph name. The call action causes the preprocessor to generate a COBOL PERFORM statement for the specified paragraph name.

If the paragraph name in a goto or call action is an embedded SQL reserved word, specify it in quotes. The paragraph name targeted by the goto or call action must be in the scope of all subsequent embedded SQL statements until another whenever statement is encountered for the same action. This is necessary because when the preprocessor interprets a whenever goto statement, it generates the COBOL statement:

after an embedded SQL statement. Similarly, in interpreting a whenever call statement, the preprocessor generates the COBOL statement:

after subsequent embedded SQL statements. If the paragraph name is invalid, the COBOL compiler generates an error.

You can also use user‑defined handlers for error handling. For more information, see the SQL Reference Guide. Note that the reserved procedure sqlprint, which can substitute for a paragraph name in a whenever call statement, is always in the scope of the program.

When the condition specified for a call action occurs, control passes to the first statement in the named paragraph. After the last statement contained in the paragraph has been executed, control returns to the statement following the statement that caused the call to occur. Consequently, after handling the whenever condition in the called paragraph, you may want to take some action, instead of merely allowing execution to continue with the statement following the embedded SQL statement that generated the error.

The following example demonstrates use of the whenever statement in the context of printing some values from the employee table. The comments do not relate to the program but to the use of error handling.

WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
     01 E-REC.
         02 ENO      PIC S9(8) USAGE DISPLAY.
         02 FILLER   PIC X(2) VALUE SPACES.
         02 ENAME    PIC X(20).
         02 AGE      PIC S9(4) USAGE DISPLAY.
      01 ERRMSG      PIC X(200).
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
BEGIN.
        EXEC SQL DECLARE empcsr CURSOR FOR
            SELECT eno, ename, age
            FROM employee
            END-EXEC.

* An error when opening the "personnel" database will
* cause the error to be printed and the program to
* abort.
        EXEC SQL WHENEVER SQLERROR STOP END-EXEC.
        EXEC SQL CONNECT personnel END-EXEC.
* Errors from here on will cause the program to clean up
         EXEC SQL WHENEVER SQLERROR
             GOTO CLEAN-UP END-EXEC.
         EXEC SQL OPEN empcsr END-EXEC.
         DISPLAY "Some values from
                      the ""employee"" table".

* When no more rows are fetched, close the cursor
EXEC SQL WHENEVER NOT FOUND GOTO CLOSE-CSR
END-EXEC.
* The last statement was an OPEN, so we know that the
* value of SQLCODE cannot be SQLERROR or NOT FOUND.
* Loop is broken by NOT FOUND

        PERFORM UNTIL SQLCODE NOT = 0
            EXEC SQL FETCH empcsr
                INTO :ENO, :ENAME, :AGE END-EXEC
* The DISPLAY does not execute after the previous FETCH * returns the NOT FOUND condition.
            DISPLAY E-REC
        END-PERFORM.

* From this point in the file onwards, ignore all
* errors. Also, turn off the NOT FOUND condition,
* for consistency.
        EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
        EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
    CLOSE-CSR.
        EXEC SQL CLOSE empcsr END-EXEC.
        EXEC SQL DISCONNECT END-EXEC.
        STOP RUN.
    CLEAN-UP.
        EXEC SQL INQUIRE_SQL(:ERRMSG = ERRORTEXT)
            END-EXEC.
        DISPLAY "Aborting because of error".
        DISPLAY ERRMSG.
        EXEC SQL DISCONNECT END-EXEC.
        STOP RUN.