SQL Language Guide : 8. SQL Statements : WHENEVER : WHENEVER Examples
 
Share this page                  
WHENEVER Examples
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;