Fixed-List Select Statements

A fixed-list SELECT statement is a statement that returns a predictable number of data items of a known type from an open cursor. For each of these items, a host variable is required to accommodate incoming data.

To issue a fixed-list SELECT statement dynamically:

1. (Optional) Test each SQL statement using the SQLWizard (Windows) or Interactive SQL (UNIX) to see that proper results are obtained.
2. Load the SQL statement into a data area.
3. Use PREPARE to validate the statement and translate it into an executable form.
4. Use DECLARE to declare a cursor for the statement.
5. Use OPEN to open the cursor.
6. Use FETCH to retrieve a row into a fixed list of variables.
7. When the end of data is reached, use CLOSE to close the cursor.

   Be sure to handle any errors.

The following example shows how to execute a fixed-list SELECT statement dynamically.

* include SQLDA copybook 
     EXEC SQL 
         INCLUDE SQLDA 
     END-EXEC

* assign value to variable sqlcmd. 
     MOVE 'select e_no, ' & 
          'lname from employee where dept="1050"' TO sqlcmd

* prepare SELECT command. 
     EXEC SQL 
         PREPARE q1 FROM :sqlcmd 
     END-EXEC

* declare cursor for SELECT command. 
     EXEC SQL 
         DECLARE c1 CURSOR FOR q1 
     END-EXEC

* open cursor. 
     EXEC SQL 
         OPEN c1 
     END-EXEC

* fetch data into program variables EMPNO, LASTNME and 
* FIRSTNME. 
     EXEC SQL 
         FETCH c1 INTO :EMPNO, :LASTNME 
     END-EXEC 

* close cursor. 
     EXEC SQL 
         CLOSE c1 
     END-EXEC