Execute a Dynamic Select Statement
To execute a dynamic SELECT statement, use one of the following methods:
• If your program knows the data types of the SELECT statement result columns, use the EXECUTE IMMEDIATE statement with the INTO clause to execute the select. EXECUTE IMMEDIATE defines a select loop to process the retrieved rows.
• If your program does not know the data types of the SELECT statement result columns, use the EXECUTE IMMEDIATE statement with the USING clause to execute the select.
• If your program does not know the data types of the SELECT statement result columns, declare a cursor for the prepared SELECT statement and use the cursor to retrieve the results.
The EXECUTE IMMEDIATE option allows you to define a select loop to process the results of the select. Select loops do not allow the program to issue any other SQL statements while the loop is open. If the program must access the database while processing rows, use the cursor option.
Details about these options are found in
When Result Column Data Types Are Known (see page
Unknown Result Column Data Types) and
When Result Column Data Types Are Unknown (see page
How Unknown Result Column Data Types are Handled).
To determine whether a statement is a select, use the PREPARE and DESCRIBE statements. A REPEATED SELECT statement can be prepared only if it is associated with a cursor.
The following code demonstrates the use of the PREPARE and DESCRIBE statements to execute random statements and print results. This example uses cursors to retrieve rows if the statement is a select.
statement_buffer = ' ';
loop while reading statement_buffer from terminal
exec sql prepare s1 from :statement_buffer;
exec sql describe s1 into :rdescriptor;
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
:rdescriptor;
if (sqlca.sqlcode not equal 100) then
print the row using
rdescriptor;
end if;
end loop;
free result variables from rdescriptor;
exec sql close c1;
end if;
process sqlca for status;
end loop;