Dynamically executing SQL for fixed-list SELECT statements

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 can specify a list of host variables to contains the filed values.

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 fields and the data types of those fields. A fixed-list SELECT statement returns a result segment 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.

Procedure

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

  1. Include an SQLIMSCA.
  2. 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.
  3. Declare a cursor for the statement name.
  4. Prepare the statement.
  5. Open the cursor.
  6. Fetch rows from the result segment.
  7. Close the cursor.
  8. Handle any resulting errors. This step is the same as for static SQL, except for the number and types of errors that can result.
Example: Suppose that your program retrieves hospital names and codes by dynamically executing SELECT statements of this form: 
SELECT HOSPNAME, HOSPCODE FROM PCB01.HOSPITAL
Declaring a cursor for the statement name:

Use a cursor to put the results into host variables for a SELECT statement.

Example: When you declare the cursor, use the statement name (call it STMT), and give the cursor itself a name (for example, C1): 
EXEC SQLIMS DECLARE C1 CURSOR FOR STMT
END-EXEC.
Preparing the statement:

Prepare a statement (STMT) from STMTSTR.

Example: This is one possible PREPARE statement: 
EXEC SQLIMS PREPARE STMT FROM :STMTSTR
END-EXEC.

To execute STMT, your program must open the cursor, fetch rows from the result segment, and close the cursor.

Opening the cursor:

The OPEN statement evaluates the SELECT statement named STMT.

Example: 
EXEC SQLIMS OPEN C1
END-EXEC.
Fetching rows from the result table:
Example: Your program could repeatedly execute a statement such as this: 
EXEC SQLIMS FETCH C1 INTO :HOSPNAME, :HOSPCODE
END-EXEC.
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, :HOSPNAME and :HOSPCODE) 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.

Closing the cursor:
Example: Close the cursor when your program is finished running the FETCH statement: 
EXEC SQLIMS CLOSE C1
END-EXEC.