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.
