Explicit Error Handling

The program can also handle errors by inspecting values in the SQLCA record 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 Pascal statements.

program Db_Test( input, output );

label
Exit_Label;
exec sql begin declare section;
const
not_found = 100;

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( str : varying[ub] of Char );
    exec sql begin declare section;
    var
        errmsg: varying[200] of Char;
        err_stmt: varying[40] of Char;
    exec sql end declare section;
    begin {Clean_Up}
        err_stmt := str;
        exec sql inquire_sql (:errmsg = ERRORTEXT);
        writeln('Aborting because of error in ', err_stmt, ': ');
        writeln( errmsg );
        exec sql disconnect;

        goto Exit_Label;
    end; {Clean_Up}

begin                 {Db_Test}
        {Exit if the database cannot be opened.}
        exec sql connect personnel;
        if (sqlca.sqlcode < 0) then
        begin
            writeln( 'Cannot access database.' );
            goto Exit_Label;
        end;

        {Errors if cannot open cursor.}
        exec sql open empcsr;
        if (sqlca.sqlcode < 0) then
                Clean_Up( 'OPEN "empcsr"' ); {No return}
        writeln( '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) do    {
                            | Loop is broken by NOT FOUND
                            }
            begin
                exec sql fetch empcsr
                    into :eno, :ename, :age;

            {Do not print the last values twice.}
            if (sqlca.sqlcode < 0) then
                    Clean_Up( 'FETCH "empcsr"' )
            else if (sqlca.sqlcode <> NOT_FOUND) then
                writeln( eno, ', ', ename, ', ', age );
        end; {while}

{
| From this point in the file onwards, ignore all errors.
}
exec sql close empcsr;
exec sql disconnect;

Exit_Label:;
end; {Db_Test}