WHENEVER
Valid in: ESQL
The WHENEVER statement enables your application to handle error and exception conditions arising from embedded OpenSQL database statements. The WHENEVER statement stipulates that a specified action be performed when a specified condition occurs. The WHENEVER statement detects conditions by checking SQLCA variables, so an SQLCA must be included in your program before you issue the WHENEVER statement.
After a WHENEVER has been declared, it remains in effect until another WHENEVER is specified for the same condition. Since WHENEVER is a declarative and not an executable statement, its physical location in the program's source code, rather than its sequence in the program's execution, determines its scope.
WHENEVER statements can be repeated for the same condition and can appear anywhere after the INCLUDE SQLCA statement.
The WHENEVER statement has the following format:
exec sql WHENEVER condition action
condition
Defines the condition that triggers the action. The condition can be any of the following:
SQLWARNING
Indicates that the last embedded SQL database statement produced a warning condition. The sqlwarn0 variable of the SQLCA is set to W.
SQLERROR
Indicates that an error occurred as a result of the last embedded OpenSQL database statement. The sqlcode of the SQLCA is set to a negative number.
NOT FOUND
Indicates that a SELECT, FETCH, UPDATE, DELETE, INSERT, COPY, CREATE INDEX, or CREATE AS...SELECT statement affected no rows. The sqlcode variable of the SQLCA is set to 100.
DBEVENT
Indicates that an event has been raised. The sqlcode variable of the SQLCA is set to 710. This condition occurs only for events that the application is registered to receive.
action
Specifies the action. Valid actions include:
CONTINUE
No action is taken when the condition occurs. The program continues with the next executable statement. If a fatal error occurs, an error message is printed and the program aborts.
STOP
Displays an error message and terminates when the condition occurs. If the program is connected to a database when the condition occurs, the program disconnects from the database without committing pending updates. The stop action cannot be specified for the not found condition.
GOTO label
Transfers control to the specified label (same as a host language go to statement). The label (or paragraph name in COBOL) must be specified using the rules of your host language. (The keyword GOTO can also be specified as GO TO).
CALL procedure
Calls the specified procedure (in COBOL, performs the specified paragraph). The procedure must be specified according to the conventions of the host language. No arguments can be passed to the procedure. To direct the program to print any error or warning message and continues with the next statement, specify call SQLPRINT. (SQLPRINT is a procedure provided by Ingres, not a user-written procedure.)
If your program does not include an SQLCA (and therefore no WHENEVER statements), OpenSQL displays all errors. If your program includes an SQLCA, OpenSQL continues execution (and does not display errors) for all conditions for which you do not issue a WHENEVER statement. To override the continue default and direct OpenSQL to display errors and messages, set II_EMBED_SET to sqlprint.
The program's condition is automatically checked after each embedded OpenSQL database statement. If one of the conditions has become true, the action specified for that condition is taken. If the action is goto, then the label must be within the scope of the statements affected by the WHENEVER statement at compile time.
An action specified for a condition affects all subsequent embedded OpenSQL source statements until another WHENEVER is encountered for that condition.
The embedded SQL preprocessor does not generate any code for the WHENEVER statement. Therefore, in a language that does not allow empty control blocks, (for example, COBOL, which does not allow empty IF blocks), the WHENEVER statement should not be the only statement in the block.
Be careful to avoid coding potentially infinite loops with WHENEVER statements. Within a sequence of statements functioning as an error handling block for a particular condition, the first statement should be a whenever continue that turns off the action. For example, consider the following program fragment:
exec sql whenever sqlerror goto error_label;
exec sql create table worktable
(workid integer2, workstats varchar(15));
...
process data;
...
error_label:
exec sql whenever sqlerror continue;
exec sql drop worktable;
exec sql disconnect;
...
If the error handling block did not specify continue for condition sqlerror and the DROP statement caused an error, at runtime the program would infinitely loop between the DROP statement and the label, error_label.
Host language variables cannot be used in a WHENEVER statement. This statement must be terminated according to the rules of your host language.
Last modified date: 11/09/2022