HP 3000 Manuals

ALLBASE/SQL FORTRAN Application Programming Guide

Sequential Table Processing 

In sequential table processing, you process an active set by fetching a
row at a time and optionally deleting or updating it.  Sequential table
processing is useful when the likelihood of row changes throughout a set
of rows is high and when a program user does not need to review multiple
rows to decide whether to change a specific row.

In the following example, rows for parts having the same SalesPrice are
displayed one at a time.  The program user can delete a displayed row or
change its SalesPrice.  Note that the host variable declarations are
identical to those for the simple data manipulation example, since only
one row at a time is fetched.  Rows are fetched as long as SQLCode is
equal to 0:

      SUBROUTINE GetActiveSet

          EXEC SQL INCLUDE SQLCA
          .
          .
          .
          CHARACTER        Response

          OK        =   0
          NotFound  = 100

          EXEC SQL BEGIN DECLARE SECTION
          CHARACTER*16     PartNumber
          CHARACTER*30     PartName
          SQLIND           PartNameInd
          DOUBLE PRECISION SalesPrice
          SQLIND           SalesPriceInd
          EXEC SQL END DECLARE SECTION
          .
          .
          .

       The cursor declared allows the user to change the SalesPrice of 
       the current row.  It can also be used to delete the current row. 

       EXEC SQL DECLARE PriceCursor
     1           CURSOR FOR
     2           SELECT PartNumber, PartName, SalesPrice
     3             FROM PurchDB.Parts
     4            WHERE SalesPrice = :SalesPrice
     5    FOR UPDATE OF SalesPrice
       .
       .     The program accepts a salesprice value from the user. 
       .
       EXEC SQL OPEN PriceCursor

       IF (SQLCode .NE. OK) THEN
         CALL SQLStatusCheck
         CALL ReleaseDBE
       ELSE

         CALL GetRow
       ENDIF
       .
       .
       .
       RETURN
       END
       SUBROUTINE GetRow
       .
       .
       .
       DO WHILE (SQLCode .EQ. OK)
          EXEC SQL FETCH  PriceCursor
     1             INTO :PartNumber,
     2                  :PartName :PartNameInd,
     3                  :SalesPrice :SalesPriceInd

       IF (SQLCode .EQ. OK) THEN
         CALL DisplayRow
       ELSEIF (SQLCode .EQ. NotFound) THEN
         CALL WriteOut ('No More Rows!')
       ELSE
         CALL SQLStatusCheck
       ENDIF
       END DO
       .
       .
       .
       RETURN
       END

      SUBROUTINE DisplayRow

      .  Each row fetched is displayed.  Depending on the user's response 
      .  to a program prompt, the row may be deleted or its SalesPrice 
      .  value changed.

       IF (Response .EQ. 'D') THEN
         EXEC SQL DELETE FROM PurchDB.Parts
     1             WHERE CURRENT OF PriceCursor
         .
         .  Status checking code appears here.
         .
       ELSEIF (response .EQ. 'U') THEN
         .
         .  A new SalesPrice is accepted.
         .
         EXEC SQL UPDATE PurchDB.Parts
     1              SET SalesPrice = :SalesPrice
     2              WHERE CURRENT OF PriceCursor

         .
         .  Status checking code appears here.
         .
       ENDIF
       .
       .
       .
       RETURN
       END

More on sequential table processing can be found in Chapter 8.

MPE/iX 5.0 Documentation