Dynamic SQL with descriptor areas

Example

Any SQL statement is read in at runtime. It is assumed that the number and data types of the input values are not known when the program is written. The DESCRIBE statement is used to determine both the input values and the number and data types of the column values for the derived rows.

In the example, the SQL statements are marked with EXEC SQL and END-EXEC as in ESQL-COBOL. The actions carried out by the host language of the ESQL program are shown in italics.

Error handling is only demonstrated once in this example. A complete example would, of course, require SQLSTATE to be queried after every executable SQL statement and appropriate error handling to be initiated.
It is also assumed that there are no multiple columns.

Declaration of the host variables H_COUNT, H_TYPE, H_LENGTH, H_NAME etc. to receive the values for COUNT, TYPE, LENGTH, NAME etc. in the fields of the descriptor area.

Declaration of host variables for each of the SQL data types: H_CHARACTER, H_NUMERIC1, H_NUMERIC2 etc. for different value ranges etc.

Declaration of the host variable SOURCESTMT to receive the dynamically compilable statement

1. EXEC SQL DECLARE dyn_cursor SCROLL CURSOR FOR dynstmt END-EXEC

2. EXEC SQL ALLOCATE DESCRIPTOR GLOBAL 'Placehold' END-EXEC
EXEC SQL ALLOCATE DESCRIPTOR GLOBAL 'Results' END-EXEC

3. Read in SOURCESTMT

   EXEC SQL PREPARE dynstmt FROM :SOURCESTMT END-EXEC

4. 
   

   1. EXEC SQL DESCRIBE INPUT dynstmt
USING SQL DESCRIPTOR GLOBAL 'Placehold' END-EXEC

      Error handling if SQLSTATE(1:2) is not equal to “00”

   2. EXEC SQL DESCRIBE OUTPUT dynstmt USING SQL DESCRIPTOR GLOBAL 'Results' END-EXEC

      Error handling if SQLSTATE(1:2) is not equal to “00”

5. EXEC SQL GET DESCRIPTOR GLOBAL 'Placehold' :H_COUNT = COUNT END-EXEC

6. Loop from I=1 to I=H_COUNT

   EXEC SQL GET DESCRIPTOR GLOBAL 'Placehold' VALUE :I
:H_TYPE =TYPE,
:H_LENGTH = LENGTH, ..., END-EXEC

7. Selection of a suitable user variable in accordance with the placeholder's data type. Adjusting the data type and reading the value for the placeholder into the descriptor area, e.g.:

   if :H_TYPE = 1 or 12 Read the value for the placeholder into H_CHARACTER

   EXEC SQL SET DESCRIPTOR GLOBAL 'Placehold' VALUE :I TYPE = 1,

   LENGTH=256, INDICATOR=0, DATA=:H_CHARACTER END-EXEC

   if :H_TYPE =2 or 3 and 10 to 18 digits before the decimal place Read the value for the placeholder into H_NUMERIC1

   EXEC SQL SET DESCRIPTOR GLOBAL 'Placehold' VALUE :I TYPE=2,

   PRECISION=18, SCALE=0, INDICATOR=0, DATA=:H_NUMERIC1 END-EXEC

   if :H_TYPE =2 or 3 and 1 to 9 digits before the decimal place Read the value for the placeholder into H_NUMERIC2

   EXEC SQL SET DESCRIPTOR GLOBAL 'Placehold' VALUE :I TYPE=2,

   PRECISION=18, SCALE=9, INDICATOR=0, DATA=:H_NUMERIC2 END-EXEC

   if :H_TYPE =2 or 3 and 0 digits before the decimal place Read the value for the placeholder into H_NUMERIC3

   EXEC SQL SET DESCRIPTOR GLOBAL 'Placehold' VALUE :I TYPE=2,

   PRECISION=18, SCALE=18, INDICATOR=0, DATA=:H_NUMERIC3 END-EXEC

   etc.

   End of loop

8. EXEC SQL GET DESCRIPTOR GLOBAL 'Results' :H_COUNT=COUNT END-EXEC

   if :H_COUNT=0

   EXEC SQL EXECUTE dynstmt USING SQL DESCRIPTOR GLOBAL 'Placehold'

   END-EXEC

   Go to (12)

9. else

   Loop from I=1 to I=H_COUNT

   EXEC SQL GET DESCRIPTOR GLOBAL 'Results' VALUE :I
:H_TYPE=TYPE
:H_LENGTH=LENGTH ... END-EXEC

   Selection of a suitable user variable in accordance with the data type of the column values in the derived table. Adjusting the data type in the descriptor area, like (6) for the placeholders, e.g.:

   if :H_TYPE = 1 or 12

   EXEC SQL SET DESCRIPTOR GLOBAL 'Results' VALUE :I TYPE = 1, LENGTH=256

   END-EXEC

   etc.

   End of loop

10. EXEC SQL OPEN dyn_Cursor USING SQL DESCRIPTOR GLOBAL 'Placehold' END-EXEC

11. Execute the following FETCH statement in a loop until the “end of table” condition occurs

    EXEC SQL FETCH NEXT FROM dyn_cursor INTO SQL DESCRIPTOR GLOBAL

    'Results' END-EXEC

    Error handling if SQLSTATE(1:2) is not equal to “00”

    Loop through all columns of the derived row: if :H_TYPE =1 or 12

    EXEC SQL GET DESCRIPTOR GLOBAL 'Results' VALUE :I

    :H_NAME=NAME, :H_CHARACTER=DATA END-EXEC

    if :H_TYPE =2 or 3 and 10 to 18 digits before the decimal place

    EXEC SQL GET DESCRIPTOR GLOBAL 'Results' VALUE :I

    :H_NAME=NAME, :H_NUMERIC1=DATA END-EXEC

    etc.

                  Output H_NAME and the corresponding host variable

                   End of loop

           End of loop

12. EXEC SQL CLOSE dyn_cursor END-EXEC

13. EXEC SQL DEALLOCATE DESCRIPTOR GLOBAL 'Placehold' END-EXEC

    EXEC SQL DEALLOCATE DESCRIPTOR GLOBAL 'Results' END-EXEC

Meaning

1. Declare the dynamic cursor DYN_CURSOR. DYN_CURSOR has a dynamically compilable cursor description DYNSTMT.

2. Create the descriptor areas: Placehold for the placeholders and Results for the column values of a derived row.

3. Read the statement text into SOURCESTMT and prepare it for subsequent execution. The statement text can be a dynamically compilable statement or a cursor description.

4. 
   

   1. After successful execution of the PREPARE statement, the number and data types of the placeholders in the dynamically compiled SQL statement DYNSTMT are queried with DESCRIBE INPUT. If the DESCRIBE INPUT statement was successful, DYNSTMT is dynamically compiled and the SQL descriptor area Placehold is filled with the type descriptions for the dynamic parameters.

   2. In the same way, DESCRIBE OUTPUT is used to write the number and data types of the columns of a derived row to the descriptor area Results. If the DESCRIBE OUTPUT statement was successful, DYNSTMT is dynamically compiled and the SQL descriptor area Results is filled with the type descriptions for the columns of a derived row.

5. GET DESCRIPTOR is used to determine the number of placeholders H_COUNT.

6. GET DESCRIPTOR is used to read the data type, length etc. of every placeholder and to transfer these to the appropriate host variables H_TYPE, H_LENGTH etc.

7. A host variable is selected to suit the data type of the placeholder. The value of the placeholder is read into this host variable. In order to transfer the value of a placeholder to the descriptor area, the SET DESCRIPTOR statement must be used to set the data type, length, number of digits before and after the decimal point etc. in the descriptor area to suit the data types of the host variables. The demonstration program, for example, allows for three data types, corresponding to the SQL data types NUMERIC(18,0), NUMERIC(18,9) and NUMERIC(18,18). Depending on the data type of the placeholder, one of these data types is set in the descriptor area.After the SET DESCRIPTOR statements have been executed, the descriptor area Placehold contains the values for the placeholders.

8. GET DESCRIPTOR queries the number of columns in the derived table. If this number is 0, the dynamically compiled statement is executed with EXECUTE. The descriptor area Placehold is specified in the USING clause. This area contains the values for the placeholders in the dynamically compiled statement. The program is then continued at (13).

9. Otherwise, the rows of the derived table are read using the cursor declared in (1).In the same way as for steps (6) and (7), the data types and lengths of the derived columns are first read for the descriptor area Results using GET DESCRIPTOR. SET DESCRIPTOR is then used to adjust the data types, lengths etc. in the descriptor area.

10. The cursor DYN_CURSOR is opened. The descriptor area Placehold is specified in the USING clause. It contains the values for the placeholders in the cursor description.

11. The derived table is written to the descriptor area row by row using the FETCH statement. GET DESCRIPTOR is used to transfer the column values from the descriptor area to suitable host variables and to output them.

12. The cursor DYN_CURSOR is closed.

13. Finally, the descriptor areas are released with DEALLOCATE.