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}