Dynamically executing SQL for varying-list SELECT statements

A varying-list SELECT statement returns rows that contain an unknown number of values of unknown type. When you use this type of statement, you do not know in advance exactly what kinds of host variables you need to declare for storing the results.

What your application program must do for varying-list SELECT statements: To execute a varying-list SELECT statement dynamically, your program must follow these steps:
  1. Include an SQLIMSCA.
  2. Load the input SQL statement into a data area.
  3. Prepare and execute the statement. This step is more complex than for fixed-list SELECTs. It involves the following steps:
    1. Include an SQLIMSDA (SQL descriptor area).
    2. Declare a cursor and prepare the statement.
    3. Obtain information about each field of the result segment.
    4. Determine the main storage needed to hold a row of retrieved data.
    5. Put storage addresses in the SQLIMSDA to tell where each item of retrieved data should be stored.
    6. Open the cursor.
    7. Fetch a row.
    8. Eventually close the cursor and free main storage.
  4. Handle any errors that might result.
Preparing a varying-list SELECT statement:

Suppose that your program dynamically executes SQL statements.

Your program puts the statements into a varying-length character variable; call it STMTSTR. Your program goes on to prepare a statement from the variable and then give the statement a name; call it S1.

If the statement is a SELECT statement, the program must find out how many values are in each row, and what their data types are. The information comes from an SQL descriptor area (SQLIMSDA).

An SQL descriptor area:

The SQLIMSDA is a structure that is used to communicate with your program, and storage for it is usually allocated dynamically at run time.

For COBOL, use: 

EXEC SQLIMS INCLUDE SQLIMSDA END-EXEC.
Obtaining information about the SQL statement:

An SQLIMSDA can contain a variable number of occurrences of SQLIMSVAR, each of which is a set of five fields that describe one field in the result segment of a SELECT statement. The included SQLIMSDA contains a maximum of 750 occurrences of SQLVARs, which means it can hold up to 750 of resulting columns.

Declaring a cursor for the statement:

As before, you need a cursor for the dynamic SELECT. For example, write: 

EXEC SQLIMS
  DECLARE C1 CURSOR FOR S1
Preparing the statement using the minimum SQLIMSDA:

To prepare a statement from the character string in STMTSTR and also enter its description into SQLIMSDA, write this: 

EXEC SQLIMS PREPARE STMT FROM :STMTSTR END-EXEC.
EXEC SQLIMS DESCRIBE STMT INTO :SQLIMSDA END-EXEC.

The following figure shows the contents of the minimum SQLIMSDA in use.

Figure 1. The minimum SQLIMSDA structure
Begin figure description.The minimum SQLIMSDA structure, Header, is shown as a row with four columns, containing SQLIMSDAID, SQLIMSDABC, 750, and SQLIMSD. End figure description.
SQLIMSN determines what SQLVAR gets:

The SQLIMSN field, which you must set before using DESCRIBE (or PREPARE INTO), tells how many occurrences of SQLIMSVAR the SQLIMSDA is allocated for.

  • Base SQLIMSVAR information includes:
    • Data type code
    • Length attribute
    • Column name or label
    • Host variable address
    • Indicator variable address
Whenever you execute DESCRIBE, IMS returns the following values, which you can use to build an SQLIMSDA of the correct size:
  • SQLIMSD is 0 if the SQL statement is not a SELECT. Otherwise, SQLIMSD is the number of fields in the result segment.
If the statement is not a SELECT:
To find out if the statement is a SELECT, your program can query the SQLIMSD field in SQLIMSDA after the DESCRIBE statement. If the field contains 0, the statement is not a SELECT, the statement is already prepared, and your program can execute it. You can use: 
EXEC SQLIMS EXECUTE STMT END-EXEC.
If the statement is a SELECT:

After the DESCRIBE statement executes, each occurrence of SQLIMSVAR contains a description of one field of the result segment in five fields.

The following table describes the values in the descriptor area.

Table 1. Values inserted in the SQLIMSDA
Value Field Description
SQLIMSDA SQLIMSDAID An eye-catcher
750 SQLIMSN The number of occurrences of SQLIMSVAR, set by the program
2 SQLIMSD The number of occurrences of SQLIMSVAR actually used by the DESCRIBE statement
452 SQLIMSTYPE The value of SQLIMSTYPE in the first occurrence of SQLIMSVAR. It indicates that the first field contains fixed-length character strings, and does not allow nulls.
3 SQLIMSLEN The length attribute of the column
Undefined SQLIMSIND  
8 SQLIMSNAME The number of characters in the field name
HOSPCODE SQLIMSNAME+2 The field name of the first column

The following figure shows an SQLIMSDA that describes two fields.

Figure 2. Contents of SQLIMSDA after executing DESCRIBE
Begin figure description. The FULSQLIMSDA is represented as an SQLIMSDA header and two SQLIMSVAR elements. End figure description.

The first SQLIMSVAR pertains to the first field of the result segment (the HOSPCODE column). SQLIMSVAR element 1 contains fixed-length character strings and does not allow null values (SQLIMSTYPE=452); the length attribute is 3.

Acquiring storage to hold a row:

Before fetching rows of the result segment, your program must:

  1. Analyze each SQLIMSVAR description to determine how much space you need for the field value.
  2. Derive the address of some storage area of the required size.
  3. Put this address in the SQLIMSDATA field.

If the SQLIMSTYPE field indicates that the value can be null, the program must also put the address of an indicator variable in the SQLIMSIND field. The following figures show the SQL descriptor area after you take certain actions.

Putting storage addresses in the SQLIMSDA:

After analyzing the description of each column, your program must replace the content of each SQLIMSDATA field with the address of a storage area large enough to hold values from that column. Similarly, for every field that allows nulls, the program must replace the content of the SQLIMSIND field. The content must be the address of a halfword that you can use as an indicator variable for the column. The program can acquire storage for this purpose, of course, but the storage areas used do not have to be contiguous.

The following figure shows the SQLIMSDA after your program acquires storage for the field values and their indicators, and puts the addresses in the SQLIMSDATA fields of the SQLIMSDA. It shows the content of the descriptor area before the program obtains any rows of the result table. Addresses of fields and indicator variables are already in the SQLIMSVAR.

Figure 3. SQL descriptor area after analyzing descriptions and acquiring storage
Begin figure summary. This figure represents the SQL descriptor area with additional storage and indicator variables. End figure summary.
Executing a varying-list SELECT statement dynamically:

You can easily retrieve rows of the result table using a varying-list SELECT statement. The statements differ only a little from those for the fixed-list example.

Open the cursor: If the SELECT statement contains no parameter marker, this step is simple enough. For example: 
EXEC SQLIMS OPEN C1 END-EXEC.
Fetch rows from the result table: This statement differs from the corresponding one for the case of a fixed-list select. Write: 
EXEC SQLIMS
  FETCH C1 USING DESCRIPTOR :SQLIMSDA END-EXEC.

The key feature of this statement is the clause USING DESCRIPTOR :SQLIMSDA. That clause names an SQL descriptor area in which the occurrences of SQLIMSVAR point to other areas. Those other areas receive the values that FETCH returns. It is possible to use that clause only because you previously set up SQLIMSDA to look like Figure 2.

The following figure shows the result of the FETCH. The data areas identified in the SQLIMSVAR fields receive the values from a single row of the result table.

Figure 4. SQL descriptor area after executing FETCH
Begin figure summary. This figure shows what the SQL descriptor area looks like after executing a FETCH statement. End figure summary.

Successive executions of the same FETCH statement put values from successive rows of the result table into these same areas.

Close the cursor: This step is the same as for the fixed-list case. When no more rows need to be processed, execute the following statement: 
EXEC SQLIMS CLOSE C1 END-EXEC.