Error Handling Using the SQLCA
User-defined error, message and dbevent handlers offer the most flexibility for handling errors, database procedure messages, and database events. For more information, see
Advanced Processing in this chapter.
However, you can do error handling with the SQLCA implicitly by using whenever statements, or explicitly by checking the contents of the SQLCA fields sqlcode, sqlerrd, and sqlwarn0.
Error Handling with the Whenever Statement
The syntax of the whenever statement is:
exec sql whenever condition action;
condition
Specifies the error condition. Valid conditions are: dbevent, sqlwarning, sqlerror, sqlmessage, and not found.
action
Specifies the action to be taken. Valid actions are: continue, stop, goto a label, call an Ada procedure, and raise an Ada exception.
For a detailed description of this statement, see the SQL Reference Guide.
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.
For example:
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:
if (condition) then
goto label; --raise exception
end if;
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;
The Whenever Goto Action in Embedded SQL Blocks
An Embedded SQL block-structured statement is a statement delimited by the begin and end clauses. For example, the select loop and the unloadtable loops are both block-structured statements. You can terminate these statements only by the methods specified for the particular statement in the SQL Reference Guide. For example, the preprocessor terminates the select loop either when all the rows in the database result table have been processed or by an endselect statement, and the preprocessor terminates the unloadtable loop either when all the rows in the forms table field have been processed or by an endloop statement.
Therefore, if you use a whenever statement with the goto action in an SQL block, you must avoid going to a label outside the block. Such a goto causes the block to be terminated without issuing the runtime calls necessary to clean up the information that controls the loop. (For the same reason, you must not issue an Ada return, exit, goto, or raise statement that causes control to leave or enter an SQL block.) The target label of the whenever goto statement should be a label in the block. If however, it is a label for a block of code that cleanly exits the program, the above precaution need not be taken.
The above information does not apply to error handling for database statements issued outside an SQL block, nor to explicit hard-coded error handling. For an example of hard-coded error handling, see
The Table Editor Table Field Application in this chapter.
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;
How to Determine the Number of Affected Rows
The third element of the SQLCA array sqlerrd indicates how many rows were affected by the last row-affecting statement. The following program fragment, which deletes all employees whose employee numbers are greater than a given number, demonstrates how to use sqlerrd:
procedure Delete_Rows( lower_bound: in Integer ) is
exec sql begin declare section;
lower_bound_num: integer := lower_bound;
exec sql end declare section;
begin
exec sql delete from employee
where eno > :lower_bound_num;
-- Print the number of employees deleted.
put( sqlca.sqlerrd(3) );
put_line( " (rows) were deleted.");
end Delete_Rows;