If a dynamic SQL statement returns a result, you cannot use the EXECUTE statement. Instead, you must declare and use a cursor.
First, declare the cursor using the DECLARE CURSOR statement:
EXEC SQL DECLARE C1 CURSOR FOR dynamic_sql END-EXEC
In the example above, dynamic_sql is the name of a dynamic SQL statement. You must use the PREPARE statement to prepare the dynamic SQL statement before the cursor can be opened, for example:
move "SELECT char_col FROM mfesqltest " & "WHERE int_col = ?" to sql-text EXEC SQL PREPARE dynamic_sql FROM :sql-text END-EXEC
Now, when the OPEN statement is used to open the cursor, the prepared statement is executed:
EXEC SQL OPEN C1 USING :int-col END-EXEC
If the prepared statement uses parameter markers, then the OPEN statement must supply values for those parameters by specifying either host variables or an SQLDA structure.
Once the cursor has been opened, the FETCH statement can be used to retrieve data, for example:
EXEC SQL FETCH C1 INTO :char-col END-EXEC
For a full discussion of the FETCH statement, see the topic Cursors.
Finally, the cursor is closed using the CLOSE statement:
EXEC SQL CLOSE C1 END-EXEC
For a full discussion of the CLOSE statement, see the topic Cursors.