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 0, as shown in the following example:
const
OK = 0;
NotFound = 100;
var
EXEC SQL BEGIN DECLARE SECTION;
PartNumber : packed array[1..16] of char;
PartName : packed array[1..30] of char;
PartNameInd : sqlInd;
SalesPrice : longreal;
SalesPriceInd : sqlInd;
EXEC SQL END DECLARE SECTION;
.
.
.
procedure GetActiveSet;
begin
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
CURSOR FOR
SELECT PartNumber, PartName, SalesPrice
FROM PurchDB.Parts
WHERE SalesPrice = :SalesPrice
FOR UPDATE OF SalesPrice;
.
. The program accepts a salesprice value from the user.
.
EXEC SQL OPEN PriceCursor;
if SQLCA.SQLCODE <> OK then
begin
SqlStatusCheck;
ReleaseDBE;
end
else
GetRow;
end;
procedure GetRow;
begin
while SQLCA.SQLCODE = OK do
begin
EXEC SQL FETCH PriceCursor
INTO :PartNumber,
:PartName :PartNameInd,
:SalesPrice :SalesPriceInd;
case SQLCA.SQLCODE of
OK : DisplayRow
NotFound : writeln('No more rows!');
otherwise : SqlStatusCheck;
end;
end;
procedure DisplayRow;
begin
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[1] = 'D' then
begin
EXEC SQL DELETE FROM PurchDB.Parts
WHERE CURRENT OF PriceCursor;
.
. Status checking code appears here.
.
end;
if response[1] = 'U' then
begin
.
. A new SalesPrice is accepted.
.
EXEC SQL UPDATE PurchDB.Parts
SET SalesPrice = :SalesPrice
WHERE CURRENT OF PriceCursor;
.
. Status checking code appears here.
.
end;
|
Sequential table processing is discussed in more detail in the chapter,
"Processing with Cursors."
