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.

Variables Used in BULK Processing

Technical documentation

» Table of Contents

» Index