Introducing the Cursor

You use a cursor to manage a query result that may contain more than one row when you want to make all the qualifying rows available to the program user. Cursors are used in sequential table processing as shown later in this chapter.

Like the cursor on a terminal screen, an ALLBASE/SQL cursor is a position indicator. It does not, however, point to a column. Rather, it points to one row in an active set. An active set is a query result obtained when a SELECT command associated with a cursor (defined in a DECLARE CURSOR command) is executed (using the OPEN command).

Each cursor used in a program must be declared before it is used. You use the DECLARE CURSOR command to declare a cursor. The DECLARE CURSOR command names the cursor and associates it with a particular SELECT command:

    EXEC SQL DECLARE Cursor1
   1           CURSOR FOR
   2           SELECT PartName,
   3                  SalesPrice
   4             FROM PurchDB.Parts
   5            WHERE PartNumber BETWEEN :LowValue AND :HighValue
   6         ORDER BY PartName

All cursor names within one program must be unique. You use a cursor name when you perform data manipulation operations using the cursor.

The SELECT command in the cursor declaration does not specify any output host variables. The SELECT command can, however, contain input host variables, as in the WHERE clause of the cursor declaration above.

Rows in the active set are returned to output host variables when the FETCH command is executed:

   EXEC SQL OPEN Cursor1
   .
   .     The OPEN command examines any input host
   .     variables and determines the active set.
        .
   EXEC SQL FETCH Cursor1 INTO OutputHostVariables
   
        .       The FETCH command delivers one row of
        .       the active set into output host variables.
        .
        .

If a serial scan will be used to retrieve the active set, ALLBASE/SQL locks the table(s) when the OPEN command is executed. If an index scan will be used, locks are placed when rows are fetched. Any locks obtained are held until the transaction terminates or the CLOSE command is executed.

Both the OPEN and the FETCH commands position the cursor:

The row at which the cursor points at any one time is called the current row. When a row is a current row, you can delete it as follows:

    EXEC SQL DELETE FROM PurchDB.Parts
   1                WHERE CURRENT OF Cursor1

When you delete the current row, the cursor remains between the row deleted and the next row in the active set until you execute the FETCH command again:

    EXEC SQL FETCH  Cursor1
   1           INTO :PartName :PartNameInd,
   2                :SalesPrice :SalesPriceInd

When a row is a current row you can update it if the cursor declaration contains a FOR UPDATE OF clause naming the column(s) you want to change. The following cursor, for example, can be used to update the SalesPrice column of the current row by using the WHERE CURRENT OF option in the UPDATE command:

    EXEC SQL DECLARE Cursor2
   1           CURSOR FOR
   2           SELECT PartName, SalesPrice
   3             FROM PurchDB.Parts
   4            WHERE PartNumber BETWEEN :LowValue AND :HighValue
   5       FOR UPDATE OF SalesPrice
    .
    .     Because the DECLARE CURSOR command is not
    .     executed at runtime, no status checking code
    .     needs to appear here.
    .
    EXEC SQL OPEN Cursor2
    .
    .     The OPEN command examines any input host
    .     variables and determines the active set.
    .     Then the program fetches one row at a time.
    .
    EXEC SQL FETCH  Cursor2
   1           INTO :PartName :PartNameInd,
   2                :SalesPrice :SalesPriceInd
    .
    .     If the program user wants to change the SalesPrice
    .     of the row displayed (the current row), the UPDATE
    .     command is executed.  The new SalesPrice entered by
    .     the user is stored in an input host variable named
    .     NewSalesPrice.
          .
    EXEC SQL UPDATE PurchDB.Parts
   1             SET SalesPrice = :NewSalesPrice
   2           WHERE CURRENT OF Cursor2
   
    .     After the UPDATE command is executed, the updated
    .     row remains the current row until the FETCH command
    .     is executed again.
    .

The restrictions that govern deletions and updates using a view also govern deletions and updates using a cursor. You cannot delete or update a row using a cursor if the cursor declaration contains any of the following:

After the last row in the active set has been fetched, the cursor is positioned after the last row fetched and the value in SQLCode is equal to 100. Therefore to retrieve all rows in the active set, you execute the FETCH command until SQLCode is not 0:

    DO WHILE (SQLCode .EQ. 0)
     EXEC SQL FETCH  Cursor3
   1           INTO :PartNumber,
   2                :PartName :PartNameInd,
   3                :SalesPrice :SalesPriceInd
   
     IF (SQLCode .EQ. 0) THEN
       CALL DisplayRow
     ELSEIF (SQLCA.SQLCode .EQ.100) THEN
       CALL WriteOut ('Row Not Found or No More Rows!')
      ELSE
       CALL SQLStatusCheck
     ENDIF
    END DO

When you are finished operating on an active set, you use the CLOSE command:

   EXEC SQL CLOSE Cursor3

When you close a cursor, the active set becomes undefined and you cannot use the cursor again unless you issue an OPEN command to reopen it. The COMMIT WORK and ROLLBACK WORK commands also close any open cursors, automatically. Figure 6-2 summarizes the effect of the cursor-related commands on the position of the cursor and on the active set.

Figure 6-2 Effect of SQL Commands on Cursor and Active Sets