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
function NonQuery() and
use the EXECUTE or EXECUTE IMMEDIATE command to execute it. If it is a
query, you branch to function 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;
if (sqlda.sqld == 0) {
Nonquery();
}
else if (sqlda.sqld > 0) {
Query();
}
|
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, 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:
EXEC SQL BEGIN DECLARE SECTION;
char DynamicCommand[2048];
EXEC SQL END DECLARE SECTION;
char DynamicClause[80];
short int Pos;
.
.
<newpage>
.
.
printf("\n Enter your SQL command or clause ");
printf("\n");
DynamicCommand[0] = '\0';
do {
printf("\n > ");
getline(DynamicClause);
if (DynamicClause[0] != '/') {
strcat(DynamicCommand," ");
strcat(DynamicCommand,DynamicClause);
i = 0;
while (DynamicClause[i] != '\0' & DynamicClause[i++] !=';');
if (DynamicClause[i-1] == ';') {
DynamicClause[0] = '/';
DynamicClause[1] = '\0';
}
}
else {
DynamicCommand[0] = '/';
DynamicCommand[1] = '\0';
}
} while (DynamicClause[0] != '/');
.
.
EXEC SQL PREPARE SQLCommand FROM :DynamicCommand;
|
