HP 3000 Manuals HP 3000 Manuals
Preprocessing Dynamic Commands That May or May Not Be Queries
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;