Handling Runtime Errors and Warnings |
 |
A program is said to be robust if it anticipates
common runtime errors and handles them gracefully. In on-line
applications, robust programs may allow the user to decide what
to do when an error occurs rather than just terminating. This
approach is useful, for example, when a deadlock occurs.
If a deadlock occurs, SQLCode is set to -14024 and SQLEXPLAIN
would retrieve the following message:
Deadlock detected. (DBERR 14024)
|
ALLBASE/SQL rolls back the transaction containing the SQL
command that caused the deadlock. You may want to either give
the user the option of restarting the transaction or
automatically re-execute the transaction a finite number of
times before notifying the user of the deadlock.
Maintaining Data Consistency |
|
Two or more data values, rows, or tables are said to be
consistent if they agree in some way. Changes to such
interdependent values are either committed or rolled back at
the same time in order to retain data consistency. In other
words, the set of operations that form a transaction are
considered as an Atomic Operation; either all or none of the
operations are performed on the database. Status checking in
this case determines whether to commit or roll back work by
transactions operating on tables having these dependencies.
In the case of the sample database, each order is defined by
rows in two tables: one row in the PurchDB.Orders table and
one or more rows in the PurchDB.OrderItems table. A
transaction that deletes orders from the database has to delete
all the rows for a specific order from both tables in order
to maintain data consistency. A program containing such a
transaction should commit work to the database only if it is
able to delete the row from the PurchDB.Orders table and
delete all the rows for the same order from the
PurchDB.OrderItems table:
EXEC SQL BEGIN WORK
EXEC SQL DELETE FROM PurchDB.Orders
1 WHERE OrderNumber = :OrderNumber
.
. If this command succeeds, the program
. submits the following command.
.
EXEC SQL DELETE FROM PurchDB.OrderItems
1 WHERE OrderNumber = :OrderNumber
If this command succeeds, the program
submits a COMMIT WORK command. If this
command does not succeed, the
program submits a ROLLBACK WORK command
to ensure that all rows related to the
order are deleted at the same time.
|
Determining Number of Rows Processed |
|
Knowing such information as the following about rows your
program handles helps determine the action to take in the
program:
No rows qualify for a data retrieval or change operation.
A certain number of rows were retrieved by ALLBASE/SQL and
placed in output host variables.
A certain number of rows were inserted, deleted, or updated.
When no rows qualify for an SQL command that retrieves, inserts,
or changes rows, ALLBASE/SQL sets SQLCode to 100. In the
following example, when a row in the PurchDB.Orders table
does not exist for the order number specified in
OrderNumber, SQLCode contains a 100 after ALLBASE/SQL
processes the UPDATE command:
EXEC SQL UPDATE PurchDB.Orders
1 SET OrderDate = :OrderDate
2 WHERE OrderNumber = :OrderNumber
|
When this situation arises, the program can inform the user that
the update operation could not be performed and prompt for
another order number.
When one or more rows do qualify for a data manipulation or
retrieval operation, ALLBASE/SQL sets SQLErrd(3) to the number
of rows processed. In the following example, the SQLErrd(3)
value determines whether or not subprogram unit DisplayRow
is executed:
.
.
EXEC SQL SELECT PartNumber, PartName
1 INTO :PartNumber
2 :PartName
3 FROM PurchDB.Parts
4 WHERE PartNumber = :PartNumber
.
.
.
IF (SQLErrd(3) .GT. 1) THEN
CALL SQLStatusCheck
ELSE
CALL DisplayRow
ENDIF
.
.
.
SUBROUTINE DisplayRow
.
. This subprogram unit displays one row
. and performs only one SQL command.
.
RETURN
END
|
When more than one row qualifies for a SELECT operation, SQLCode
is set to -10002, and ALLBASE/SQL returns none of the rows.
Your program can warn the user that no rows could be displayed:
SUBROUTINE SQLStatusCheck
.
.
.
IF (SQLCode .EQ. -10002) THEN
WRITE(6,102) 'More than one row qualified for '
WRITE(6,102) 'this operation; none of the rows '
WRITE(6,102) 'can be displayed.'
102 FORMAT(A80)
ELSE
CALL DisplayRow
ENDIF
RETURN
END
|
If one or more rows qualify for a data INSERT, DELETE, or UPDATE
operation, ALLBASE/SQL sets SQLErrd(3) to that number. In the
case of UPDATE and DELETE operations, if SQLErrd(3) contains a
value greater than one, you can warn the user that more than one
row will be updated or deleted and give the user the opportunity
to COMMIT WORK or ROLLBACK WORK.
