The WHENEVER statement

You can use the WHENEVER statement to trap for exceptions that occur during the execution of SQL statements.

The WHENEVER statement provides the following information:

What condition to check for:
- SQLERROR checks whether an SQL statement has failed. The application performs the specified action when the database server sets SQLCODE (sqlca.sqlcode) to a negative value and the class code of SQLSTATE to a value greater than "02".
- NOT FOUND checks whether specified data has not been found. The application performs the specified action when the database server sets SQLCODE (sqlca.sqlcode) to SQLNOTFOUND and the class code of SQLSTATE to "02".
- SQLWARNING checks whether the SQL statement has generated a warning. The application performs the specified action when the database server sets sqlca.sqlwarn.sqlwarn0 (and some other field of sqlca.sqlwarn) to W and sets the class code of SQLSTATE to "01".
In a Windows™ environment, do not use the WHENEVER ERROR STOP construction in the program that you want to compile as a DLL.
What action to take when the specified condition occurs:
- CONTINUE ignores the exception and continues execution at the next statement after the SQL statement.
- GO TO label transfers execution to the section of code that the specified label introduces.
- STOP stops program execution immediately.
- CALL function name transfers execution to the specified function name.

If no WHENEVER statement exists for a given condition, the preprocessor uses CONTINUE as the default action. To execute the sqlstate_exception() function (shown in Example of an exception-handling function that uses SQLSTATE) every time an error occurs, you can use the GOTO action of the WHENEVER SQLERROR statement. If you specify the SQLERROR condition of WHENEVER, you obtain the same behavior as if you check the SQLCODE or SQLSTATE variable for an error after each SQL statement.

The WHENEVER statement for the GOTO action can take the following two forms:

The ANSI-standard form uses the keywords GOTO (one word) and introduces the label name with a colon (:):
```
EXEC SQL whenever goto :error_label;
```
The HCL OneDB™ extension uses the keywords GO TO (two words) and specifies just the label name:
```
EXEC SQL whenever go to error_label;
```

With the GOTO action, your program automatically transfers control to the error_label label when the SQL statement generates an exception. When you use the GOTO label action of the WHENEVER statement, your code must contain the label and appropriate logic to handle the error condition. In the following example, the logic at label is simply a call to the sqlstate_exception() function:

error_label:
   sqlstate_exception (msg);

You must define this error_label label in each program block that contains SQL statements. If your program contains more than one function, you might need to include the error_label label and code in each function. Otherwise, the preprocessor generates an error when it reaches the function that does not contain the error_label. It tries to insert the code that the WHENEVER...GOTO statement has requested, but the function has not defined the error_label label.

To remove the preprocessor error, you can put the labeled statement with the same label name in each function, you can issue another action for the WHENEVER statement to reset the error condition, or you can replace the GOTO action with the CALL action to call a separate function.

You can also use the CALL keyword in the WHENEVER statement to call the sqlstate_exception() function when errors occur. (The CALL option is the HCL OneDB extension to the ANSI standard.)

If you want to call the sqlstate_exception() function every time an SQL error occurs in the program, take the following steps:

Modify the sqlstate_exception() function so that it does not need any arguments. Functions that the CALL action specifies cannot take arguments. To pass information, use global variables instead.
Put the following WHENEVER statement in the early part of your program, before any SQL statements:
```
EXEC SQL whenever sqlerror call sqlstate_exception;
```

Tip: In the preceding code fragment, you do not include the parentheses after the sqlstate_exception() function.

Make sure, however, that all functions that the WHENEVER...CALL affects can find a declaration of the sqlstate_exception() function.