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

sqlerror

Specifies an action to take when an error is detected, such as a syntax error returned to the Embedded SQL program from SQL Server.

not found

Specifies an action to take when a fetch or select into statement retrieves no data or when a searched update or delete statement affects no rows.

sqlwarning

Specifies an action to take when a warning is received; for example, when a character string is truncated.

continue

Take no action when the condition occurs.

go to | goto

Transfer control to the program statement at the specified label.

label

A host language statement label, such as a C label.

stop

Terminate the Embedded SQL program when the condition occurs.

call

Transfer control to a callable routine in the program, such as a user-defined function or subroutine.

routine_name

A host language routine that can be called. The routine must be able to be called from the source file that contains the whenever statement. You may need to declare the routine as external to compile the Embedded SQL program.

args

One or more arguments to be passed to the callable routine, using the parameter-passing conventions of the host language. The arguments can be any list of host variables, literals, or expressions that the host language allows. A space character should separate each argument from the next.

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.

SQL descriptor codes

The following table pertains to the SQL descriptor used for dynamic SQL statements. Sybase’s use of dynamic SQL values conforms to the ANSI/ISO 185-92 SQL-92 standards. For more information, see the appropriate ANSI/ISO documentation.

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF