WHENEVER
Valid in: ESQL
The WHENEVER statement enables your application to handle error and exception conditions arising from embedded SQL database statements. The WHENEVER statement directs the DBMS Server to perform a specified action 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. The WHENEVER statement has lexical (as opposed to logical) scope. For details, see the chapter “Working with Transactions and Handling Errors.”
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 SQL database statement. The sqlcode of the SQLCA is set to a negative number.
SQLMESSAGE
Indicates that a message statement in a database procedure has executed. The sqlcode variable of the SQLCA is set to 700. If the database procedure is invoked by a rule, MESSAGE statements issued by the database procedure do not set sqlcode, and the sqlmessage condition does not occur.
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 when the condition occurs. Valid actions include:
CONTINUE
No action is taken. 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. If the program is connected to a database when the condition occurs, the program disconnects from the database without committing pending updates. In response to an error or a message statement inside a database procedure, STOP terminates the database procedure. There is no way to determine which procedure statements have been executed when the database procedure is terminated in this way. 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). When specified as the response to an error or a message statement inside a database procedure, GOTO terminates the procedure when the action is performed. You cannot determine which database procedure statements have been executed when the procedure has been terminated in this way.
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 the CALL action is taken in response to an error or a message statement inside a database procedure, another Ingres tool cannot be called. The called procedure cannot issue any database statements, because a database procedure continues to execute when a call action is specified. The called procedure can issue any forms statements that do not access the database. Do not issue form statements that access the database; for example, do not enter a display loop containing a SELECT statement, or issue the FORMINIT statement.
When the message statement is issued from a database procedure that executes as a result of a rule firing, the DBMS Server displays the message text and continues program execution, even if a WHENEVER SQLMESSAGE statement is in effect. All messages are displayed and are not returned through the SQLCA.
If your program does not include an SQLCA (and therefore no WHENEVER statements), the DBMS Server displays all errors. If your program includes an SQLCA, the DBMS Server 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 the DBMS Server to display errors and messages, set II_EMBED_SET to SQLPRINT. For information about II_EMBED_SET, see the System Administrator Guide.
The program's condition is automatically checked after each embedded SQL database statement or each database procedure statement. If one of the conditions has become true, the action specified for that condition is taken. If the action is GOTO, 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 SQL 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 does not allow empty IF blocks), the WHENEVER statement must not be the only statement in the block.
To avoid infinite loops, the first statement in an error handling routine must be a WHENEVER...CONTINUE that turns off error handling for the condition that caused the error. For example:
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 loops infinitely between the DROP statement and the label, error_label.