Sequential Table Processing Commands

The SQL commands used for sequential table processing are:

DECLARE CURSOR: defines a cursor and associates with it a query.
OPEN: defines the active set.
FETCH: retrieves one row of the active set into host variables; when a row resides in host variables it is known as the current row. When a row is current and the active set is a query result derived from a single table, you can use one of the following two commands to change the row.
UPDATE WHERE CURRENT: updates the current row.
DELETE WHERE CURRENT: deletes the current row.
CLOSE: frees up ALLBASE/SQL intermal buffer space used to handle the cursor.

Refer to the ALLBASE/SQL Reference Manual for the complete syntax and semantics of these commands. Ensure that all the commands listed above for any single cursor are contained within the same transaction.

The DECLARE CURSOR Command

The DECLARE CURSOR command names a cursor and associates with it a particular SELECT command:

        DECLARE CursorName

                [IN DBEFileSetName]

                CURSOR FOR

                SelectCommand

                [FOR UPDATE OF ColumnName [,ColumnName...]]

Note that the DECLARE CURSOR command has two optional clauses:

The IN clause defines the DBEFileSet in which the section generated by the preprocessor for this command is stored. If no IN clause is specified, file space in the SYSTEM DBEFileSet is used.
The FOR UPDATE clause is used when you use the UPDATE WHERE CURRENT command to update a current row. This command may offer the simplest way to update a current row, but it imposes certain restrictions on the SELECT command. Updating a current row is fully discussed later in this chapter under the UPDATE WHERE CURRENT command.

The SELECT command for cursor declarations that do not include the FOR UPDATE clause can consist of any of the SELECT command clauses except the INTO clause:

        SELECT SelectList

          FROM TableNames

         WHERE SearchCondition1

      GROUP BY ColumnNames

        HAVING SearchCondition2

      ORDER BY ColumnIdentifiers

A SELECT command associated with a cursor does not name output host variables, but may name input host variables in the select list, the WHERE clause, and the HAVING clause. In the following example, the rows qualifying for the query result will be those with a CountCycle matching that specified by the user in input host variable CountCycle:

          EXEC SQL DECLARE Inventory

        1           CURSOR FOR

        2           SELECT PartNumber,

        3                  BinNumber,

        4                  QtyOnHand,

        5                  AdjustmentQty

        6             FROM PurchDB.Inventory

        7            WHERE CountCycle = :CountCycle

        8         ORDER BY BinNumber

When performing sequential table processing, the ORDER BY clause may be useful. In the example above, the rows in the query result will be in order by ascending bin number to help the program user, who will be moving from bin to bin, taking a physical inventory.

The DECLARE CURSOR command is actually a preprocessor directive. When the FORTRAN preprocessor parses this command, it stores a section in the target DBEnvironment. At runtime, the section is not executed when the DECLARE CURSOR command is encountered, but when the OPEN command is executed. Because the DECLARE CURSOR command is not executed at runtime, you do not need to perform status checking in your program following this command.

The OPEN Command

The OPEN command examines any input host variables and determines the active set:

        OPEN CursorName

The following command defines the active set associated with the cursor defined earlier:

        EXEC SQL OPEN Inventory

You use the FETCH command to retrieve a row at a time from the active set.

You can use the KEEP CURSOR WITH NOLOCKS option for a cursor that involves sorting, whether through the use of a DISTINCT, GROUP BY, or ORDER BY clause, or as the result of a union or a join operation. However, for kept cursors involving sorting, ALLBASE/SQL does not ensure data integrity. See the "Programming for Performance" chapter for more information on ensuring data integrity.

The FETCH Command

The FETCH command defines a current row and delivers the row into output host variables:

        FETCH CursorName INTO :OutputHostVariables

Remember to include indicator variables when one or more columns in the query result may contain a null value:

          EXEC SQL FETCH  Inventory

        1           INTO :PartNumber,

        2                :BinNumber,

        4                :QtyOnHand     :QtyOnHandInd,

        5                :AdjustmentQty :AdjustmentQtyInd

The first time you execute the FETCH command, the first row in the query result is the current row. With each subsequent execution of the FETCH command, each succeeding row in the query result becomes current. After the last row in the query result has been fetched, ALLBASE/SQL sets SQLCode to 100. ALLBASE/SQL also sets SQLCode to 100 if no rows qualify for the active set. You should test for an SQLCode value of 100 after each execution of the FETCH command to determine whether to re-execute this command:

        SUBROUTINE GetARow

        .

        .

        OK       = 0

        NotFound = 100

        DoFetch  = .TRUE.

        DO WHILE (DoFetch)

        .

        .     The FETCH command appears here.

        .

        IF (SQLCode .EQ. OK) THEN

          CALL DisplayRow

        ELSEIF (SQLCode .EQ. NotFound) THEN

          DoFetch = .FALSE.

          CALL CloseCursor

          CALL CommitWork

        ELSE

          DoFetch = .FALSE.

          CALL SQLStatusCheck

          CALL CloseCursor

          CALL RollBackWork

        ENDIF

        END DO

        RETURN

        END

When a row is current, you can update it by using the UPDATE WHERE CURRENT command or delete it by using the DELETE WHERE CURRENT command.

The UPDATE WHERE CURRENT Command

This command can be used to update the current row when the SELECT command associated with the cursor does not contain one of the following:

DISTINCT clause in the select list.
Aggregate function in the select list.
FROM clause with more than one table.
ORDER BY clause.
GROUP BY clause.

The UPDATE WHERE CURRENT command identifies the active set to be updated by naming the cursor and the column(s) to be updated:



        UPDATE TableName

           SET ColumnName = ColumnValue

               [,...]

         WHERE CURRENT OF CursorName

Any columns you name in this command must also have been named in a FOR UPDATE clause in the related DECLARE CURSOR command:



        EXEC SQL DECLARE AdjustQtyOnHand

       1          CURSOR FOR

       2          SELECT PartNumber,

       3                 BinNumber,

       4                 QtyOnHand,

       5                 AdjustmentQty

       6            FROM PurchDB.Inventory

       7           WHERE QtyOnHand IS NOT NULL

       8             AND AdjustmentQty IS NOT NULL

       9   FOR UPDATE OF QtyOnHand,

       1                 AdjustmentQty



        EXEC SQL OPEN AdjustQtyOnHand

      .

      .  The output host variables do not need to include

      .  indicator variables, because the SELECT command

      .  associated with the cursor eliminates any rows having

      .  null values from the active set:

      .

        EXEC SQL FETCH  AdjustQtyOnHand

       1          INTO :PartNumber,

       2               :BinNumber,

       3               :QtyOnHand,

       4               :AdjustmentQty

        .

        .

        .

        EXEC SQL UPDATE PurchDB.Inventory

       1            SET QtyOnHand     = :QtyOnHand + :AdjustmentQty,

       2                AdjustmentQty = 0

       3          WHERE CURRENT OF AdjustQtyOnHand

In this example, the order of the rows in the query result is not important. Therefore the SELECT command associated with cursor AdjustQtyOnHand does not need to contain an ORDER BY clause and the UPDATE WHERE CURRENT command can be used.

In cases where order is important and the ORDER BY clause must be used, you can use the form of the UPDATE command described in Chapter 6 to update values in the current row. In this case, if more than one row qualifies for the search condition in the UPDATE command, more rows than just the current row will be changed:

      EXEC SQL DECLARE Inventory

     1          CURSOR FOR

     2          SELECT PartNumber,

     3                 BinNumber,

     4                 QtyOnHand,

     5                 AdjustmentQty

     6            FROM PurchDB.Inventory

     7           WHERE CountCycle = :CountCycle

     8        ORDER BY BinNumber

      .

      .

      .

      EXEC SQL FETCH  Inventory

     1          INTO :PartNumber,

     2               :BinNumber,

     3               :QtyOnHand     :QtyOnHandInd

     4               :AdjustmentQty :AdjustmentQtyInd

      .

      .   The program displays the current row.  If the

      .   QtyOnHand value is not null, the program prompts

      .   the user for an adjustment quantity.  Adjustment

      .   quantity is the difference between the quantity

      .   actually in the bin and the QtyOnHand in the row

      .   displayed. If the QtyOnHand value is null, the

      .   program prompts the user for both QtyOnHand and

      .   AdjustmentQty. Any value entered is used later to

      .   update AdjustmentQty. The value(s) entered, as well

      .   as the current PartNumber and BinNumber, are saved

      .   until all rows have been fetched and other values

      .   accepted from the user. Then one of the following

      .   UPDATE commands is executed for each UPDATE requested

      .   by the user:

         .

      EXEC SQL UPDATE PurchDB.Inventory

     1            SET AdjustmentQty = :AdjustmentQty

     2          WHERE PartNumber = :PartNumber

     3            AND BinNumber  = :BinNumber

        .

        .

        .

      EXEC SQL UPDATE PurchDB.Inventory

     1            SET QtyOnHand =     :QtyOnHand,

     2                AdjustmentQty = :AdjustmentQty

     3          WHERE PartNumber = :PartNumber

     4            AND BinNumber  = :BinNumber

After either the UPDATE WHERE CURRENT or the UPDATE command is executed, the current row remains the same until the FETCH command is re-executed.

The DELETE WHERE CURRENT Command

This command can be used to delete the current row when the SELECT command associated with the cursor does not contain one of the following:

DISTINCT clause in the select list.
Aggregate function in the select list.
FROM clause with more than one table.
ORDER BY clause.
GROUP BY clause.

The DELETE WHERE CURRENT command has a very simple structure:

    DELETE FROM TableName WHERE CURRENT OF CursorName

The DELETE WHERE CURRENT command can be used in conjunction with a cursor declared with or without the FOR UPDATE clause:

       The program displays the current row and asks

	   the user whether to update or delete it.  If the

	   user wants to delete the row, the following command

	   is executed:



        EXEC SQL DELETE FROM PurchDB.Inventory

       1          WHERE CURRENT OF AdjustQtyOnHand

Even though the SELECT command associated with cursor Inventory names only some of the columns in table PurchDB.Inventory, the entire current row is deleted.

After the DELETE WHERE CURRENT command is executed, there is no current row. You must re-execute the FETCH command to obtain another current row.

As in the case of the UPDATE WHERE CURRENT command, if the SELECT command associated with the cursor contains an ORDER BY clause or other components listed earlier, you can use the DELETE command to delete a row:



        EXEC SQL DELETE FROM PurchDB.Inventory

       1               WHERE PartNumber = :PartNumber

       2                 AND BinNumber  = :BinNumber

If you use the DELETE command to delete a row while using a cursor to examine an active set, remember that more than one row will be deleted if multiple rows satisfy the conditions specified in the WHERE clause of the DELETE command. In addition, the row that is current when the DELETE command is executed remains the current row until the FETCH command is re-executed.

The CLOSE Command

When you no longer want to operate on the active set, you use the CLOSE command:



        CLOSE CursorName

The CLOSE command frees up ALLBASE/SQL internal buffers used to handle cursor operations. This command does not release any locks obtained since the cursor was opened; to release locks, you must terminate the transaction:



       The program opens a cursor and operates

   	   on the active set.  After the last row has

   	   been operated on, the cursor is closed:



        EXEC SQL CLOSE Inventory



       Additional SQL commands are executed, then

   	   the transaction is terminated:



        EXEC SQL COMMIT WORK

You also use the CLOSE command when you want to re-access the active set. In this case, simply re-open the cursor after executing the CLOSE command. Because locks have not been released, any changes to the rows in the active set will be those made by your program since the cursor was first opened:



     Cursor Inventory is used to update information

	 in table PurchDB.Inventory.  After the last row

	 in the active set has been fetched and its information

	 changed, the cursor is closed:



     EXEC SQL CLOSE Inventory



     The cursor is then re-opened to allow the program

	 user to review the information and optionally make

	 some last-minute adjustments:



     EXEC SQL OPEN Inventory



     After the user has reviewed all rows in the active

	 set, any changes made to the active set are

	 made permanent as follows:



     EXEC SQL COMMIT WORK