The SQLCA COMMON Block

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 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 the chapter, "Host Variables," 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.

SQLCA.SQLCODE

SQLCA.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 SQLCA.SQLErrd(3) to detect this condition as discussed later in this chapter.

Negative SQLCA.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 SQLCA.SQLCode of -2613 is:

   Precision digits lost in decimal operation multiply.  (DBERR 2613)

SQLCA.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 SQLCA.SQLCode is returned when more than one error occurs. For example, if you attempt to execute the following SQL command, two negative SQLCA.SQLCode values result:

   EXEC SQL ADD PUBLIC, GROUP1 TO GROUP GROUP1

The SQLCA.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 SQLCA.SQLCodes associated with the execution of an SQL command, you execute the SQLEXPLAIN command until SQLCA.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 SQLCA.SQLCode is a negative number. Before executing SQLEXPLAIN for the first time, the program has access to the first SQLCA.SQLCode returned. Each time SQLEXPLAIN is subsequently executed, the next SQLCA.SQLCode becomes available to the program, and so on until SQLCA.SQLCode equals zero. If the user needs to have further access to a SQLCA.SQLCode value, the SQLCA.SQLCode value needs to be saved into another data variable. Each time SQLEXPLAIN or any other SQL command is executed, the SQLCA.SQLCode value changes to reflect the result of the previously executed command.

This example explicitly tests the value of SQLCA.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 SQLCA.SQLCodes and the SQLCA.SQLWarn(0) W flag have messages to describe their corresponding conditions.

The SQLCA.SQLCode is also used in implicit status checking:

ALLBASE/SQL tests for the condition SQLCA.SQLCode less than zero (<0) when you use the SQLERROR option of the WHENEVER command.
ALLBASE/SQL tests for the condition SQLCA.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 SQLCA.SQLCode, the code routine at Label 2000 in the same program unit is executed. When ALLBASE/SQL detects an SQLCA.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 SQLCA.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."

SQLCA.SQLERRD(3)

SQLCA.SQLErrd(3) can contain one of the following values:

0, when SQLCA.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 SQLCA.SQLErrd(3) value depends on the SQLCA.SQLCode value. When SQLCA.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

SQLCA.SQLWARN(0)

A W in SQLCA.SQLWarn(0) in conjunction with a 0 in SQLCA.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, SQLCA.SQLCode is set to a negative number, but SQLCA.SQLWarn(0) is set to W only if SQLCA.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 SQLCA.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 SQLCA.SQLWarn(0) occurs, SQLCA.SQLCode does not contain a value that describes the warning. Therefore you cannot explicitly evaluate the contents of SQLCA.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.