Executing the Select with Execute Immediate
You can execute a dynamic SELECT statement that has been prepared and described with an EXECUTE IMMEDIATE statement that includes the USING clause. The USING clause tells OpenSQL to place the values returned by the select into the variables pointed to by the elements of the SQLDA sqlvar array. If the select will return more than one row, you can also define a select loop to process each row before another is returned.
The syntax of EXECUTE IMMEDIATE in this context is as follows:
EXEC SQL EXECUTE IMMEDIATE select_statement
USING [DESCRIPTOR] descriptor_name;
[EXEC SQL BEGIN;
host_code
EXEC SQL END;]
Within a select loop, no OpenSQL statements other than an ENDSELECT can be issued. For selects without select loops, OpenSQL issues an error if more than one row is returned.
To illustrate this option, the following example contains a dynamic SELECT. The results of the SELECT statement are used to generate a report.
...
allocate an sqlda
read the dynamic select from the terminal into a
stmt_buffer
exec sql prepare s1 from :stmt_buffer;
exec sql describe s1 into :sqlda;
if (sqlca.sqlcode < 0) or (sqlda.sqld = 0) then
print ('Error or statement is not a select');
return;
else if (sqlda.sqld > sqlda.sqln) then
allocate a new sqlda;
exec sql describe s1 into :sqlda;
endif;
analyze the results and allocate variables
exec sql execute immediate :stmt_buffer
using descriptor :sqlda;
exec sql begin;
process results, generating report
if error occurs, then
exec sql endselect;
endif
exec sql end;
...