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

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 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.

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