You need special techniques to handle dynamic commands which may be
either queries or non-queries. In a program that accepts both query and non-query SQL commands, you
first PREPARE the command, then use the DESCRIBE command in conjunction with the SQLDA, the
data structure that lets you identify whether a command is a query. The PREPARE command must appear physically in your
source program before the EXECUTE or DECLARE CURSOR command that
uses the name you assign to the dynamic command in the PREPARE command.
The sqld field of the SQLDA is set to 0 if the dynamic command is not a query
and to a positive integer if it is a query. The SQLDA data structure is used
in any program that may host a dynamic query.
In the following example, if the command is not a query, you branch to
NonQuery and use the EXECUTE or EXECUTE IMMEDIATE command to execute it. If it is a
query, you branch to Query, where you declare a cursor, open it, then use FETCH to retrieve qualifying rows.
EXEC SQL PREPARE ThisCommand FROM :DynamicCommand;
EXEC SQL DESCRIBE ThisCommand INTO SQLDA;
The SQLDA.SQLD field of the SQLDA is set to 0 if the dynamic command
is not a query and to a positive integer if it is a query. The
SQLDA is a special data structure used in any program that may host
a dynamic query. The data structure is fully defined in this
section under "Setting Up the SQLDA."
if SQLDA.SQLD = 0 then NonQuery;
The command is not a query and the EXECUTE or
EXECUTE IMMEDIATE command is used to execute it.
else if SQLDA.SQLD > 0 then Query;
The command is a query and a cursor is used
to retrieve qualifying rows.
|
To handle a command entirely unknown at programming time, you
accept the command into the host variable. In the
following example, an SQL command is accepted into a host variable
named DynamicCommand, which is declared large enough to accommodate
the largest expected dynamic command. User input is accepted into
DynamicClause and concatenated in DynamicCommand until
the user enters a semicolon.
var
EXEC SQL BEGIN DECLARE SECTION;
DynamicCommand := String[1024];
EXEC SQL END DECLARE SECTION;
DynamicClause := String[80];
Pos := SmallInt;
.
.
writeln ('Enter an SQL command or clause > ');
writeln;
DynamicCommand := '';
repeat
prompt('> ');
readln(DynamicClause);
if DynamicClause <> '/' then
begin
DynamicCommand := DynamicCommand + ' ' + DynamicClause;
Pos := StrPos(DynamicClause, ';');
if Pos <> 0 then DynamicClause := '/';
end
else
DynamicCommand := '/';
until DynamicClause = '/';
.
.
EXEC SQL PREPARE SQLCommand FROM :DynamicCommand;
|
