Dynamic SQL for Varying-List SELECT Statements

Getting Started › Dynamic SQL › Using Dynamic SQL in Application Programs › Dynamic SQL for Varying-List SELECT Statements

Dynamic SQL for Varying-List SELECT Statements

The most complex way to use dynamic SQL for SELECT statements is when you do not know (when you write the application program) the number and data types of the columns in the SELECT's result table. This requires the use of varying-list SELECT statements.

As the example below shows, a program that uses varying-list SELECT statements must do the following:

Load input SQL statement into a data area (as in non-SELECT statement example already given).

DECLARE a cursor-name for the statement name.

    EXEC SQL DECLARE CURSOR1 CURSOR FOR STMT;

Declare a host variable called SMLSQLDA of data type SQLDA, with 100 SQLVARs, SQLN=100 (for example, :SQLSTRING = SELECT COL1 FROM TAB1). If the statement in :SQLSTRING contains parameter markers, allocate and initialize an SQLDA called, for example, SQLDAPARA, which describes the host variables that correspond to the parameter markers. Allocate the storage for these host variables, if necessary.

Prepare the variable statement.

    PREPARE USERSTMT INTO SMLSQLDA FROM :SQLSTRING;
    IF SMLSQLDA.SQLD = 0 THEN
        the statement was not a select-statement--ERROR
    ENDIF

You must next determine if you have to allocate a larger SQLDA. The PREPARE caused SMLSQLDA.SQLD to be set to the number of columns in the result table, and SMLSQLDA.SQLDABC is equal to the size in bytes required for an SQLDA big enough to describe that many columns (16 + SQLD * 44). If SMLSQLDA.SQLD is greater than SMLSQLDA.SQLN, the number of columns in the result table is larger than the size allowed for in SMLSQLDA. In this case, no information has been put into the SQLVARs of SMLSQLDA. The SQLD field has been set to the number of columns in the result table, so that an SQLDA of the required size may be allocated.
The application program should now allocate an SQLDA of the size indicated by SMLSQLDA.SQLD. If we call this full-size SQLDA LRGSQLDA, to get the description of the result table filled in, the application program should then execute a DESCRIBE statement, using LRGSQLDA.
```
    EXEC SQL DESCRIBE STMT INTO LRGSQLDA;
```
Now we have an SQLDA that describes (in its SQLVAR section) all of the columns of the result table of the select-statement in SQLSTRING. Examine the SQLDA to allocate storage for a result row of the select-statement. Set the addresses in the SQLVAR entries to point to memory allocated for each entry.
```
    EXEC SQL OPEN CURSOR1;
```
Or, if there were parameter markers in the select-statement:
```
    EXEC SQL OPEN CURSOR1 USING DESCRIPTOR SQLDAPARA;
```
FETCH rows from result table and CLOSE the cursor. The clause USING DESCRIPTOR LRGSQLDA names an SQLDA in which the occurrences of SQLVAR point to other areas that receive the values returned by the FETCH. The USING clause can be used here because LRGSQLDA was set up previously (in this example).
```
    EXEC SQL FETCH CURSOR1 USING DESCRIPTOR LRGSQLDA;
```
Close the cursor.
```
    EXEC SQL CLOSE CURSOR1;
```
Deal with any SQL return codes that indicate errors.