Previous Topic

Next Topic

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 the specified action when the specified condition occurs. An SQLCA must be included in your program; the whenever statement detects conditions by checking SQLCA variables.

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.

Previous Topic

Next Topic

Syntax

The Whenever statement has the following format:

exec sql WHENEVER condition action;

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.


© 2007 Ingres Corporation. All rights reserved.