HP 3000 Manuals HP 3000 Manuals
The SQLCA COMMON Block
Every ALLBASE/SQL FORTRAN program unit must have the EXEC SQL INCLUDE
SQLCA statement before the Host Variable Declaration Section to declare
the SQL Communication Area:
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
.
C Host Variable Declaration Section
.
EXEC SQL END DECLARE SECTION
The FORTRAN preprocessor generates the following declaration in the
modified source file after it parses this SQL command:
C**** Start SQL Preprocessor ****
C EXEC SQL INCLUDE SQLCA
C
C**** Start Inserted Statements ****
CHARACTER SQLCAID*8
INTEGER SQLCABC,
1 SQLCODE
INTEGER SQLERRL
CHARACTER SQLERRM*254,
1 SQLERRP*8
INTEGER SQLERRD(6)
CHARACTER SQLWARN(0:7)
INTEGER SQLEXT(2)
CHARACTER SQLWARN0,SQLWARN1,SQLWARN2,SQLWARN3,
1 SQLWARN4,SQLWARN5,SQLWARN6,SQLWARN7
EQUIVALENCE (SQLWARN0,SQLWARN(0)),
1 (SQLWARN1,SQLWARN(1)),
1 (SQLWARN2,SQLWARN(2)),
1 (SQLWARN3,SQLWARN(3)),
1 (SQLWARN4,SQLWARN(4)),
1 (SQLWARN5,SQLWARN(5)),
1 (SQLWARN6,SQLWARN(6)),
1 (SQLWARN7,SQLWARN(7))
COMMON /Sqlca/ SQLCAID,SQLCABC,SQLCODE,SQLERRL,
1 SQLERRM,SQLERRP,SQLERRD,SQLWARN,SQLEXT
C**** End SQL Preprocessor ****
The following fields in this record are available for you to use in
status checking.
SQLCODE
SQLERRD(3)
SQLWARN(0)
SQLWARN(1)
SQLWARN(2)
SQLWARN(6)
The other fields are reserved for use by ALLBASE/SQL only.
As discussed in Chapter 4, the SQLCA COMMON block must be included
whenever a program unit executes SQL commands. If no EXEC SQL INCLUDE
SQLCA statement is included, the FORTRAN preprocessor will issue a
warning message. If a program accesses multiple DBEnvironments, each
DBEnvironment requires a separate SQLCA. Consequently, ensure that all
program units that access the same DBEnvironment are preprocessed
separately from any program units that access a different DBEnvironment.
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
INSERT
UPDATE
DELETE
FETCH
UPDATE WHERE CURRENT
DELETE WHERE CURRENT
Note that when you execute UPDATE or DELETE commands dynamically and no
rows qualify for the operation, SQLCode is not set to 100. You can use
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 CURSOR
END DECLARE SECTION
INCLUDE SQLCA
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 information 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 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 (SQLCode .EQ. 100) THEN
WRITE(6,102) 'No rows qualified for this operation.'
102 FORMAT(A80)
ELSEIF (SQLCode .LT. 0) THEN
CALL SQLStatusCheck
ENDIF
:
SUBROUTINE SQLStatusCheck
:
SQLCodeTmp = SQLCode
DO WHILE (SQLCode .NE. 0)
EXEC SQL SQLEXPLAIN :SQLMessage
CALL WriteOut (SQLMessage)
END DO
SQLCode = SQLCodeTmp
.
.
RETURN
END
The subroutine 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 subsequently executed, the next SQLCode becomes available to the
program, and so on until SQLCode equals zero. If the user needs to have
further access to a SQLCode value, the SQLCode value needs to be saved
into another data variable. Each time SQLEXPLAIN or any other SQL
command is executed, the SQLCode value changes to reflect the result of
the previously executed command.
This example explicitly tests the value of SQLCode twice: first to
determine whether it is equal to 100, then to determine whether it is
less than 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 only negative SQLCodes and the SQLWarn(0) W flag have messages to
describe their corresponding conditions.
The SQLCode is also used in implicit status checking:
* ALLBASE/SQL tests for the condition SQLCode less than zero (<0)
when you use the SQLERROR option of the WHENEVER command.
* ALLBASE/SQL tests for the condition SQLCode equal to 100 (=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 in the same program unit is executed.
When ALLBASE/SQL detects an SQLCode of 100, the code routine at label
4000 in the same program unit is executed instead:
EXEC SQL WHENEVER SQLERROR GOTO 2000
EXEC SQL WHENEVER NOT FOUND GOTO 4000
WHENEVER commands remain in effect for all SQL commands that appear
sequentially after them in the modified source code until another
WHENEVER command for the same condition occurs. The following WHENEVER
command, for example, changes the effect of an SQLCode of 100. Instead
of the code routine at Label 4000 in the same program unit being
executed, the code routine at label 4500 in the same program unit is
executed:
EXEC SQL WHENEVER NOT FOUND GOTO 4500
The scope of WHENEVER commands is fully explained later in this chapter
under "Implicit Status Checking."
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 an INSERT, UPDATE, or
DELETE command, 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 for
logical data consistency all or no rows should be deleted, use
ROLLBACK WORK. If logical data consistency is not an issue, COMMIT
WORK may minimize re-processing time.
* A positive number that provides information about the number of
rows processed in any data manipulation command.
The meaning of any positive SQLErrd(3) value depends on the SQLCode
value.
When SQLCode is 0, SQLErrd(3) indicates:
* The number of rows processed in one of the following operations:
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
FETCH
SQLWARN(0)
A W in SQLWarn(0) in conjunction with a 0 (zero) 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 authority. (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
command. If the transaction performed no UPDATE, INSERT, or DELETE
operations, this situation causes no 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.
An error and a warning condition may exist at the same time. In this
event, SQLCode is set to a negative number, but SQLWarn(0) is set to W
only if SQLWarn(6) is set to W. Messages describing all the warnings and
errors can be displayed as follows:
.
.
IF (SQLCode .NE. 0) THEN
DO WHILE (SQLCode .NE. 0)
CALL DisplayMessage
END DO
ENDIF
.
.
SUBROUTINE DisplayMessage
EXEC SQL SQLEXPLAIN :SQLMessage
WRITE(6,102) SQLMessage
102 FORMAT(A120)
.
.
RETURN
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 ((SQLWarn(0) .EQ. 'W') .AND. (SQLCode .EQ. 0)) THEN
DO WHILE (SQLWarn(0) .EQ. 'W')
CALL DisplayWarning
END DO
ENDIF
:
SUBROUTINE DisplayWarning
:
EXEC SQL SQLEXPLAIN :SQLMessage
WRITE(6,102) SQLMessage
102 FORMAT(A120)
:
RETURN
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
When a warning condition that sets SQLWarn(0) occurs, SQLCode does not
contain a value that describes the warning. Therefore you cannot
explicitly evaluate the contents of SQLCode in order to conditionally
handle warnings. You can either display the message SQLEXPLAIN retrieves
from the ALLBASE/SQL catalog or you can ignore the warning.
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:
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:
* 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)
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)
SQLWARN(6)
When an error exists so serious that ALLBASE/SQL has 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 is -14024 or
less:
* An SQLCode of -4008 indicates that ALLBASE/SQL does not have
access to the amount of shared memory required to complete the
execution of an open transaction:
ALLBASE/SQL Shared Memory allocation failed in DBCore. (DBERR 4008)
* An SQLCode of -14024 indicates that a deadlock has occurred:
Deadlock detected. (DBERR 14024)
A deadlock exists when each of two transactions needs data that
the other transaction already has locked. When a deadlock occurs,
ALLBASE/SQL rolls back the transaction with the larger priority
number. If two deadlocked transactions have the same priority,
ALLBASE/SQL rolls back the newer transaction.
* An SQLCode with a greater negative value than -14024 indicates
that the error is serious enough to warrant terminating your
program. For example, when the log file is full, log space needs
to be reclaimed before ALLBASE/SQL can process any additional
transactions:
Log full. (DBERR 14046)
When these errors occur, ALLBASE/SQL sets SQLWarn(6) to W, SQLWarn(0) to
W, and SQLCode to a negative number. You only need to examine SQLWarn(6)
if you want to terminate your program any time ALLBASE/SQL has to roll
back the current transaction:
IF ((SQLCode .LT. 0) .AND. ( SQLWARN(6) .EQ. 'W')) THEN
CALL SQLStatusCheck
CALL TerminateProgram
ELSE
CALL SQLStatusCheck
ENDIF
In this example, the program executes subprogram unit SQLStatusCheck when
an error occurs. The program terminates whenever SQLWarn(6) is W, but
continues if SQLWarn(6) is not W.
If a deadlock or a shared memory problem occurs, the contention that
caused it may not exist if the transaction is restarted. In this case,
you may want to examine both SQLWarn(6) and SQLCode and terminate the
program only when SQLCode is less than -14024:
.
.
.
100 CONTINUE
C This is the RESTART POINT
.
.
.
IF (SQLCode .GT. -14025) THEN
DO WHILE (SQLCode .NE. 0)
EXEC SQL SQLEXPLAIN :SQLMessage
CALL WriteOut (SQLMessage)
END DO
GOTO 100
ENDIF
IF ((SQLWARN(6) .EQ. 'W') .AND. (SQLCode .LT. -14024)) THEN
DO WHILE (SQLCode .NE. 0)
EXEC SQL SQLEXPLAIN :SQLMessage
CALL WriteOut (SQLMessage)
END DO
CALL TerminateProgram
ENDIF
If a deadlock or a shared memory problem occurs, the program displays all
the messages, then continues. The program also continues when an error
exists but is not serious enough to cause ALLBASE/SQL to roll back the
current transaction. In the case of serious errors, however, SQLCode is
set to less than -14024, and the program terminates after displaying all
the messages.
If multiple SQLCodes result when ALLBASE/SQL processes a command that
causes the current transaction to be rolled back, SQLWarn(6) is set to W
in conjunction with the first available SQLCode. Therefore, if your
program needs to examine SQLWarn(6), ensure that you examine it before
using SQLEXPLAIN for the second time or it will be reset.
If one or more errors are detected before an automatic rollback occurs,
the first SQLCode available to your program will not be equal to -4008 or
greater than or equal to -14024. However, should one of these conditions
occur, the corresponding SQLCode is guaranteed to be the last SQLCode
available to your program, since ALLBASE/SQL rolls back the current
transaction and does not continue to look for additional errors. You can
use this characteristic to construct a test such that a transaction is
automatically reapplied behind the program user's back only if a deadlock
or a shared memory problem occurs but no other errors were detected
first:
TryCounter = 0
TryLimit = 3
.
.
100 IF (SQLCommandDone) THEN
.
. Program user is prompted for a part number.
.
SQLCommandDone = .TRUE.
.
. A SELECT command is attempted.
.
Trycounter = TryCounter +1
.
.
IF ((SQLCode .EQ. -14024).OR.(SQLCode .EQ. -4008)) THEN
IF (Trycounter .EQ. TryLimit) THEN
SQLCommandDone = .FALSE.
WRITE (*,*) 'Could not complete transaction.'
WRITE (*,*) 'Try again later if you want.'
ELSE
SQLCommandDone = .TRUE.
ENDIF
ELSE
Abort = .FALSE.
IF (SQLWarn(6) .EQ. 'W') THEN
Abort = .TRUE.
ENDIF
DO WHILE (SQLCode .NE. 0)
EXEC SQL SQLEXPLAIN :SQLMessage
WRITE (*,110) SQLMessage
110 FORMAT(A120)
END DO
IF (Abort) THEN
CALL TerminateProgram
ELSE
SQLCommandDone = .TRUE.
ENDIF
ENDIF
.
.
GOTO 100
At this point, a SELECT command is executed. If an error occurs, and if
the first error detected was a deadlock or a shared memory problem, the
SELECT command is automatically re-executed as many as three times before
the user is notified of the situation. If other errors occurred before
the deadlock or shared memory problem, the transaction is not
automatically reapplied. If an error with an SQLCode less then -14024
occurred, the program is terminated after the error messages are
displayed.