HP 3000 Manuals HP 3000 Manuals
Chapter 5 Runtime Status Checking and the SQLCA
When an SQL command is executed, ALLBASE/SQL returns information
describing how the command executed. This information signals one or
more of the following conditions:
* The command was successfully executed.
* The command could not be executed because an error condition
occurred, but the current transaction may continue.
* No rows qualified for a data manipulation operation.
* A specific number of rows were placed into output host variables.
* A specific number of rows qualified for an insert, update, or
delete operation.
* The command was executed, but a character string was truncated.
* The command was executed, but a null value was eliminated from an
aggregate function.
* The command was executed, but a warning condition resulted.
* The command could not be executed because an error condition
necessitated rolling back the current transaction.
Based on this runtime status information, a program can commit work,
rollback work, continue, terminate, display a message, or perform some
other appropriate activity.
ALLBASE/SQL returns status information into a common data structure block
known as the SQLCA, which stands for SQL Communication Area. The SQLCA
has four data items which your programs can take advantage of:
* SQLCode, which is set to 0 if a command executes successfully, to
a negative number identifying a specific error condition, or to
100 if no rows qualify for an SQL data manipulation operation.
* SQLErrd(3), which is set to the number of rows that ALLBASE/SQL
put into output host variables for data retrieval operations or
the number of rows that ALLBASE/SQL processed for data change
operations. It is set to 0 when a single-row data change
operation causes an error condition or when SQLCode = 100.
* SQLWarn(0), which is set to W when a warning condition occurs or
when SQLWarn(6) is set to W.
* SQLWarn(1), which is set to W when a character string is truncated
while being stored in a host variable.
* SQLWarn(2), which is set to W when a null value is eliminated from
the argument set of an aggregate function.
* SQLWarn(6), which is set to W when an error occurs that caused
ALLBASE/SQL to abort the current transaction.
These data items can be used in several ways to perform runtime status
checking:
* You can use the WHENEVER command to perform implicit status
checking. When you use this command, ALLBASE/SQL checks the
SQLCode and SQLWarn(0) values for you, then takes an action based
on information you provide in the WHENEVER command.
* You can write FORTRAN code that explicitly examines one or more of
the SQLCA data items, then proceeds on the basis of their values.
This kind of status checking is called explicit status checking.
* You can use a combination of both implicit and explicit status
checking.
In conjunction with status checking of any kind, you can use the
SQLEXPLAIN command. This command retrieves a message from the
ALLBASE/SQL message catalog that describes an error or warning. When
several errors or warnings occur, you can use SQLEXPLAIN to retrieve
messages for all of them. Refer to the ALLBASE/SQL Message Manual for an
explanation of all error and warning messages.
This chapter examines the need for runtime status checking. It describes
the SQLCA COMMON block and the conditions under which its data items are
set by ALLBASE/SQL. It also gives several examples of implicit and
explicit status checking, some of which use SQLEXPLAIN to display a
status message.