SQL Cursor Commands

The following ALLBASE/SQL commands are used in cursor processing:

DECLARE CURSOR defines a cursor and associates it with 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 terminates access to the active set and frees up ALLBASE/SQL buffer space used to handle the cursor.

For a given cursor, the commands listed above (with the exception of DECLARE CURSOR) should be contained within the same transaction. Refer to the ALLBASE/SQL Reference Manual for the complete syntax and semantics of these commands.

DECLARE CURSOR

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



      DECLARE CursorName

              [IN DBEFileSetName]

              CURSOR FOR

              SelectCommand

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

This command does not retrieve rows from a table.

In the physical order of your source program statements, the DECLARE CURSOR command must precede any command that references the cursor; for example, the OPEN command.

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 OF 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 SelectCommand. Updating a current row is fully discussed later in this chapter under "UPDATE WHERE CURRENT."

The SELECT command syntax 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, or 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

                CURSOR FOR

                SELECT PARTNUMBER,

                       BINNUMBER,

                       QTYONHAND,

                       ADJUSTMENTQTY

                  FROM PURCHDB.INVENTORY

                 WHERE COUNTCYCLE = :COUNTCYCLE

              ORDER BY BINNUMBER

      END-EXEC.

When performing cursor processing, the ORDER BY clause may be useful. In the previous example, 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 COBOL preprocessor parses this command, it stores a section in the target DBEnvironment. At run time, 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 run time, you do not need to perform status checking in your program following this command.

OPEN

The OPEN command examines any input host variables, determines the active set, and allocates internal buffer space for the active set, with the following syntax:

OPEN CursorName [KEEP CURSOR] { WITH LOCKS WITH NOLOCKS }

The following command opens the cursor defined earlier:



         EXEC SQL OPEN INVENTORY END-EXEC.

Once the active set is defined, the FETCH command will retrieve data from it, one row at a time.

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.

For more information on using KEEP CURSOR see the "Using KEEP CURSOR" section later in this chapter.

FETCH

The FETCH command defines a current row and delivers the row into output host variables, as shown in the following syntax:



        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

                  INTO :PARTNUMBER,

                       :BINNUMBER,

                       :QTYONHAND     :QTYONHANDIND,

                       :ADJUSTMENTQTY :ADJUSTMENTQTYIND

        END-EXEC.

The first time you execute the FETCH command, the first row in the query result becomes 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:



        77   DONE-FETCH-FLAG      PIC X VALUE SPACE.

          88 NOT-DONE-FETCH       VALUE SPACE.

          88 DONE-FETCH           VALUE 'X'.

          .

          .

          .

        PROCEDURE DIVISION.

            .

            .

            PERFORM FETCH-ROW THRU FETCH-ROW-EXIT UNTIL DONE-FETCH.

            .

            .

            .

        FETCH-ROW.



            The FETCH command appears here.



            IF SQLCODE = 0 PERFORM DISPLAY-ROW

            ELSE

            IF SQLCODE = 100

               MOVE 'X' TO DONE-FETCH-FLAG

               DISPLAY "No rows qualify or no additional rows qualify."

               GO TO FETCH-ROW-EXIT

            ELSE

               PERFORM SQL-STATUS-CHECK.

        FETCH-ROW-EXIT.

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.

UPDATE WHERE CURRENT

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 syntax 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, as shown in the following syntax:



   EXEC SQL DECLARE ADJUSTQTYONHAND

             CURSOR FOR

             SELECT PARTNUMBER,

                    BINNUMBER,

                    QTYONHAND,

                    ADJUSTMENTQTY

               FROM PURCHDB.INVENTORY

              WHERE QTYONHAND IS NOT NULL

                AND ADJUSTMENTQTY IS NOT NULL

      FOR UPDATE OF QTYONHAND,

                    ADJUSTMENTQTY

   END-EXEC.



   EXEC SQL OPEN ADJUSTQTYONHAND END-EXEC.

   .

   In this case, the output host variables do not need to include

   indicator variables, because the SELECT command associated with the

   cursor eliminates from the active set any rows having null values.

   .

   EXEC SQL FETCH  ADJUSTQTYONHAND

             INTO :PARTNUMBER,

                  :BINNUMBER,

                  :QTYONHAND,

                  :ADJUSTMENTQTY

   END-EXEC.

   .

   .

   EXEC SQL UPDATE PURCHDB.INVENTORY

               SET QTYONHAND     = :QTYONHAND + :ADJUSTMENTQTY,

                   ADJUSTMENTQTY = 0

             WHERE CURRENT OF ADJUSTQTYONHAND

   END-EXEC.

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 UPDATE command with the WHERE clause to update values in the current row as well as any other rows that qualify for the search condition.



   EXEC SQL DECLARE INVENTORY

             CURSOR FOR

             SELECT PARTNUMBER,

                    BINNUMBER,

                    QTYONHAND,

                    ADJUSTMENTQTY

               FROM PURCHDB.INVENTORY

              WHERE COUNTCYCLE = :COUNTCYCLE

           ORDER BY BINNUMBER

   END-EXEC.

   .

   .

   .

   EXEC SQL FETCH  INVENTORY

             INTO :PARTNUMBER,

                  :BINNUMBER,

                  :QTYONHAND     :QTYONHANDIND,

                  :ADJUSTMENTQTY :ADJUSTMENTQTYIND

   END-EXEC.

   .

         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 int he following UPDATE command.

   .

   .

   EXEC SQL UPDATE PURCHDB.INVENTORY

               SET QTYONHAND =     :QTYONHAND :QTYONHANDIND,

                   ADJUSTMENTQTY = :ADJUSTMENTQTY :ADJUSTMENTQTYIND

             WHERE PARTNUMBER = :PARTNUMBER

               AND BINNUMBER  = :BINNUMBER

   END-EXEC.

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.

If you want to execute UPDATE commands inside the FETCH loop, remember that more than one row in the active set may qualify for the UPDATE operation, as when the WHERE clause in the UPDATE command does not specify a unique key. When more than one row qualifies for the UPDATE, you may not see a changed row unless you CLOSE and re-OPEN the cursor. To avoid this problem, either ensure your UPDATE commands change only one row (the current row) or perform the UPDATE operations outside the FETCH loop.

DELETE WHERE CURRENT

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 syntax:



        DELETE FROM TableName

              WHERE CURRENT OF CursorName

The DELETE WHERE CURRENT command syntax 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

             WHERE CURRENT OF ADJUSTQTYONHAND

   END-EXEC.

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 with the WHERE clause to delete a row:



        EXEC SQL DELETE FROM PURCHDB.INVENTORY

                       WHERE PARTNUMBER = :PARTNUMBER

                         AND BINNUMBER  = :BINNUMBER

        END-EXEC.

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.

CLOSE

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



        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 with a COMMIT WORK or a ROLLBACK WORK:



        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 END-EXEC.



        Additional SQL commands are executed, then the transaction is terminated:



   EXEC SQL COMMIT WORK END-EXEC.

When a transaction terminates, any cursors opened during that transaction are automatically closed, unless you ar using the KEEP CURSOR option of the OPEN command. To avoid possible confusion, it is good programming practice to always use the CLOSE command followed by a COMMIT WORK to explicitly close any open cursors before ending a transaction. Refer to the section, "Using KEEP CURSOR," for more information on closing a kept cursor.