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 as shown in the
following example.
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,
:NUMBEROFROWS
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.