HP 3000 Manuals HP 3000 Manuals
The Importance of Status Checking
Status checking is performed primarily for three reasons:
* To gracefully handle runtime error and warning conditions.
* To maintain data consistency.
* To return information about the most recently executed command,
such as how many rows ALLBASE/SQL processed.
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.