HP 3000 Manuals HP 3000 Manuals
Using the SQLCA
The SQLCA is used for communicating information between the application
program and ALLBASE/SQL. SQL places information in the SQLCA each time it
is called. Since there is no guarantee that information from one call to
SQL will be present after the next call to SQL, any information needed
from the SQLCA must be obtained after each call to ALLBASE/SQL.
Every ALLBASE/SQL Pascal main program must have the SQLCA declared in the
global declaration section. You can use the INCLUDE command to declare
the SQLCA:
EXEC SQL INCLUDE SQLCA;
When the Pascal preprocessor parses this command, it inserts the
following type definition into the modified source file:
sqlca: Sqlca_Type
You can also use this type definition in the global declaration section
of your source file instead of using the INCLUDE command to declare the
SQLCA.
The Pascal preprocessor generates the following record declaration for
sqlca_type in the type include file. This portion of the type include
file contains some conditional statements. The entire type include file
can be found in the chapter, "Using the ALLBASE/SQL Pascal Preprocessor."
(It is recommended that you initialize the SqlcaId element to blanks, one
time, before the first SQL statement in your program.)
SQLCA_TYPE = record
SQLCAID : packed array [1..8] of char;
SQLCABC : integer;
SQLCODE : integer;
SQLERRM : string[255];
SQLERRP : packed array [1..8] of char;
SQLERRD : array [1..6] of integer;
$if 'XOPEN_SQLCA'$
SQLWARN0, SQLWARN1, SQLWARN2,
SQLWARN3, SQLWARN4, SQLWARN5,
SQLWARN6, SQLWARN7 : char;
$else$
SQLWARN : packed array [0..7] of char;
$endif$
SQLEXT : packed array [1..8] of char;
end;
The following elements in this record are available for you to use in
status checking. The other elements are reserved for use by ALLBASE/SQL
only.
SQLCA.SQLCODE or SQLCODE
SQLCA.SQLERRD[3]
SQLCA.SQLWARN[0] or SQLCA.SQLWARN0
SQLCA.SQLWARN[1] or SQLCA.SQLWARN1
SQLCA.SQLWARN[2] or SQLCA.SQLWARN2
SQLCA.SQLWARN[3] or SQLCA.SQLWARN3
SQLCA.SQLWARN[6] or SQLCA.SQLWARN6
In conformance with XOPEN standards, SQLCODE can be used to address this
particular element, and each SQLWARN element can be addressed without the
use of square brackets. If you choose to use XOPEN standards addressing,
you must include the following compiler directive in your source code:
$ SET `XOPEN_SQLCA=TRUE'$
(Note, use this compiler directive only if you are using XOPEN standards
addressing.)
The SQLCA must be passed whenever you call a subprogram that executes SQL
commands. The recommended method of doing so is to declare the SQLCA
globally in the main program. This is true even though your main program
contains no other SQL statement. The SQLCA must be a VAR parameter to
these subprograms in order to save space and attain the best performance.
Table 5-1. SQLCA Status Checking Fields
--------------------------------------------------------------------------------------------------
| | | |
| FIELD NAME | SET TO | CONDITION |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLCODE | 0 | No error occurred during command |
| or SQLCODE | | execution |
| | Less than 0 | |
| | | Error, command not executed |
| | 100 | |
| | | No rows qualify for DML operation |
| | | (does not apply to dynamic commands) |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLERRD[3] | Number of rows put | Data retrieval operation |
| | into output host | |
| | variables | Data change operation |
| | | |
| | Number of rows | Error in single row data change |
| | processed | operation |
| | | |
| | 0 | SQLCODE equals 100 |
| | | |
| | 0 | |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLWARN[0] | W | Warning, command not properly |
| or SQLCA.SQLWARN0 | | executed |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLWARN[1] | W | At least one character string value |
| or SQLCA.SQLWARN1 | | was truncated when being stored in |
| | | a host variable |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLWARN[2] | W | At least one null value was eliminated |
| or SQLCA.SQLWARN2 | | from the argument set of an aggregrate |
| | | function |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLWARN[3] | W | For dynamic commands only, when the |
| or SQLCA.SQLWARN3 | | number of host variables in a SELECT |
| | | or FETCH is unequal to the number of |
| | | columns in the table being operated on |
| | | |
--------------------------------------------------------------------------------------------------
| | | |
| SQLCA.SQLWARN[6] | W | The current transaction was rolled back |
| or SQLCA.SQLWARN6 | | |
| | | |
--------------------------------------------------------------------------------------------------
SQLCODE
SQLCODE can contain one of the following values:
* 0, when an SQL command executes without generating a warning or
error condition.
* A negative number, when an SQL command cannot be executed because
an error condition exists.
* 100, when no row qualifies for one of the following commands, but
no error condition exists:
SELECT FETCH
INSERT BULK FETCH
UPDATE (non-dynamic execution only) UPDATE WHERE CURRENT
DELETE (non-dynamic execution only) DELETE WHERE CURRENT
BULK SELECT
Note that when you prepare and execute UPDATE or DELETE commands and no
rows qualify for the operation, SQLCODE is not set to 100. You can use
SQLCA.SQLERRD[3] to detect this condition, as discussed later in this
chapter.
Negative SQLCODE values are the same as the numbers associated with their
corresponding messages in the ALLBASE/SQL message catalog. For example,
the error message associated with an SQLCODE of -2613 is:
Precision digits lost in decimal operation MULTIPLY. (DBERR 2613)
SQLCODE is set by all SQL commands except the following directives:
BEGIN DECLARE SECTION
DECLARE
END DECLARE SECTION
INCLUDE
WHENEVER
When SQLCODE is -4008, -14024, or a greater negative value than -14024,
ALLBASE/SQL automatically rolls back the current transaction. When this
condition occurs, ALLBASE/SQL also sets SQLWARN[6] to W. Refer to the
discussion later in this chapter on SQLWARN[6] for more on this topic.
More than one SQLCODE is returned when more than one error occurs. For
example, if you attempt to execute the following SQL command, two
negative SQLCODE values result:
EXEC SQL ADD PUBLIC, GROUP1 TO GROUP GROUP1;
The following SQLCODES associated with the two errors are:
-2308, which indicates the reserved name PUBLIC is invalid.
-2318, which indicates you cannot add a group to itself.
To obtain all SQLCODEs associated with the execution of an SQL command,
you execute the SQLEXPLAIN command until SQLCODE is 0:
if SQLCA.SQLCODE = 100 then
writeln('No rows qualified for this operation.');
else
if SQLCA.SQLCODE < 0 then SQLStatusCheck;
.
.
.
procedure SQLStatusCheck;
begin
repeat
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
until SQLCA.SQLCODE = 0;
end;
The procedure named SQLStatusCheck is executed when SQLCODE is a negative
number. Before executing SQLEXPLAIN for the first time, the program has
access to the first SQLCODE returned. Each time SQLEXPLAIN is executed
subsequently, the next SQLCODE becomes available to the program, and so
on until SQLCODE equals 0.
This example explicitly tests the value of SQLCODE twice: first to
determine whether it is equal to 100, then to determine whether it is <0.
If the value 100 exists, no error will have occurred and the program will
display the message, "No rows qualify for this operation."
It is necessary for the program to display its own message in this case
because SQLEXPLAIN messages are available to your program only when
SQLCODE contains a negative number or when SQLWARN[0] contains a W.
The SQLCODE is also used in implicit status checking in the following
situations:
* ALLBASE/SQL tests for the condition SQLCODE less than 0 when you
use the SQLERROR option of the WHENEVER command.
* ALLBASE/SQL tests for the condition SQLCODE equal to 100 when you
use the NOT FOUND option of the WHENEVER command.
In the following situation, when ALLBASE/SQL detects a negative SQLCODE,
the code routine at label 2000 is executed. When ALLBASE/SQL detects an
SQLCODE of 100, the code routine at label 4000 is executed instead, as
follows:
EXEC SQL WHENEVER SQLERROR GOTO 2000;
EXEC SQL WHENEVER NOT FOUND GOTO 4000;
WHENEVER commands remain in effect for all SQL commands that appear
physically after them in the source program until another WHENEVER
command for the same condition occurs.
The scope of WHENEVER commands is fully explained later in this chapter
under "Implicit Status Checking Techniques."
SQLERRD[3]
SQLERRD[3] can contain one of the following values:
* 0, when SQLCODE is 100 or when one of the following commands
causes an error condition:
INSERT
UPDATE
DELETE
UPDATE WHERE CURRENT
DELETE WHERE CURRENT
If an error occurs during execution of INSERT, UPDATE, or DELETE,
one or more rows may have been processed prior to the error. In
these cases, you may want to either COMMIT WORK or ROLLBACK WORK,
depending on the application. For example, if all or no rows
should be updated for logical data consistency, use ROLLBACK WORK.
However, if logical data consistency is not an issue, COMMIT WORK
may minimize re-preprocessing time.
* A positive number, when SQLCODE is 0. In this case, the positive
number provides information about the number of rows processed in
the following data manipulation commands:
The number of rows inserted, updated, or deleted in one of the
following operations:
BULK INSERT
INSERT
UPDATE
DELETE
UPDATE WHERE CURRENT
DELETE WHERE CURRENT
The number of rows put into output host variables when one of the
following commands is executed:
SELECT
BULK SELECT
FETCH
BULK FETCH
* A positive number when SQLCODE is less than 0. In this case,
SQLERRD[3] indicates the number of rows that were successfully
retrieved or inserted prior to the error condition:
BULK SELECT
BULK FETCH
BULK INSERT
As in the case of INSERT, UPDATE, and DELETE, mentioned above, you
can use either a COMMIT WORK or ROLLBACK WORK command, as
appropriate.
SQLCA.SQLWARN[0]
A W in SQLWARN[0] in conjunction with a 0 in SQLCODE indicates that the
SQL command just executed caused a warning condition.
Warning conditions flag unusual but not necessarily important conditions.
For example, if a program attempts to submit an SQL command that grants
an already existing authority, a message such as the following would be
retrieved when SQLEXPLAIN is executed:
User peg already has DBA authorization. (DBWARN 2006)
In the case of the following warning, the situation may or may not
indicate a problem:
A transaction in progress was aborted. (DBWARN 2010)
This warning occurs when a program submits a RELEASE command without
first terminating a transaction with a COMMIT WORK or ROLLBACK WORK. If
the transaction did not perform any UPDATE, INSERT, or DELETE operations,
this situation will not cause work to be lost. If the transaction did
perform UPDATE, INSERT, or DELETE operations, the database changes are
rolled back when the RELEASE command is processed.
You retrieve the appropriate warning message by using SQLEXPLAIN. Note
that you cannot explicitly test sqlwarn[0] the way you can test SQLCODE,
since sqlwarn[0] always contains W when a warning occurs.
An error and a warning condition may exist at the same time. In this
event, SQLCODE is set to a negative number, and sqlwarn[0] is set to W.
Messages describing all the warnings and errors can be displayed as
follows:
if SQLCA.SQLCODE <> 0 then
repeat
DisplayMessage;
until SQLCA.SQLCODE = 0;
.
.
.
procedure DisplayMessage;
begin
EXEC SQL SQLEXPLAIN :StatusMessage;
writeln(StatusMessage);
end;
If multiple warnings but no errors result when ALLBASE/SQL processes a
command, SQLWARN[0] is set to W and remains set until the last warning
message has been retrieved by SQLEXPLAIN or another SQL command is
executed. In the following example, DisplayWarning is executed when this
condition exists.
if ((SQLCA.SQLCODE = 0) and (SQLCA.SQLWARN[0] = 'W')) then
repeat
DisplayWarning;
until SQLCA.SQLWARN[0] <> 'W';
.
.
.
procedure DisplayWarning;
begin
EXEC SQL SQLEXPLAIN :StatusMessage;
writeln(StatusMessage);
end;
When you use the SQLWARNING option of the WHENEVER command, ALLBASE/SQL
checks for a W in SQLWARN[0]. You can use the WHENEVER command to do
implicit status checking (equivalent to that done explicitly above) as
follows:
EXEC SQL WHENEVER SQLWARNING GOTO 3000;
EXEC SQL WHENEVER SQLERROR GOTO 2000;
SQLCA.SQLWARN[1]
A W in sqlwarn[1] indicates truncation of at least one character string
value when the string was stored in a host variable. Any associated
indicator variable is set to the value of the string length before
truncation, for example:
For example:
EXEC SQL SELECT PartNumber,
PartName
INTO :PartNumber
:PartName :PartNameInd
FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
If PartName was declared as a character array of 20 bytes, and the
PartName column in the PurchDB.Parts table has a length of 30 bytes, then
SQL performs the following tasks:
* SQLWARN[1] is set to W.
* PartNameInd is set to 30 (the length of PartName in the table).
* SQLCODE is set to 0.
* SQLEXPLAIN retrieves the message:
Character string truncation during storage in host variable.
(DBWARN 2040)
SQLCA.SQLWARN[2]
A W in sqlwarn[2] indicates that at least one null value was eliminated
from the argument set of an aggregrate function.
For example:
EXEC SQL SELECT MAX(OrderQty)
INTO :MaxOrderQty
FROM PurchDB.OrderItems;
If any OrderQty values are null:
* SQLWARN[2] is set to W.
* SQLCODE is set to 0.
* SQLEXPLAIN retrieves the message:
NULL values eliminated from the argument of an aggregate
function. (DBWARN 2041)
SQLCA.SQLWARN[3]
A W in sqlwarn[3] indicates that the number of columns specified in a
dynamic SELECT or FETCH statement is unequal to the number of columns
indicated in the sqld field of the SQLDA. Under normal circumstances,
this error does not occur, because the DESCRIBE command sets the sqld
field correctly. Look at this example:
EXEC SQL PREPARE DynamicCommand from 'SELECT PartNumber, PartName
FROM PurchDB.Parts;';
EXEC SQL DESCRIBE DynamicCommand INTO SQLDA; /*SQLDA.SQLD is always set
at DESCRIBE by ALLBASE/SQL.*/
EXEC SQL DECLARE DynamicCursor FOR DynamicCommand;
EXEC SQL OPEN DynamicCursor;
/* Set up the SQLDA for a fetch. */
begin
with SQLDA do
begin
SqlBufLen := sizeof(DataBuffer);
SqlNRow := SqlBufLen DIV SqlRowLen;
SqlRowBuf := waddress(DataBuffer);
Sqld := 1; /* Oops! sqlda.sqld is incorrectly reset by the program. */
/* You should NEVER do this. */
/* Only ALLBASE/SQL should set this field. */
end;
end;
.
/* Do the fetch. */
EXEC SQL FETCH DynamicCursor USING DESCRIPTOR SQLDA;
The FETCH will fail and ALLBASE/SQL performs the following tasks:
* SQLWARN[3] is set to W.
* SQLCODE is set to -2762.
* SQLEXPLAIN retrieves the message:
Select list has ! items and host variable buffer has !.
(DBERR 2762)
SQLCA.SQLWARN[6]
When an error occurs that causes ALLBASE/SQL to roll back the current
transaction, SQLWARN[6] is set to W. ALLBASE/SQL automatically rolls back
transactions when SQLCODE is equal to -4008, or equal to or less than
-14024.
When such errors occur, ALLBASE/SQL does the following:
* Sets SQLWARN[6] to W.
* Sets SQLWARN[0] to W.
* Sets SQLCODE to a negative number.
If you want to terminate your program any time ALLBASE/SQL has to roll
back the current transaction, you can just test sqlwarn[6].
if SQLCA.SQLCODE < 0 then
if SQLCA. SQLWARN[6] = 'W' then
begin
SQLStatusCheck;
TerminateProgram;
end
else
SQLStatusCheck;
In this example, the program executes procedure SQLStatusCheck when an
error occurs. The program terminates whenever ALLBASE/SQL has rolled
back a transaction, but continues if an error has occurred but was not
serious enough to cause transaction roll back.