HP 3000 Manuals

ALLBASE/SQL C 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:

     struct StructName {
          data type          Column1Name;
          data type          Column2Name;
         sqlind               Col2IndVar;
         .
         .
         .
          data type          ColumnnName;
         sqlind               ColnIndVar;
         } ArrayName[n];

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

     EXEC SQL BEGIN DECLARE SECTION;
     struct {
        char             PartNumber[17];
        char             PartName[31];
        sqlind           PartNameInd;
        }  PartsArray[26]; 
     double              SalesPrice;
     EXEC SQL END DECLARE SECTION;
     .
     .
     .
     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 zero. 

   *   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, the default number of rows is the
       number of records in the array from the StartIndex to the end of
       the array for an INSERT operation.  For a retrieval operation, the
       default number of rows is the smaller of two values; 1) the number
       of records in the array from the StartIndex to the end of the
       array, or 2) 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;
     struct {
        char             PartNumber[17];
        char             PartName[31];
        sqlind           PartNameInd;
        } PartsArray[26];
      short int           StartIndex;
      short int           NumberOfRows;
     double              SalesPrice;
     EXEC SQL END DECLARE SECTION;
     .
     .
     .
     EXEC SQL BULK SELECT  PartNumber, PartName
                     INTO :PartsArray,
                           :StartIndex, 
                           :NumberOfRows 
                     FROM  PurchDB.Parts
                    WHERE  SalesPrice < :SalesPrice;


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

MPE/iX 5.0 Documentation