HP 3000 Manuals HP 3000 Manuals
Preprocessing of 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
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;