5. Embedded SQL for Ada : The SQL Communications Area : Error Handling Using the SQLCA : Explicit Error Handling
 
Share this page                  
Explicit Error Handling
The program can also handle errors by inspecting values in the SQLCA structure at various points. For further details, see the SQL Reference Guide.
The following example is functionally the same as the previous example, except that the error handling is hard-coded in Ada statements.
-- 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;
    not_found: constant := 100;

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

    --
    -- Clean_Up: Error handling procedure (print error
    -- and disconnect).
    --
    procedure Clean_Up( str: in String) is
 
        exec sql begin declare section;
            err_stmt: String(40) := str;
            errmsg:   String(200);
        exec sql end declare section;

    begin         -- Clean_Up
        exec sql inquire_sql (:errmsg = ERRORTEXT);
        put_line
             ( "Aborting because of error in " &
                err_stmt & ": ");
        put_line( errmsg );
        exec sql disconnect;

        raise sql_error; -- No return
    end Clean_Up;

begin -- Db_Test
    -- Exit if the database cannot be opened.
    exec sql connect personnel;
    if (sqlca.sqlcode < 0) then
        put_line( "Cannot access database.");
        raise sql_error;
    end if;

    -- Errors if cannot open cursor.
    exec sql open empcsr;
    if (sqlca.sqlcode < 0) then
        Clean_Up( "OPEN ""empcsr""" );
    end if;

    put_line("Some values from the ""employee"" table.");

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

        -- Do not print the last values twice
        if (sqlca.sqlcode < 0) then
            Clean_Up( "FETCH ""empcsr""" );
        elsif (sqlca.sqlcode <= NOT_FOUND) then
            put( eno );
            put( ", " & ename & ", ");
            put( age );
            new_line;
        end if;
    end loop;

    -- From this point in the file onwards, ignore all
    -- errors.

    exec sql close empcsr;
    exec sql disconnect;

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