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