SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Exception Handling and Error Conditions
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
13-10
Enabling and Disabling the WHENEVER Declaration
•
A WHENEVER declaration remains in effect until another WHENEVER declaration
for the same condition appears. If you want to execute a different routine when an
error occurs, specify a new WHENEVER declaration with a different CALL routine.
For example, in a C program, to insert a new row when a row is not found, specify
a new WHENEVER declaration:
EXEC SQL WHENEVER NOT FOUND CALL insert_row;
The new WHENEVER declaration remains in effect until it is disabled or changed.
•
If a WHENEVER declaration is coded in a function, the declaration remains in
effect outside of the function even if the scope of the function is no longer valid.
Therefore, if you do not want the declaration to remain in effect, disable it at the
end of the function described next in Enabling and Disabling the WHENEVER
Declaration.
•
A WHENEVER declaration does not affect SQL statements if they appear in the
program before WHENEVER.
•
If you are debugging a program and you use a WHENEVER declaration to call an
error handling procedure, you might need to save the SQLSTATE value in a local
variable within the error handling procedure. Each subsequent SQL statement
resets SQLSTATE, and you might lose a value you need for debugging.
Enabling and Disabling the WHENEVER Declaration
If you use WHENEVER SQLERROR GOTO some_label; to avoid infinite loops, enable
the WHENEVER declaration at the beginning of the program and disable WHENEVER
for the part of the program that handles the condition named in the declaration.
Example
This example enables and disables the WHENEVER directive:
EXEC SQL WHENEVER SQLERROR GOTO end_prog; /* enables action */
...
end_prog:
EXEC SQL WHENEVER SQLERROR CONTINUE; /* disables action */
...
Saving and Restoring SQLSTATE or SQLCODE
If you use WHENEVER SQLERROR CALL sql_error and the sql_error function
contains SQL statements, you can save and restore the value of SQLSTATE or
SQLCODE before returning from the sql_error function.
Example
This example uses the first FETCH statement returning an SQLCODE value that is not
equal to 0:
EXEC SQL WHENEVER SQLERROR CALL sql_error;
...
C
C