Error Handling with the Whenever Statement

Embedded SQL/Ada provides the raise exception action as well as the regular SQL actions. You can use this instead of the less desirable goto action. Note that you should not declare the named exception in an SQL declare section.

exec sql include sqlca;
stmt_error: exception;
...
exec sql whenever sqlerror raise stmt_error;
...
-- Database statements
exception
         when stmt_error =>
               put_line("An error occurred.");
               ...

In Ada, all label, exception, and procedure names must be legal Ada identifiers, beginning with an alphabetic character. If the name is an Embedded SQL reserved word, specify it in quotes. Note that the label targeted by the goto action and the exception targeted by the raise action must be in the scope of all subsequent Embedded SQL statements until you encounter another whenever statement for the same action. This is necessary because the preprocessor may generate the Ada statement:

after an Embedded SQL statement. If the scope of the label or exception is invalid, the Ada compiler generates 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 issuing an Ada return statement. The Ada return statement causes 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.

-- I/O packages
with text_io; use text_io;

with integer_text_io; use integer_text_io;
with short_integer_text_io; use short_integer_text_io;

exec sql include sqlca;

procedure Db_Test is
        exec sql begin declare section;
                eno:        Short_Integer;
                ename:     String(1..20);
                age:        String(1..1);
        exec sql end declare section;

        sql_error: Exception;

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

    --
    -- Clean_Up: error handling procedure (print error
    -- and disconnect).

    procedure Clean_Up is
        exec sql begin declare section;
            errmsg: String(200);
        exec sql end declare section;

    begin -- Clean_Up
        exec sql inquire_sql (:errmsg = errortext);
        put_line( "aborting because of error: " );
        put_line( errmsg );
        exec sql disconnect;

        raise sql_error; -- No return
    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;

    put_line( "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) loop
        -- Loop is broken by NOT found
        exec sql fetch empcsr
            into :eno, :ename, :age;

        --
        -- These "put" statements do not execute after
        -- the previous FETCH returns the NOT FOUND
        -- condition.
        --
        put( eno );
        put( ", " & ename & ", ");
        put( age );
        new_line;
    end loop;

    --
    -- 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;

    --
    -- "Sqlerror" is raised only in Clean_Up, which
    -- has already taken care of the error.
    --
    exception
        when sql_error =>
            null;       -- Just go away quietly
end Db_Test;