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]}; 

Parameters

Examples

Example 1

exec sql whenever sqlerror call err_handler(); 

exec sql whenever sqlwarning call warn_handler(); 

 long SQLCODE; 

 exec sql begin declare section;

     CS_CHAR        lastname[40];
     CS_CHAR        firstname[20];
     CS_CHAR        phone[12];
exec sql end declare section;

 exec sql declare au_list cursor for

     select au_lname, au_fname, phone

     from authors 

     order by au_lname;

exec sql open au_list;

 exec sql whenever not found go to list_done;

 while (TRUE){
        exec sql fetch au_list 

             into :lastname, :firstname, :phone;

         printf(“Lastname is: %s\n”, lastname,        

“Firstname is: %s\n”, firstname, 

         “Phone number is: %s\n”, phone; 

 }

 list_done:

 exec sql close au_list;

 exec sql disconnect current;

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.