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;