HP 3000 Manuals

ALLBASE/SQL COBOL 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 COLUMN2-NAME is
COLUMN2-IND-VAR:

     01  ARRAY-NAME.
       05  ROW-NAME OCCURS n TIMES.
         10  COLUMN1-NAME       Valid data clause.
         10  COLUMN2-NAME       Valid data clause.
         10  COLUMN2-IND-VAR    SQLIND.
         .
         .
         .
         10  COLUMNn-NAME       Valid data clause.

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

     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
     01  PARTSARRAY.
       05  EACH-ROW OCCURS 25 TIMES.
         10  PARTNUMBER         PIC X(16).
         10  PARTNAME           PIC X(30).
         10  PARTNAMEIND        SQLIND.
     01  SALESPRICE             PIC S9(8)V99 COMP-3.
     .
     .
     .
     EXEC SQL BULK SELECT  PARTNUMBER, PARTNAME
                     INTO :PARTSARRAY
                     FROM  PURCHDB.PARTS
                    WHERE  SALESPRICE < :SALESPRICE
     END-EXEC.

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 END-EXEC.
     01  PARTSARRAY.
       05  EACH-ROW OCCURS 25 TIMES.
         10  PARTNUMBER         PIC X(16).
         10  PARTNAME           PIC X(30).
         10  PARTNAMEIND        SQLIND.
     01  SALESPRICE             PIC S9(8)V99 COMP-3.
     01  STARTINDEX             PIC S9(4) COMP.
     01  NUMBEROFROWS           PIC S9(4) COMP.
     EXEC SQL END DECLARE SECTION END-EXEC.
     .
     .
     EXEC SQL BULK SELECT  PARTNUMBER, PARTNAME
                     INTO :PARTSARRAY,
                          :STARTINDEX,
                          :NUMBEROFROWSi
                     FROM  PURCHDB.PARTS
                    WHERE  SALESPRICE < :SALESPRICE
     END-EXEC.

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

MPE/iX 5.0 Documentation