Getting Started › Dynamic SQL › Using Dynamic SQL in Application Programs › Dynamic SQL for Fixed-List SELECT Statements

Dynamic SQL for Fixed-List SELECT Statements

In the following example, assume that you know the number and data types of the columns in the SELECT's result table when you code the application program.

To dynamically execute a fixed-list SELECT statement your program must:

1. Load input SQL statement into a data area (as in non-SELECT statement example in Dynamic SQL for Non-SELECT Statements).
2. DECLARE a cursor-name for the statement name.

       EXEC SQL DECLARE CURSOR1 CURSOR FOR STMT;
   

3. Read or construct an SQL select-statement (of the form SELECT NAME, ZIP FROM CUSTOMERS WHERE...) into host variable USERSTR, then use a PREPARE statement and an OPEN statement as shown.

       EXEC SQL PREPARE STMT FROM :USERSTR;
   
       EXEC SQL OPEN CURSOR1;
   

   Alternately, if there were always two parameter markers in the statement:

       EXEC SQL OPEN CURSOR1 USING :PARA1, :PARA2;
   

   Or, to be more flexible, the input host variables could be described by an SQLDA, as in:

       EXEC SQL OPEN CURSOR1 USING DESCRIPTOR :SQLDA-PARAS;
   

   The application program in this case is required to ensure that the number of host variables described in the SQLDA matches the number of parameter markers in the SQL statement.

4. FETCH rows from result table.

       EXEC SQL FETCH CURSOR1 INTO :NAME, :PHONE;
   

5. CLOSE the cursor.

       EXEC SQL CLOSE CURSOR1;
   

6. Deal with any SQL return codes that indicate errors.