DB2 Version 10.1 for Linux, UNIX, and Windows

WHENEVER statement

The WHENEVER statement specifies the action to be taken when a specified exception condition occurs.

Invocation

This statement can only be embedded in an application program. It is not an executable statement. The statement is not supported in REXX.

Authorization

None required.

Syntax


>>-WHENEVER--+-NOT FOUND--+--+-CONTINUE---------------------+--><
             +-SQLERROR---+  +-+-GOTO--+--+---+--host-label-+   
             '-SQLWARNING-'  | '-GO TO-'  '-:-'             |   
                             '-DO--+-function-name()-+------'   
                                   +-BREAK-----------+          
                                   '-CONTINUE--------'

Description

The NOT FOUND, SQLERROR, or SQLWARNING clause is used to identify the type of exception condition.

NOT FOUND: Identifies any condition that results in an SQLCODE of +100 or an SQLSTATE of '02000'.
SQLERROR: Identifies any condition that results in a negative SQLCODE.
SQLWARNING: Identifies any condition that results in a warning condition (SQLWARN0 is 'W'), or that results in a positive SQL return code other than +100.

The CONTINUE or GO TO clause is used to specify what is to happen when the identified type of exception condition exists.

CONTINUE

Causes the next sequential instruction of the source program to be executed.

GOTO or GO TO host-label

Causes control to pass to the statement identified by host-label. For host-label, substitute a single token, optionally preceded by a colon. The form of the token depends on the host language.

DO

Causes additional action in the form of a function call, break statement, or continue statement to take place.

function-name(): Specifies the C function that is to be called. The function must have a void return value and cannot accept any arguments. The function name must end with set of parentheses "(" and ")". The name of the function is limited to 255 bytes.
The function name resolution takes place during the compilation of the C and C++ embedded SQL application. The DB2® precompiler does not resolve the function name.

BREAK: Specifies the C break statement. The C break statement exits thedo, for, switch, or while statement block.

CONTINUE: Specifies the C continue statement. The C continue statement passes control to the next iteration of the do, for, switch, or while statement block.

Notes

There are three types of WHENEVER statements:

WHENEVER NOT FOUND
WHENEVER SQLERROR
WHENEVER SQLWARNING

Every executable SQL statement in a program is within the scope of one implicit or explicit WHENEVER statement of each type. The scope of a WHENEVER statement is related to the listing sequence of the statements in the program, not their execution sequence.

An SQL statement is within the scope of the last WHENEVER statement of each type that is specified before that SQL statement in the source program. If a WHENEVER statement of some type is not specified before an SQL statement, that SQL statement is within the scope of an implicit WHENEVER statement of that type in which CONTINUE is specified.

If the WHENEVER statement is not used, the default action is to continue processing if an error, warning, or exception condition occurs during execution.

The WHENEVER statement must be used before the SQL statements that you want to affect. Otherwise, the precompiler does not know that additional error-handling code is required for the executable SQL statements. You can have any combination of the three basic forms active at any time. The order in which you declare the three forms is not significant.

To avoid an infinite looping situation, ensure that you undo the WHENEVER handling before any SQL statements are executed inside the handler. You can undo the WHENEVER handling by using the WHENEVER SQLERROR CONTINUE statement.

The WHENEVER statement support for use of the DO function-name(), DO BREAK, or DO CONTINUE syntax is available in Version 9.7 Fix Pack 6 and later.

Example

In the following C example, if an error is produced, go to HANDLERR. If a warning code is produced, continue with the normal flow of the program. If no data is returned, go to ENDDATA.

   EXEC SQL WHENEVER SQLERROR GOTO HANDLERR;
   EXEC SQL WHENEVER SQLWARNING CONTINUE;
   EXEC SQL WHENEVER NOT FOUND GO TO ENDDATA;

The C example for use of the DO function-name(), DO BREAK, or DO CONTINUE syntax are:

/* DO function_name */
EXEC SQL WHENEVER SQLERROR DO perform_error_action(); 
EXEC SQL WHENEVER SQLWARNING DO perform_warning_action(); 
EXEC SQL WHENEVER NOT FOUND DO perform_notfound_action();  

/* DO BREAK */ 
EXEC SQL WHENEVER SQLERROR DO BREAK; 
EXEC SQL WHENEVER SQLWARNING DO BREAK; 
EXEC SQL WHENEVER NOT FOUND DO BREAK;

/* DO CONTINUE */
EXEC SQL WHENEVER SQLERROR DO CONTINUE;
EXEC SQL WHENEVER SQLWARNING DO CONTINUE;
EXEC SQL WHENEVER NOT FOUND DO CONTINUE;