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