HP 3000 Manuals HP 3000 Manuals
Bulk Table Processing
BULK table processing offers a way to retrieve or insert multiple rows
with the execution of a single SQL command. Three commands can be used
in this fashion:
* You can use the BULK SELECT command when you know in advance the
maximum number of rows in a multiple-row query result, as when the
query result will contain a row for each month of the year or day
of the week. This command minimizes the time a table is locked
for the retrieval operation, because the program can execute the
BULK SELECT command, then immediately terminate the transaction,
even before displaying any rows.
* You can use the BULK FETCH command to handle multiple-row query
results of unpredictable maximum length. This use of a cursor is
most suitable for display only applications, such as programs that
let a user browse through a query result, so many rows at a time.
* You can use the BULK INSERT command to insert multiple rows into a
table. Like the BULK SELECT command, this command is efficient
for concurrency, because any exclusive lock acquired to insert
rows need be held only until the BULK INSERT command is executed.
In each of these three commands, the host variables that hold rows are in
an array, as illustrated in the following example. The example shows how
you can use a cursor to retrieve and display ten rows at a time from the
active set. The host variable named StartIndex is set to 1 so that the
first row in each group of rows fetched is stored in the first element of
the PartsTable array. The host variable named NumberOfRows controls the
maximum number of rows returned with each execution of the BULK FETCH
command. StartIndex and NumberOfRows are set before the first BULK FETCH
is executed.
const
OK = 0;
NotFound = 100;
MaximumRows = 10;
var
EXEC SQL BEGIN DECLARE SECTION;
PartsTable : packed array[1..10] of
packed record
PartNumber : packed array[1..16] of char;
PartName : packed array[1..30] of char;
PartNameInd : sqlInd;
StartIndex : integer;
NumberOfRows : integer;
EXEC SQL END DECLARE SECTION;
:
procedure DeclareCursor;
begin
EXEC SQL DECLARE PartsCursor
CURSOR FOR
SELECT PartNumber, PartName
FROM PurchDB.Parts;
:
function OpenCursor: boolean;
begin
EXEC SQL OPEN PartsCursor;
if SQLCA.SQLCODE <> OK then
begin
OpenCursor := FALSE;
SqlStatusCheck;
ReleaseDBE;
end
else
OpenCursor := TRUE;
end;
:
procedure GetRows;
begin
if OpenCursor then
begin
StartIndex := 1;
NumberOfRows := MaximumRows;
while SQLCA.SQLCODE = OK do
begin
EXEC SQL BULK FETCH PartsCursor
INTO :PartsTable,
:StartIndex,
:NumberOfRows;
As many as ten rows are put into the PartsTable
array. If the FETCH command executes without
error the value in SQLERRD(3) indicates the
number of rows returned to PartsTable.
case SQLCA.SQLCODE of
OK : for i := 1 to sqlca.sqlerrd(3) do
writeln(Partnumber,'|',PartName);
NotFound : writeln ('No more rows qualify!');
otherwise : SqlStatusCheck;
end; (*case*)
end; (*do*)
end; (*if*)
BULK table processing is discussed in additional detail in the chapter,
"Bulk Table Processing."