Embedded Usage

Host language variables cannot be used in an embedded Whenever statement.

Permissions

All users are permitted to use this statement.

Locking

In general, the Whenever statement has no impact. However, if the specified action is stop, any locks held are dropped because this action terminates execution of the program.

Related Statements

Create Procedure

Examples: Whenever

The following examples describe how to enable your application to handle error and exception conditions arising from embedded SQL database statements.

1. During program development, print all errors and continue with next statement.

   exec sql whenever sqlerror call sqlprint;

2. During database cursor manipulation, close the cursor when no more rows are retrieved.

   exec sql open cursor1;
       exec sql whenever not found goto close_cursor;
   
       loop until whenever not found is true
           exec sql fetch cursor1
               into :var1, :var2;
           print and process the results;
       end loop;
   
       close_cursor:
           exec sql whenever not found continue;
           exec sql close cursor1;

3. Stop program upon detecting an error or warning condition.

   exec sql whenever sqlerror stop;
       exec sql whenever sqlwarning stop;

4. Reset whenever actions to default within an error handling block.

   error_handle:
           exec sql whenever sqlerror continue;
           exec sql whenever sqlwarning continue;
           exec sql whenever not found continue;
       ...
       handle cleanup;
       ...

5. Always confirm that the connect statement succeeded before continuing.

   exec sql whenever sqlerror stop;
       exec sql connect :dbname;
       exec sql whenever sqlerror continue;

6. Ignore all messages originating in a database procedure. This is useful when you want to suppress informational messages when providing a production application.

   exec sql whenever sqlmessage continue;
       ...
       exec sql execute procedure proc1;