Error Handling with the Whenever Statement

In Embedded SQL/Pascal, all labels and procedure names must be legal Pascal label identifiers, beginning with a digit, an alphabetic character, or an underscore. If the label is an Embedded SQL reserved word, it should be specified in quotes. Note that the label targeted by the goto action must be in the scope of all subsequent Embedded SQL statements until another whenever statement is encountered for the same action. This is necessary because the preprocessor can generate the Pascal statement:

after an Embedded SQL statement. If the scope of the label is invalid, the Pascal compiler will generate an error.

The same scope rules apply to procedure names used with the call action. Note that the reserved procedure sqlprint, which prints errors or database procedure messages and then continues, is always in the scope of the program. When a whenever statement specifies a call as the action, the target procedure is called, and after its execution, control returns to the statement following the statement that caused the procedure to be called. Consequently, after handling the whenever condition in the called procedure, you may want to take some action, instead of merely returning from the Pascal procedure. Returning from the Pascal procedure will cause the program to continue execution with the statement following the Embedded SQL statement that generated the error.

The following example demonstrates use of the whenever statements in the context of printing some values from the Employee table. The comments do not relate to the program but to the use of error handling.

label
        Close_Csr,
        Exit_Label;

exec sql begin declare section;
var
     eno:         [word] -32768 .. 32767;
     ename:         varying[20] of Char;
     age:         [byte] -128 .. 127;
exec sql end declare section;
     exec sql include sqlca;

     exec sql declare empcsr cursor for
        select eno, ename, age
        from employee;

    {
    | Clean_Up: Error handling procedure (print error and disconnect).
    }
    procedure Clean_Up;
    exec sql begin declare section;
    var
             errmsg: varying[200] of Char;
    exec sql end declare section;
    begin         {Clean_Up}
         exec sql whenever sqlerror stop;
         inquire_sql (:errmsg = errortext) ;
         writeln( 'Aborting because of error: ' );
         writeln( errmsg );
         exec sql disconnect;
         goto Exit_Label;
    end; {Clean_Up}

begin             {Db_Test}
    {
    | An error when opening the personnel database
    | will cause the error to be printed and the
    | program to abort.
    }
    exec sql whenever sqlerror stop;
    exec sql connect personnel;

    {
    |    Errors from here on will cause the program to clean up.
    }
    exec sql whenever sqlerror call Clean_Up;

    exec sql open empcsr;

    writeln( 'Some values from the "employee" table.' );

    {When no more rows are fetched, close the cursor.}
    exec sql whenever not found goto Close_Csr;

    {
    | The last executable Embedded SQL statement
    | was an OPEN,so we know that the value of
    | "sqlcode" cannot be SQLERROR or NOT FOUND.
    }
    while (sqlca.sqlcode = 0) do
    {Loop is broken by NOT FOUND}
    begin
        exec sql fetch empcsr
             into :eno, :ename, :age;

        {
        | This writeln statement does not execute
        |    after the previous FETCH returns the
        |    NOT FOUND condition.
        }
        writeln( eno, ', ', ename, ', ', age );
    end; {while}
    {
    | From this point in the file onwards, ignore
    |    all errors. Also turn off the NOT FOUND
    | condition, for consistency.
    }
    exec sql whenever sqlerror continue;
    exec sql whenever not found continue;
Close_Csr:
    exec sql close empcsr;
    exec sql disconnect;

Exit_Label:;
end; {Db_Test}