whenever

Description

Specifies an action to occur whenever an executable SQL statement causes a specified condition.

Syntax

exec sql whenever {sqlerror | not found | sqlwarning} 
 {continue | go to label | goto label |
 stop | call routine_name [args]} end-exec

Parameters

Examples

Example 1

      EXEC SQL BEGIN DECLARE SECTION END-EXEC.

            01        LNAME      PIC X(15).

            01        FNAME      PIC X(15).

            01        PHONE      PIC X(15).

       EXEC SQL END DECLARE SECTION END-EXEC.

       EXEC SQL WHENEVER SQLERROR PERFORM ERR-PARA END-EXEC.

       EXEC SQL WHENEVER SQLWARNING PERFORM WARN-PARA END-EXEC.

     * If there are no more records to process from the fetch, stop the

     * program.

       EXEC SQL WHENEVER NOT FOUND STOP END-EXEC.

             ...

       EXEC SQL DECLARE au_list CURSOR FOR 

                   SELECT au_lname, au_fname, phone 

                   FROM authors

                   ORDER BY au_lname END-EXEC.

       EXEC SQL OPEN au_list END-EXEC.

       PERFORM FETCH-LOOP UNTIL SQLCODE = 100 END-EXEC.

       EXEC SQL CLOSE au_list END-EXEC.  

             ...

       FETCH-LOOP.

             EXEC SQL FETCH au_list INTO 

                         :LNAME, :FNAME, :PHONE END-EXEC

             DISPLAY "LAST NAME  : ",LNAME

             DISPLAY "FIRST NAME : ",FNAME

             DISPLAY "PHONE      : ",PHONE

       END-FETCH-LOOP.

        WARN-PARA.

             DISPLAY "Warning code is " SQLCODE.

             DISPLAY "Warning message is " SQLERRMC.

                   ...

         WARN-PARA-END.

             EXIT.

         ERR-PARA.

       *

       * print the error code, the error message and the line number of

       * the command that caused the error.

       *

             DISPLAY "Error code is " SQLCODE.

             DISPLAY "Error message is " SQLERRMC.

             EXIT.

Usage

• The whenever statement causes the Embedded SQL precompiler to generate code following each executable SQL statement. The generated code includes the test for the condition and the host language statement or statements that carry out the specified action.

• The Embedded SQL precompiler generates code for the SQL statements that follow the whenever statement in the source file, including SQL statements in subroutines that are defined in the same source file.

• Use whenever…continue to cancel a previous whenever statement. The continue action causes the Embedded SQL precompiler to ignore the condition. To prevent infinite loops, use whenever…continue in an error handler before executing any Embedded SQL statements.

• When you use whenever…go to label, label must represent a valid location to resume execution. In C, for example, label must be declared in any routine that has executable SQL statements within the scope of the whenever statement. C does not allow a goto statement to jump to a label declared in another function.

• If you have a whenever statement in your program but you have not declared SQLCA or SQLSTATE status variables, the Embedded SQL precompiler assumes that you are using the SQLCODE variable. Be sure that SQLCODE is declared. Otherwise, the generated code will not compile.