WHENEVER (Declare embedded exception)

Function

WHENEVER declares processing of a UAP, based on the return code (SQLCODE) set in the SQL Communications Area by HiRDB after an SQL has executed.

Format

WHENEVER
   {SQLERROR | SQLWARNING | NOT FOUND}
   {CONTINUE {GO TO | GOTO} [:]host-identifier
    |PERFORM [:]host-identifier}
    |DO {break | continue | 'command-statement' }}

Operands

SQLERROR
Specifies the processing to be performed when the SQL does not execute normally due to human error or an HiRDB problem (i.e., when negative values are returned to SQLCODE in the SQL Communications Area and to the SQLCODE variable).
SQLWARNING
Specifies the processing to be performed when the SQL executed normally but a condition requiring a warning to the user was detected (when W is returned to the SQLWARN0 area of the SQL Communications Area or when a positive value other than 100 is returned to the SQLCODE area and the SQLCODE variable of the SQL Communications Area).

Specifies the processing to be performed when there are no more rows to be retrieved during table retrieval (i.e., when the value 100 is returned to SQLCODE in the SQL Communications Area, the value 100 is returned to the SQLCODE variable, and the value 02000 is returned to the SQLSTATE variable).

Specifies the branching address when execution of the UAP is to branch; following are the host identifiers:

Specifies a procedure that is to be executed; following are the applicable procedure specifications:

Object methods cannot be specified.

This statement either causes branching of the execution of the UAP or executes an arbitrary command statement. The continue command and 'command-statement' can be used in the C and C++ languages.

DO break
Executes the break statement.
DO continue
Executes the continue statement.
DO 'command-statement'
Executes any command statement of the host language, coded as a character string (for example, a function call specifying an argument).

Common rules

  1. If there is no WHENEVER specification, CONTINUE is assumed for all return codes.
  2. WHENEVER can be specified multiple times in a UAP.
  3. SQLs other than the ROLLBACK statement cannot be executed in the SQLERROR status.
  4. After a procedure has executed, control returns to the instruction following the SQL at which the exception-handling event occurred.
  5. The scope of the processing specified in an embedded exception declaration depends on its location in the source program[Figure]i.e., a processing action specification in an embedded exception declaration is effective for all SQL execution results that occur in the source program until another embedded exception declaration specifying the processing of the same singular condition is encountered.
    Figure 6-1 shows the scope of processing specified in an embedded exception declaration.

    Figure 6-1 Scope of processing specified in WHENEVER declarations

    [Figure]

  6. In the event of overlapping scopes for SQLERROR, SQLWARNING, and NOT FOUND, the SQLs are processed according to the following priorities:
    SQLERROR[Figure] NOT FOUND[Figure] SQLWARNING

Notes

  1. If the need for the action declared in a WHENEVER statement ceases during processing of the SQL, a WHENEVER statement with the CONTINUE option (for continuing the processing) must be inserted at a program location where the statement will be a moot point in order provide for the same exception-handling event (SQLERROR, etc.).
    Figure 6-2 shows an example of coding the WHENEVER statement.

    Figure 6-2 Example of coding a WHENEVER statement (1)

    [Figure]

    Explanation
    If there are no SQLs that produce the NOT FOUND condition in the SQL statements following (3), any processing that deals with the NOT FOUND condition is not required in the SQL statements following (3). Therefore, in WHENEVER statements following (3), the processing is CONTINUE.
  2. If the ROLLBACK statement is executed at a branching address because of an execution error in the SQL, care must be taken that an endless loop is not created by a WHENEVER statement with the SQLERROR specification.
    Figure 6-3 shows an example of coding the WHENEVER statement.

    Figure 6-3 Example of coding a WHENEVER statement (2)

    [Figure]

    Explanation
    If the WHENEVER statement in (3) is not provided, the WHENEVER statement in (1) is also applicable to the ROLLBACK statement in (4). In this case, an endless loop results if an error occurs during rollback processing. To provide for this contingency, the WHENEVER statement of (3) (in which CONTINUE is specified) is specified before the ROLLBACK statement.
  3. The scope of an embedded exception declaration extends to multiple functions in the UAP.
    The destination for the GOTO statement specified in the WHENEVER statement should be specified in the same function as the SQL statement. If the destination is specified out of the function, a compilation error can result.
    A separate WHENEVER statement must be specified, as necessary, for each function.
    Figures 6-4 and 6-5 show examples of coding the WHENEVER statement.

    Figure 6-4 Example of coding a WHENEVER statement (3)

    [Figure]

    Explanation
    If an SQL error (SQLERROR) occurs at (2), control branches to (3) because destination (3) specified in (1) is in the same function as (2). On the other hand, if an SQL error occurs at (4), a compile-time error results because destination (3) specified in (1) is not in the same function as (4).

    Figure 6-5 Example of coding a WHENEVER statement (4)

    [Figure]
    Explanation
    Branching is successful because destination (3) that is taken if an SQL error (SQLERROR) occurs at (2) and destination (6) that is taken if an SQL error occurs at (5) are in the same function in their respective SQL statements.
  4. The WHENEVER statement should not be declared before FREE CONNECTION HANDLE.

Examples