5. Working with Embedded SQL : Dynamic Programming : Select Statement with Execute Immediate
 
Share this page                  
Select Statement with Execute Immediate
A dynamic SELECT statement can be executed if the statement has been prepared and described with an EXECUTE IMMEDIATE statement that includes the USING clause. The USING clause tells the DBMS Server to place the values returned by the select into the variables pointed to by the elements of the SQLDA sqlvar array. If the select returns more than one row, a select loop can also be defined to process each row before another is returned.
The syntax of EXECUTE IMMEDIATE in this context is:
EXEC SQL EXECUTE IMMEDIATE select_statement
               USING [DESCRIPTOR] descriptor_name;
[EXEC SQL BEGIN;
               host_code
EXECT SQL END;]
Within a select loop, no SQL statements other than an ENDSELECT can be issued. For non-looped selects, the DBMS Server expects the select to return a single row, and issues an error if more than one row is returned.
To illustrate this option, the following program example contains a dynamic select whose results 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 an error message
          ('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;