HP 3000 Manuals HP 3000 Manuals
Dynamic Operations
Dynamic operations offer a way to execute SQL commands that cannot be
completely defined until run time. You accept part or all of an SQL
command that can be dynamically preprocessed from the user, then use one
of the following techniques to preprocess and execute the command:
* If the dynamic command is not a query, you can use the PREPARE
command to preprocess it, then execute it later during the same
transaction using the EXECUTE command. Alternatively, you can use
the EXECUTE IMMEDIATE command to preprocess and execute the
dynamic command in one step.
* If the dynamic command is a query, you use special data structures
plus the following SQL commands to handle the dynamic command:
PREPARE, DESCRIBE, DECLARE CURSOR, OPEN, FETCH, and CLOSE.
To determine whether a dynamic command is a query, you use the DESCRIBE
command after preparing the dynamic command:
The program accepts an SQL command from the user and stores
it in a host variable named DynamicCommand.
EXEC SQL PREPARE DynamCommand FROM :DynamicCommand;
After the command is prepared, the DESCRIBE command is used
to determine whether the prepared command is a SELECT command.
EXEC SQL DESCRIBE DynamCommand INTO SQLDA;
The SQLDA.SQLD field of the SQLDA is examined to determine
whether the dynamic command is a query.
if SQLDA.SQLD = 0 then (*the command is not a query*)
begin
EXEC SQL EXECUTE DynamCommand;
.
.
.
else if SQLDA.SQLD > 0 then (*the command is a query*)
.
.
.
A non-query can be executed with the EXECUTE
command, but a query requires special handling.
Dynamic queries require special handling because you may not know in
advance what a query result will look like. The number and type of
columns, the existence of null values, the column names...this and other
information may need to be obtained by the program at run time because it
is not known at programming time.
To obtain the information needed to parse any query result at run time,
you use three special data structures in your program: the SQLDA, a
format array, and a data buffer:
* The SQLDA (SQL Description Area) and the format array contain
information about the query result. These data structures have
the format described in the chapter, "Host Variables."
* The data buffer holds one or more rows fetched from the query
result. Its format is also described in the chapter, "Host
Variables."
The following example summarizes how you declare and use these data
structures:
const
OK = 0;
Notfound = 100;
NbrFmtRecords = 255; (*maximum number of columns*)
MaxDataBuff = 1600; (*maximum number of bytes the program*)
(*allows in each fetch; 1600 bytes *)
(*accommodates the display of 20*)
(*80-character lines*)
var
EXEC SQL BEGIN DECLARE SECTION;
DynamicCommand : String[1024] (*maximum length of*)
(*a dynamic command*)
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLDA;
(*declaration of SQLDA*)
SQLFmts : array[1..NbrFmtRecords] of SqlFormat_Type;
(*declaration of format array*)
DataBuffer : packed array[1..MaxDataBuff] of char;
(*declaration of data buffer*)
:
procedure ExecuteDynamCmd;
begin
with SQLDA do (*You must set two SQLDA fields before DESCRIBE*)
begin
Sqln := NbrFmtRecords; (*number of records in format array*)
SqlFmtArr := waddress(SQLFmts); (*address of format array*)
end;
.
. The program accepts a dynamic command.
EXEC SQL PREPARE DYNAMCOMMAND FROM :DYNAMICCOMMAND;
.
. Status checking is done.
EXEC SQL DESCRIBE DYNAMCOMMAND INTO SQLDA;
if SQLDA.SQLD = 0 (*dynamic command is not a query*)
.
. The program executes the dynamic command.
.
else if SQLDA.SQLD > 0 then (*dynamic command is a query*)
begin
EXEC SQL DECLARE DYNAMCURSOR CURSOR FOR DYNAMCOMMAND;
EXEC SQL OPEN DYNAMCURSOR;
.
. If SQLCODE is 0, rows in the query result can be
fetched. First, however, you must set three
SQLDA fields.
with SQLDA do
begin
SqlBufLen := sizeof(DataBuffer); (*bytes in data buffer*)
SqlNRow := SqlBufLen DIV SqlRowLen;
(*number of rows to fetch into data buffer*)
SqlRowBuf := waddress(DataBuffer); (*address of data buffer*)
end;
while SQLCA.SQLCODE = OK do
begin
EXEC SQL FETCH DYNAMCURSOR USING DESCRIPTOR SQLDA;
if SQLCA.SQLCODE <> OK then
begin
if SQLCA.SQLCODE = NotFound then
writeln('No more rows qualify.')
else
SQLStatusCheck;
end;
else
DisplaySelect;
end;
:
procedure DisplaySelect;
This procedure parses the data buffer and displays
rows fetched. For each column in each row of the
query result, various fields in the format array
are used to identify where data values are located
in the data buffer as well as the data type of
these values and null value information.
Techniques for parsing the data buffer and more examples of dynamic
preprocessing are in the chapter, "Using Dynamic Operations."