HP 3000 Manuals

ALLBASE/SQL Pascal 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 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."

MPE/iX 5.0 Documentation