 |
» |
|
|
|
The SQL commands used for BULK table processing are:
BULK SELECT
BULK FETCH
BULK INSERT
|
BULK SELECT | |
The BULK SELECT command is useful when the maximum number of
rows in the query result is known at programming time and when the
query result is not too large.
For example, this command might be used in
an application that retrieves a query result containing
a row for each month of the year. The syntax of the BULK SELECT command is:
BULK SELECT SelectList
INTO ArrayName [,StartIndex [,NumberOfRows]]
FROM TableNames
WHERE SearchCondition1
GROUP BY ColumnName
HAVING SearchCondition2
ORDER BY ColumnID
|
Remember, the WHERE, GROUP BY, HAVING, and ORDER BY clauses are
optional.
Note that the order of the select list items must match
the order of the corresponding host variables in the array. In the following example, parts are counted at one of three frequencies or
cycles: 30, 60, or 90 days. The host variable array needs to contain
only three records, since the query result will never exceed three rows.
EXEC SQL BEGIN DECLARE SECTION;
PartsPerCycle : packed array[1..3]
of packed record
CountCycle : SmallInt;
PartCount : integer;
end;
.
.
.
EXEC SQL BULK SELECT CountCycle, COUNT(PartNumber)
INTO :PartsPerCycle
FROM PurchDB.Inventory;
|
The query result is a three row table that describes how many
parts are counted per count cycle.
Multiple query results can be retrieved into the same
host variable array by using StartIndex and
NumberOfRows values and executing a BULK SELECT
command multiple times:
EXEC SQL BEGIN DECLARE SECTION;
PartsPerCycle : packed array[1..15]
of packed record
CountCycle : SmallInt;
PartCount : integer;
end;
StartIndex : SmallInt;
NumberOfRows : SmallInt;
LowBinNumber : packed array [1..16] of char;
HighBinNumber : packed array [1..16] of char;
.
.
.
EXEC SQL END DECLARE SECTION;
LessThanFive : boolean;
.
.
.
procedure DisplayRows;
var
i : integer;
begin
for i = 1 to (StartIndex - 1) do
with PartsPerCycle[i] do
begin
writeln('CountCycle: ', Countcycle);
writeln('PartCount: ', PartCount);
end; (* end for *)
end; (* end of procedure DisplayRows *)
.
.
.
Several variables are initialized:
StartIndex := 1;
NumberOfRows := 3;
LessThanFive := TRUE;
while LessThanFive = TRUE do
|
begin
The user is prompted for a range of bin numbers or a 0. If bin numbers
are entered, they are used in a BETWEEN predicate in the BULK SELECT command.
This WHILE loop can be executed as many as five times, at which time
the array would be filled.
prompt('Enter a low bin number or 0 to STOP> ');
readln(LowBinNumber);
if LowBinNumber <> 0 then
begin
prompt('Enter a high bin number> ');
readln(HighBinNumber);
EXEC SQL BULK SELECT CountCycle, COUNT(PartNumber)
INTO :PartsPerCycle,
:StartIndex,
:NumberOfRows
FROM PurchDB.Inventory
WHERE BinNumber
BETWEEN :LowBinNumber AND :HighBinNumber;
StartIndex := StartIndex + 3;
if StartIndex = 16 then
LessThanFive := FALSE;
end (* if LowBinNumber *)
else
LessThanFive := FALSE;
end; (* while *)
The final StartIndex value can be used to display the final contents of
the host variable array.
if StartIndex > 1 then DisplayRows;
|
The following example illustrates the use of SQLERRD(3) to display
rows stored in the host variable array. It also checks
SQLCODE in conjunction with SQLERRD(3), to determine
whether or not the BULK SELECT executed without error and whether there
may be additional qualified rows for which there was not room in the
array. In each case, an appropriate message is displayed.
procedure DisplayRows;
var
i : integer;
begin
for i := 1 to SQLCA.SQLERRD[3] do
with OrdersArray[i] do
begin
writeln ('OrderNumber: ', OrderNumber);
writeln ('VendorNumber: ', VendorNumber);
end;
end; (* end of procedure DisplayRows *)
.
.
.
The variable MaximumRows is set to the number of records in the host variable array.
MaximumRows := 25;
.
.
.
EXEC SQL BULK SELECT OrderNumber, VendorNumber
INTO :OrdersArray
FROM PurchDB.Orders;
case SQLCA.SQLCODE of
0 : begin
if SQLCA.SQLERRD[3] = MaximumRows then
begin
write('There may be additional rows ');
writeln('that cannot be displayed.');
DisplayRows;
end;
100 : writeln('No rows were found.');
otherwise begin
if SQLCA.SQLERRD[3] > 0 then
begin
write('The following rows were retrieved ');
writeln('before an error occurred:');
DisplayRows;
end;
SqlStatusCheck;
end;
end;
|
BULK FETCH | |
The BULK FETCH command is useful for reporting applications that
operate on large query results whose maximim size is unknown at
programming time. The syntax of the BULK FETCH command is:
BULK FETCH CursorName
INTO ArrayName [,StartIndex [,NumberOfRows]]
|
You use this command in conjunction with the following cursor commands:
DECLARE CURSOR: defines a cursor and associates with it a
query. The cursor declaration should not contain a FOR UPDATE clause,
however, because the BULK FETCH command is designed to be
used for active set retrieval only. The order of the select
list items in the embedded SELECT command must match the order
of the corresponding host variables in the host variable array.
OPEN: defines the active set.
BULK FETCH: delivers rows into the host variable
array and advances the cursor to the last row delivered.
If a single execution of this command does not retrieve
the entire active set, you re-execute it to retrieve subsequent
rows in the active set.
CLOSE: releases ALLBASE/SQL internal buffers used to handle cursor
operations.
To retrieve all the rows in an active set larger than the
host variable array, you can test for a value of 100 in
SQLCODE to determine when you have fetched the last
row in the active set:
EXEC SQL BEGIN DECLARE SECTION;
.
.
.
SupplierBuffer : packed array[1..20]
of packed record
PartNumber : packed array[1..16] of char;
VendorName : packed array[1..30] of char;
DeliveryDays : SmallInt;
DeliveryDaysInd : SqlInd;
end;
EXEC SQL END DECLARE SECTION;
DoFetch : boolean;
Response : packed array[1..3] of char;
.
.
.
procedure DisplayRows;
var
i : integer;
begin
for i := 1 to SQLCA.SQLERRD[3] do
with SupplierBuffer[i] do
begin
The values in each row returned by the BULK FETCH command are displayed here.
end;
|
if SQLCA.SQLCODE = 0 then
begin
prompt('Do you want to see additional rows? (YES/NO)> ');
readln('Response');
if Response[1] in [N','n'] then DoFetch := FALSE;
end;
end; (* end of DisplayRows procedure *)
.
.
.
EXEC SQL DECLARE SupplierInfo
CURSOR FOR
SELECT PartNumber,
VendorName,
DeliveryDays
FROM PurchDB.Vendors,
PurchDB.SupplyPrice
WHERE PurchDB.Vendors.VendorNumber =
PurchDB.SupplyPrice.Vendornumber
ORDER BY PartNumber;
EXEC SQL OPEN SupplierInfo;
DoFetch = TRUE;
while DoFetch = TRUE do
begin
EXEC SQL BULK FETCH SupplierInfo
INTO SupplierBuffer;
case SQLCA.SQLCODE of
0 : DisplayRows;
100 : begin
writeln('No rows were found');
DoFetch := FALSE;
end;
otherwise begin
DisplayRows;
SqlStatusCheck;
DoFetch := FALSE;
end;
end; (* end case *)
EXEC SQL CLOSE SupplierInfo;
|
Each time the BULK FETCH command is executed, the CURRENT row is the
last row put by ALLBASE/SQL into the host variable array.
When the last row in the active set has been fetched, ALLBASE/SQL
sets SQLCODE to 100 the next time the BULK FETCH
command is executed.
BULK INSERT | |
The BULK INSERT command is useful for multiple-row insert operations.
The syntax of the BULK INSERT command is:
BULK INSERT INTO TableName
(ColumnNames)
VALUES (ArrayName [,StartIndex [,NumberOfRows]]
|
As in the case of the simple INSERT command
you can omit ColumnNames when you provide values for all columns
in the target table. ALLBASE/SQL attempts to assign a null value to any
unnamed column. In the following example, a user is prompted
for multiple rows. When the host
variable array is full and/or when the user is finished specifying values,
the BULK INSERT command is executed:
EXEC SQL BEGIN DECLARE SECTION;
.
.
.
NewParts : packed array[1..20]
of packed record
PartNumber : packed array[1..16] of char;
PartName : packed array[1..30] of char;
PartNameInd : SqlInd;
SalesPrice : longreal;
SalesPriceInd : SqlInd;
end;
StartIndex : SmallInt;
NumberOfRows : SmallInt;
EXEC SQL END DECLARE SECTION;
DoneEntry : boolean;
Response : packed array[1..4] of char;
.
.
.
procedure BulkInsert;
EXEC SQL BULK INSERT INTO PurchDB.Parts
(PartNumber,
PartName,
SalesPrice)
VALUES (:NewParts,
:StartIndex,
:NumberOfRows);
.
.
.
end; (* end of procedure BulkInsert *)
procedure PartEntry;
The user is prompted for three column values, and the values are assigned to the
appropriate record in the host variable array; then the array row counter
(NumberOfRows)is incremented and the user asked whether the user wants to specify
another line item.
NumberOfRows := NumberOfRows + 1;
prompt('Do you want to specify another line item (Y/N)?> ');
readln(Response);
if Response[1] in ['N','n'] then
begin
DoneEntry := TRUE;
BulkInsert;
end
else
begin
if NumberOfRows = 20 then
begin
BulkInsert;
NumberOfRows := 0;
end
else
BulkInsert;
end; (* end else *)
end; (* end of procedure PartEntry *)
.
.
.
StartIndex := 1;
NumberOfRows := 0;
DoneEntry := FALSE;
repeat PartEntry until DoneEntry;
|
|
