HP 3000 Manuals HP 3000 Manuals
SQL Bulk Commands
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 form 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;
struct {
short int CountCycle;
int PartCount;
} PartsPerCycle[3];
EXEC SQL END DECLARE SECTION;
.
.
.
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;
struct {
short int CountCycle;
int PartCount;
} PartsPerCycle[15];
short int StartIndex;
short int NumberOfRows;
char LowBinNumber[16];
char HighBinNumber[16];
EXEC SQL END DECLARE SECTION;
.
.
.
int DisplayRows()
{
int i;
for (i = 0; i < StartIndex; i++) {
printf("CountCycle: %d\n", PartsPerCycle[i].CountCycle);
printf("PartCount: %d\n", PartsPerCycle[i].PartCount);
} /* end for */
} /* end of procedure DisplayRows */
.
.
.
main()
{
#define TRUE 1
#define FALSE 0
typedef int boolean;
int StartIndex;
int NumberOfRows;
boolean LessThanFive;
/* Initialize variables. */
StartIndex = 1;
NumberOfRows = 3;
LessThanFive = TRUE;
do {
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.
printf("\n Enter a low bin number or 0 to STOP > ");
scanf("%d",LowBinNumber);
if (LowBinNumber != 0) {
printf("\n Enter a high bin number > ");
scanf("%d\n",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 == 15) LessThanFive = FALSE;
} /* if LowBinNumber */
else
LessThanFive = FALSE;
} while (LessThanFive == TRUE);
The final StartIndex value is used to display the final contents of
the host variable array.
if (StartIndex > 0) {
DisplayRows();
}
}
The following example illustrates the use of sqlca.sqlerrd[2] to display
rows stored in the host variable array. It also checks sqlca.sqlcode in
conjunction with sqlca.sqlerrd[2], 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.
int DisplayRows()
{
int i;
for (i = 0; i < sqlca.sqlerrd[2]; i++) {
printf("OrderNumber: %s\n", OrdersArray[i].OrderNumber);
printf("VendorNumber: %s\n", OrdersArray[i].VendorNumber);
}
} (* end of procedure DisplayRows *)
.
.
.
The variable MaximumRows is set to the number of records in the host
variable array.
Main()
{
.
.
.
MaximumRows = 25;
.
.
.
EXEC SQL BULK SELECT OrderNumber, VendorNumber
INTO :OrdersArray
FROM PurchDB.Orders;
switch (sqlca.sqlcode) {
case 0: if (sqlca.sqlerrd[2] == MaximumRows) {
printf("\n There may be additional rows ");
printf("\n that cannot be displayed.");
}
DisplayRows();
break;
case 100: printf("\n No rows were found!");
break;
default: if (sqlca.sqlerrd[2] > 0) {
printf("\n The following rows were retrieved ");
printf("\n before an error occurred:");
}
DisplayRows();
SQLStatusCheck();
break;
} /* End switch */
.
.
.
}
BULK FETCH
The BULK FETCH command is useful for reporting applications that operate
on large query results or query results whose maximum size is unknown at
programming time.
The form 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
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: opens the cursor.
* 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 sqlca.sqlcode to determine when
you have fetched the last row in the active set:
.
.
int DisplayRows()
{
int i;
for (i = 0; i < sqlca.sqlerrd[2]; i++) {
The values in each row returned by the BULK FETCH command are
displayed here.
}
if (sqlca.sqlcode != 0) {
printf("\n Do you want to see additional rows? (yes/no) > ");
scanf("%s", Response);
if ((Response[0] == 'N') || (Response[0] == 'n')) {
DoFetch = FALSE;
}
} (* end of DisplayRows procedure *)
.
.
main()
{
EXEC SQL BEGIN DECLARE SECTION;
struct {
char PartNumber[17];
char VendorName[31];
short int DeliveryDays;
sqlind DeliveryDaysInd;
} SupplierBuffer[20];
EXEC SQL END DECLARE SECTION;
typedef int boolean;
boolean DoFetch;
char Response[2];
#define TRUE 1
#define FALSE 0
.
.
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;
do {
EXEC SQL BULK FETCH SupplierInfo
INTO :SupplierBuffer;
switch (sqlca.sqlcode) {
case 0: DisplayRows();
break;
case 100: printf("\n No rows were found!");
DoFetch = FALSE;
break;
default: DisplayRows();
SQLStatusCheck();
DoFetch = FALSE;
break;
} /* End switch */
} while (DoFetch != TRUE);
EXEC SQL CLOSE SupplierInfo;
After the BULK FETCH command is executed, the last row ALLBASE/SQL put
into the host variable array is the current row. If the BULK FETCH
command is re-executed, the first row in the next set of rows fetched is
the row following the current row, and the last row fetched becomes the
current row. When the last row in the active set has been fetched,
ALLBASE/SQL sets sqlca.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 form 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;
struct {
char PartNumber[17];
char PartName[31];
sqlind PartNameInd;
double SalesPrice;
sqlind SalesPriceInd;
} NewParts[20];
short int StartIndex;
short int NumberOfRows;
EXEC SQL END DECLARE SECTION;
typedef int boolean;
boolean DoneEntry;
char Response[2];
#define TRUE 1
#define FALSE 0
StartIndex = 1;
NumberOfRows = 0;
DoneEntry = FALSE;
do {
PartEntry();
} while (DoneEntry != TRUE);
.
.
int BulkInsert()
{
EXEC SQL BULK INSERT INTO PurchDB.Parts
(PartNumber,
PartName,
SalesPrice)
VALUES (:NewParts,
:StartIndex,
:NumberOfRows);
.
.
} /* End of function BulkInsert */
int 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 s/he wants to specify another line item.
.
.
NumberOfRows = NumberOfRows + 1;
printf("\n Do you want to specify another line item (Y/N)? > ");
scanf("%s",Response);
if ((Response[0] == 'N') || (Response[0] == 'n')) {
DoneEntry = TRUE;
BulkInsert();
}
else {
if (NumberOfRows == 20) {
BulkInsert();
NumberOfRows = 0;
}
} /* End else */
.
.
} /* End of PartEntry Function */