Varying-List Select Statements

A varying-list SELECT statement is one that retrieves data items of varying number and format. Because the number and type of data items are not known, you cannot define host variables in advance.

However, by including the SQLDA in your program, you can use PREPARE to define a SELECT statement and follow it with a DESCRIBE statement. This process inserts the data item information into the SQLDA, which your program can use to execute a SELECT statement. Likewise, record fetches are performed using the SQLDA.

To issue a varying-list SELECT statement:

1. Include the SQLDA copybook in your program.
2. Use PREPARE to define a statement (either already in a statement buffer or to be read) so that it may be translated to executable form.
3. Use DESCRIBE for the prepared statement to determine how many columns will be retrieved and their data types. You may use a PREPARE INTO to PREPARE and DESCRIBE in a single statement.
4. If the SQLDA is not large enough to contain column descriptions for each column, your program must determine how much space is needed, obtain that storage space, build a new SQLDA, and DESCRIBE again.
5. The program must now determine how much space is needed for the data in each column (and possibly an indicator variable). Storage must be obtained so that when a value is fetched, your program will know where to put the data. Acquiring this storage is accomplished by analyzing the SQLDA. For more information on the SQLDA, see the SQL Option SQL Reference.
6. Execute the SELECT statement by declaring and opening a cursor for the prepared statement.
7. Fetch a row of data by specifying USING DESCRIPTOR SQLDA on the FETCH statement.
8. When end of data is reached, close the cursor.

The following program excerpt shows how to execute a varying-list SELECT statement dynamically.

* include SQLDA copybook. 
     EXEC SQL 
         INCLUDE SQLDA
     END-EXEC

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

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

* allocate space for an SQLDA ... 
     MOVE 20 TO SQLN

* describe SELECT command. 
     EXEC SQL 
         DESCRIBE q1 INTO SQLDA 
     END-EXEC

* make sure SQLDA is big enough. 
     IF (SQLN <= SQLD) 
     	   PERFORM ...
             ... 
             MOVE SQLD TO SQLN. 
             EXEC SQL 
                 DESCRIBE q1 INTO SQLDA 
             END-EXEC 
         END-PERFORM
     END-IF

* analyze results of DESCRIBE. 
...
* allocate storage to hold one row of the result. ...
* 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 USING DESCRIPTOR SQLDA 
     END-EXEC

* display results fetching next record as long as there 
* is one. 
     PERFORM UNTIL SQLCODE <> 0 
         DISPLAY ... 
         EXEC SQL 
             FETCH c1 USING DESCRIPTOR SQLDA 
         END-EXEC 
     END-PERFORM 
     DISPLAY 'END OF LIST'.

* close cursor. 
     EXEC SQL 
         CLOSE c1 
     END-EXEC