Including dynamic SQL for fixed-list SELECT statements in your program

A fixed-list SELECT statement returns rows that contain a known number of values of a known type. When you use this type of statement, you know in advance exactly what kinds of host variables you need to declare to store the results.

About this task

The term fixed-list does not imply that you must know in advance how many rows of data will be returned. However, you must know the number of columns and the data types of those columns. A fixed-list SELECT statement returns a result table that can contain any number of rows; your program looks at those rows one at a time, using the FETCH statement. Each successive fetch returns the same number of values as the last, and the values have the same data types each time. Therefore, you can specify host variables as you do for static SQL.

An advantage of the fixed-list SELECT is that you can write it in any of the programming languages that Db2 supports. Varying-list dynamic SELECT statements require assembler, C, PL/I, and COBOL.

For example, suppose that your program retrieves last names and phone numbers by dynamically executing SELECT statements of this form:

SELECT LASTNAME, PHONENO FROM DSN8C10.EMP
  WHERE ... ;

The program reads the statements from a terminal, and the user determines the WHERE clause.

As with non-SELECT statements, your program puts the statements into a varying-length character variable; call it DSTRING. Eventually you prepare a statement from DSTRING, but first you must declare a cursor for the statement and give it a name.

Procedure

To execute a fixed-list SELECT statement dynamically, your program must:

Include an SQLCA.
Load the input SQL statement into a data area.
The preceding two steps are exactly the same including dynamic SQL for non-SELECT statements in your program.
Declare a cursor for the statement name.
Dynamic SELECT statements cannot use INTO. Therefore, you must use a cursor to put the results into host variables.
For example, when you declare the cursor, use the statement name (call it STMT), and give the cursor itself a name (for example, C1):
```
EXEC SQL DECLARE C1 CURSOR FOR STMT;
```
Prepare the statement.
Prepare a statement (STMT) from DSTRING. This is one possible PREPARE statement:
```
EXEC SQL PREPARE STMT FROM :DSTRING ATTRIBUTES :ATTRVAR;
```
ATTRVAR contains attributes that you want to add to the SELECT statement, such as FETCH FIRST 10 ROWS ONLY or OPTIMIZE for 1 ROW. In general, if the SELECT statement has attributes that conflict with the attributes in the PREPARE statement, the attributes on the SELECT statement take precedence over the attributes on the PREPARE statement. However, in this example, the SELECT statement in DSTRING has no attributes specified, so Db2 uses the attributes in ATTRVAR for the SELECT statement.

As with non-SELECT statements, the fixed-list SELECT could contain parameter markers. However, this example does not need them.
Open the cursor.
The OPEN statement evaluates the SELECT statement named STMT.
For example, without parameter markers, use this statement:
```
EXEC SQL OPEN C1;
```
If STMT contains parameter markers, you must use the USING clause of OPEN to provide values for all of the parameter markers in STMT. If four parameter markers are in STMT, you need the following statement:
```
EXEC SQL OPEN C1 USING :PARM1, :PARM2, :PARM3, :PARM4;
```
Fetch rows from the result table.
For example, your program could repeatedly execute a statement such as this:
```
EXEC SQL FETCH C1 INTO :NAME, :PHONE;
```
The key feature of this statement is the use of a list of host variables to receive the values returned by FETCH. The list has a known number of items (in this case, two items, :NAME and :PHONE) of known data types (both are character strings, of lengths 15 and 4, respectively).
You can use this list in the FETCH statement only because you planned the program to use only fixed-list SELECTs. Every row that cursor C1 points to must contain exactly two character values of appropriate length. If the program is to handle anything else, it must use the techniques for including dynamic SQL for varying-list SELECT statements in your program.
Close the cursor.
This step is the same as for static SQL.
A WHENEVER NOT FOUND statement in your program can name a routine that contains this statement:
```
EXEC SQL CLOSE C1;
```
Handle any resulting errors. This step is the same as for static SQL, except for the number and types of errors that can result.