 |
» |
|
|
|
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: 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: Beyond the statement level
Setting the Atomicity to the Row LevelSometimes 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 LevelSometimes 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.
|
