Dynamic SQL Statements and Cursors

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.