Varying-list SELECT statements

 

In dynamic SQL, a varying-list SELECT statement is used when the number and format of the result columns are not predictable; that is, you do not know the data types or the number of variables that you need.

Therefore, you cannot define host variables in advance to accommodate the result columns returned.

In REXX, steps 5.b, 6, and 7 are not applicable. REXX only supports SQL descriptors defined using the SQLDA structure; it does not support allocated SQL descriptors.

If your application accepts varying-list SELECT statements, your program has to:

  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 (declared in step 3) that includes the name of the dynamic SELECT statement.

  5. For an allocated SQL descriptor, run an ALLOCATE DESCRIPTOR statement to define the descriptor you intend to use.

  6. Issue a DESCRIBE statement to request information from SQL about the type and size of each column of the result table. Notes:

    1. You can also code the PREPARE statement with an INTO clause to perform the functions of PREPARE and DESCRIBE with a single statement.

    2. If using an SQLDA and the SQLDA is not large enough to contain column descriptions for each retrieved column, the program must determine how much space is needed, get storage for that amount of space, build a new SQLDA, and reissue the DESCRIBE statement.

      If using an allocated SQL descriptor and the descriptor is not large enough, deallocate the descriptor, allocate it with a larger number of entries, and reissue the DESCRIBE statement.

  7. For an SQLDA descriptor, allocate the amount of storage needed to contain a row of retrieved data.

  8. For an SQLDA descriptor, put storage addresses into the SQLDA to tell SQL where to put each item of retrieved data.

  9. FETCH a row.

  10. Process the data returned in the SQL descriptor.

  11. Handle any SQL return codes that might result.

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

  13. For an allocated SQL descriptor, run a DEALLOCATE DESCRIPTOR statement to delete the descriptor.

 

Parent topic:

Processing SELECT statements and using a descriptor

 

Related reference


Example: A SELECT statement for allocating storage for SQLDA