HP 3000 Manuals HP 3000 Manuals
Error Conditions in ALLBASE/SQL
When you issue an SQL statement, error messages are returned if the
statement cannot be carried out as intended. In an interactive session
with ISQL, the messages are displayed on your terminal. In application
programs, you access the message buffer directly by using the SQLEXPLAIN
statement. The effect of an error on your session depends on three
factors:
* Severity of the error
* Atomicity level set within the transaction
* Constraint checking mode set within the transaction
Severity of Errors
In general, errors result in partially or completely undoing the effects
of an SQL statement. If the error is very severe, the transaction is
rolled back. When a transaction is rolled back, ALLBASE/SQL displays a
message like the following along with other messages:
Your current transaction was rolled back by DBCore. (DBERR 14029)
If an error is less severe, the statement is undone, but the transaction
is allowed to continue.
Atomicity of Error Checking
By default, error checking is done at the statement level. In other
words, the entire statement either succeeds or fails. This means that
for set operations, the statement succeeds for all members of the set or
fails for all members of the set. For example, if there is an error on
the fifteenth row of a twenty-row BULK INSERT statement, the entire
statement has no effect, and no rows are inserted. Or if an UPDATE
statement that affects twenty rows creates a uniqueness violation for one
row, the statement will fail for all rows. This approach guarantees data
integrity for the entire statement. Under special circumstances, you can
choose a different atomicity level for error checking:
* Row level
* Beyond the statement level
Setting the Atomicity to the Row Level.
Sometimes statement level atomicity has drawbacks which you can correct.
For example, data manipulation statements involving large amounts of data
require considerable overhead for logging when issued at statement level,
and this can impair performance. For better performance, you can set
atomicity to row level. With row level atomicity, if an error occurs on
one row, earlier rows are not undone. For example, for an error on the
fifteenth row of a twenty-row BULK INSERT, statement execution stops at
the fifteenth row, but the first fourteen rows will be processed unless
you use the ROLLBACK WORK statement. To use row level error checking,
issue the following statement:
SET DML ATOMICITY AT ROW LEVEL
Only DML statements can be checked for errors at the row level of
atomicity. Refer to the SET DML ATOMICITY statement in the "SQL
Statements" chapter for complete details.
Deferring Error Checking beyond the Statement Level.
Sometimes statement level atomicity is too narrow for your needs. For
operations involving more than one table, it may be useful to defer error
checking until all tables are updated. For example, if you are loading
two tables that have a referential relationship that is circular--that
is, each table references a primary key element in the other table--then
you must defer constraint error checking until both tables are loaded;
otherwise any attempt to load a row would result in a constraint error.
To defer referential constraint error checking beyond the statement
level, issue the following statement:
SET REFERENTIAL CONSTRAINTS DEFERRED
After the loading of both tables is complete, issue the following
statement:
SET REFERENTIAL CONSTRAINTS IMMEDIATE
This turns on constraint error checking and reports any constraint errors
that now exist between the two tables. Only integrity constraint error
checking can be deferred beyond the statement level. Refer to the SET
CONSTRAINTS statement in the "SQL Statements" chapter for complete
details.
Additional Information about Errors
Refer to the "Introduction" to the ALLBASE/SQL Message Manual for a
general description of error handling. For the coding of error handling
routines in application programs, refer to the chapter "Using Data
Integrity Features" in the ALLBASE/SQL Advanced Application Programming
Guide and the "Runtime Status Checking and the SQLCA" chapter in the
application programming guide for the language of your choice. For error
handling in procedures, refer to the chapter "Constraints, Procedures,
and Rules." For row level error checking, see the SET DML ATOMICITY
statement, and for deferred constraint checking, see the SET CONSTRAINTS
statement, both in the "SQL Statements" chapter.