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. |
|
| |
|
