How to Execute a Dynamic Select Statement
If you know the data types of the result columns, use the EXECUTE IMMEDIATE statement with the INTO clause. For details, see
When the Result Column Data Types Are Known.
If you do not know the data types of the result columns, the SELECT statement must be prepared and described first, then the program can either:
• Use the EXECUTE IMMEDIATE statement with the USING clause to execute the select.
• Declare a cursor for the prepared SELECT statement and use the cursor to retrieve the results.
The EXECUTE IMMEDIATE option defines a select loop to process the results of the select. Select loops minimize disk and communications I/O but do not allow the program to issue any other OpenSQL statements in the loop. If the program must access the database while processing rows, use the cursor option.
If the program does not know whether the statement is a select, the PREPARE and DESCRIBE statements can be used to determine whether the statement is a select. The following example demonstrates the program logic required to accept OpenSQL statements from a user, execute the statements, and print the results. If the statement is a select, the program uses a cursor to execute the query.
statement_buffer = ' ';
loop while reading statement_buffer from terminal
exec sql prepare s1 from :statement_buffer;
exec sql describe s1 into :result_descriptor;
if (sqlda.sqld = 0) then
exec sql execute s1;
else
/* This is a SELECT */
exec sql declare c1 cursor for s1;
exec sql open c1;
allocate result variables using result_descriptor;
loop while there are more rows in the cursor
exec sql fetch c1 using descriptor
:result_descriptor;
if (sqlca.sqlcode not equal 100) then
print the row using result_descriptor;
end if;
end loop;
free result variables from result_descriptor;
exec sql close c1;
end if;
process sqlca for status;
end loop;
Last modified date: 08/28/2024