SQL Programming Manual for Pascal

Error and Status Processing
HP NonStop SQL Programming Manual for Pascal—528614-001
6-6
Using the WHENEVER Directive
Avoiding Infinite Loops
You might need to disable the WHENEVER directive within an error handling
procedure to avoid an infinite loop if the error handling code generates errors or
warnings. An infinite loop can occur when the:
•
SQLERROR condition executes a statement that generates an error
•
SQLWARNING condition executes a statement that generates a warning
•
NOT FOUND condition executes a statement that generates a NOT FOUND
condition
To avoid these situations, disable the appropriate WHENEVER directive for the part of
your program that handles each condition.
The following example enables and disables the WHENEVER directive.
{ Reset SQLERROR checking while in error handler }
EXEC SQL WHENEVER SQLERROR ;
PROCEDURE ERROR_HANDLER;
BEGIN
{ error handling code }
END ;
{ Re-enable SQLERROR checking: }
EXEC SQL WHENEVER SQLERROR CALL :ERROR_HANDLER ;
PROCEDURE FIRST;
BEGIN
EXEC SQL SELECT ...;
EXEC SQL SELECT ...;
EXEC SQL SELECT ...;
END ;
PROCEDURE SECOND;
BEGIN
EXEC SQL SELECT ...;
EXEC SQL SELECT ...;
EXEC SQL SELECT ...;
END ;
BEGIN { Main Program }
FIRST;
SECOND;
EXEC SQL INSERT...;
END.
WHENEVER Directives and Aggregates
All aggregate functions except COUNT return a null value when operating on an empty
set. If a host variable receives the null value as the result of an aggregate function, you
must specify an indicator variable and test the result of the indicator variable.
Otherwise, NonStop SQL returns a “no indicator variable provided” condition instead of