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.
