HP 3000 Manuals

ALLBASE/SQL Pascal Application Programming Guide

Variables Used in BULK Processing 

Rows are retrieved into or inserted from host variables declared as an
array of records.  Any column that may contain a null value must have an
indicator variable immediately following the declaration for the column
in the array.  For example, the indicator variable for Column2Name is
Column2IndVar, as shown in the following syntax:

     ArrayName               : packed or unpacked array [1..n]
     of packed record
       Column1Name           : Valid data type;
       Column2Name           : Valid data type;
        Col2IndVar            : SqlInd; 
     :
       ColumnName            : Valid data type;
     end;

You reference the name of the array in the BULK SQL command:

     EXEC SQL BEGIN DECLARE SECTION;
      PartsArray          : packed array[1..25] 
      of packed record
        PartNumber       : packed array [1..16] of char;
        PartName         : packed array [1..30] of char;
        PartNameInd      : SqlInd;
      end;
     SalesPrice          : longreal;
     :
     EXEC SQL BULK SELECT  PartNumber, PartName
                     INTO  :PartsArray 
                     FROM  PurchDB.Parts
                    WHERE  SalesPrice < :SalesPrice;

Two additional host variables may be specified in conjunction with the
array: 

   *   A StartIndex variable:  a SMALLINT or INTEGER variable that
       specifies an array subscript.  The subscript identifies where in
       the array ALLBASE/SQL should store the first row in a group of
       rows retrieved.  In the case of an INSERT operation, the subscript
       identifies where in the array the first row to be inserted is
       stored.  If not specified, the assumed subscript is one.

   *   A NumberOfRows variable:  a SMALLINT or INTEGER variable that
       indicates to ALLBASE/SQL how many rows to transfer into or take
       from the array, starting at the array record designated by
       StartIndex.  If not specified for an INSERT operation, the assumed
       number of rows is the number of records in the array from the
       StartIndex to the end of the array.  If not specified for a SELECT
       operation, the assumed number of rows is the smaller of two
       values:  the number of records in the array or the number of rows
       in the query result.  NumberOfRows can be specified only if you
       specify the StartIndex variable.

In the BULK SELECT example shown earlier, these two variables would be
declared and referenced as follows:

     EXEC SQL BEGIN DECLARE SECTION;
     PartsArray          : packed array[1..25]
      of packed record
        PartNumber       : packed array [1..16] of char;
        PartName         : packed array [1..30] of char;
        PartNameInd      : SqlInd;
      end;
     SalesPrice          : longreal;
      StartIndex        : SmallInt; 
      NumberOfRows      : SmallInt; 
     :
     EXEC SQL BULK SELECT  PartNumber, PartName
                     INTO :PartArray,
                           :StartIndex, 
                           :NumberOfRows 
                     FROM  PurchDB.Parts
                    WHERE  SalesPrice < :SalesPrice;

Note that StartIndex and NumberOfRows must be referenced in that order
and immediately following the array reference.

MPE/iX 5.0 Documentation