Fixed-list SELECT statements

 

In dynamic SQL, a fixed-list SELECT statement retrieves data of a predictable number and type. When using this statement, you can anticipate and define host variables to accommodate the retrieved data so that an SQL descriptor area (SQLDA) is not necessary.

Each successive FETCH returns the same number of values as the last, and these values have the same data formats as those returned for the last FETCH. You can specify host variables in the same manner as for any SQL application.

You can use fixed-list dynamic SELECT statements with any SQL-supported application program.

To run fixed-list SELECT statements dynamically, your application must:

1. Place the input SQL statement into a host variable.

2. Issue a PREPARE statement to validate the dynamic SQL statement and put it into a form that can be run. If DLYPRP (*YES) is specified on the CRTSQLxxx command, the preparation is delayed until the first time the statement is used in an EXECUTE or DESCRIBE statement, unless the USING clause is specified on the PREPARE statement.

3. Declare a cursor for the statement name.

4. Open the cursor.

5. FETCH a row into a fixed list of variables (rather than into a descriptor area, as if you were using a varying-list SELECT statement.

6. When end of data occurs, close the cursor.

7. Handle any SQL return codes that result.

For example:

  MOVE 'SELECT EMPNO, LASTNAME FROM CORPDATA.EMPLOYEE WHERE EMPNO>?'
  TO DSTRING.
  EXEC SQL    PREPARE S2 FROM :DSTRING END-EXEC.
 
  EXEC SQL    DECLARE C2 CURSOR FOR S2 END-EXEC.
 
  EXEC SQL    OPEN C2 USING :EMP END-EXEC.
 
  PERFORM FETCH-ROW UNTIL SQLCODE NOT=0.
 
  EXEC SQL    CLOSE C2 END-EXEC.
  STOP-RUN.
  FETCH-ROW.
  EXEC SQL    FETCH C2 INTO :EMP, :EMPNAME END-EXEC.

Remember that because the SELECT statement, in this case, always returns the same number and type of data items as previously run fixed-list SELECT statements, you do not need to use an SQL descriptor area.

 

Parent topic:

Processing SELECT statements and using a descriptor