 |
» |
|
|
|
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 Chapter 12 “SQL Statements S - Z” 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. For complete
details, refer to the SET CONSTRAINTS statement Chapter 12 “SQL Statements S - Z” 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 Chapter 4 “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 Chapter 12 “SQL Statements S - Z” |
