WHENEVER
Function
This statement specifies the action to be performed when an SQL statement results in an exception condition.
Syntax
label | Host language label. |
procedure | Host language procedure, routine, or function identifier. |
Description
The variables in the SQLCA are updated during program execution and should be verified by the application program. This may be done in two different ways:
Test the contents of the appropriate variable in the SQLCA explicitly. That is usually the SQLCODE field.
Specify the WHENEVER SQL statement.
Note:
If no testing takes place, the default action for errors is to continue with the application program.
An application program may contain any number of WHENEVER statements. The WHENEVER statement may appear anywhere after the declaration of an SQLCA. WHENEVER statements are pre-processed strictly in the order of their physical appearance in the source code, regardless of the execution order or conditional execution that the application program might imply. They will also only refer to the SQLCA which is currently in scope. Should two or more WHENEVER statements contradict each other, then the statement which was physically specified last is relevant for a particular SQL statement. The SQLCA is not updated as a result of a WHENEVER statement.
The condition is determined to be true according to the value of the variable SQLCODE and may be one of the following:
NOT FOUND if the value is +100, indicating that no rows were found.
SQLERROR if the value is negative, indicating an error.
SQLWARNING if the value is positive other than +100, indicating a warning.
The action taken, should the condition be true, may be one of the following:
CONTINUE ignores the exception condition and continues with the next executable statement.
GOTO label continues the application program's logic with the statement identified by the label. The label must conform to the rules of the host language. The label may be prefixed with or without a `:' . GOTO may also be specified as GO TO.
CALL procedure continues the application program's logic with the procedure identified by procedure. The procedure name must conform to the rules of host language. The procedure may not specify any host language parameters.
Generally, a single WHENEVER statement will be valid for all SQL statements in the program. If an error occurs, control can be passed to an error handling routine. If SQL statements are to be executed from within this error handling routine, they too are subject to the conditions of the relevant WHENEVER statement. This means that if an error occurs during execution of the called error handling routine, an attempt will be made to call this same routine again, because the initial WHENEVER statement is still valid. This situation can be avoided by having a second WHENEVER statement in the error handling routine which specifies the option CONTINUE. It is recommended to test the SQLCA explicitly within the error routine.
Limitations
For the ANSI 74 standard (Embedded SQL setting COBOL II = off), every SQL statement is treated as if the optional period was coded. That means that the generated code will always be terminated with a period. It is not possible to code more than one SQL statement in an IF statement.
ANSI Specifics
The SQLWARNING condition and the CALL option are not part of the Standard.
CONNX Embedded SQL Specifics
This is an Adabas SQL Gateway Embedded SQL extension.
Example
The following example continues normal execution of a program if, an SQL query returns no rows.
WHENEVER NOT FOUND CONTINUE;
The following example continues program execution at another point (where that point is specified by a label) whenever an SQL statement produces a warning.
WHENEVER SQLWARNING GOTO label_name;
The following example diverts a program flow to a procedure whenever an SQL statement produces an error.
WHENEVER SQLERROR CALL procedure_name;
