Dynamic SQL for Arbitrary Statement-Types

Getting Started › Dynamic SQL › Using Dynamic SQL in Application Programs › Dynamic SQL for Arbitrary Statement-Types

Dynamic SQL for Arbitrary Statement-Types

The most complex use of dynamic SQL is when you need to execute, in a program, several kinds of dynamic SQL statements, including varying-list SELECT statements (in any of which a variable number of parameter markers might be contained). Such a program could be said to execute "arbitrary" SQL statements. For example, this kind of program could present a list of choices, such as choices about:

Operations (select, delete, update)
Table names
Columns to select or update

The program could also allow the entering of lists (such as worker ID numbers) by which to control the application of operations.

When you know the number and types of parameters, but you do not know in advance the number of parameter markers (and perhaps the kinds of parameter they stand for):

Name a list of host variables in the EXECUTE statement if the SQL statement is not SELECT.
Name a list of host variables in the OPEN statement if the SQL statement is SELECT.

In either case, the number and types of host variables named by your program must agree with the number of parameter markers (in USERSTMT) and the types of parameter for which they stand. The first variable you use must have the expected type for the first parameter marker in the statement, and so on for the other variables and parameter marker. You must therefore use at least as many variables as the number of parameter markers.

Use a SQL Descriptor Area (SQLDA) when you do not know the number and types of parameters. You can have as many SQLDAs included in your program as you want (there is no upper limit), and they do not all have to be used for the same thing. You can also set up an SQLDA to describe a set of parameters.

For purposes of this example, the SQLDA describing a set of parameters is called SQLDAPARA. Its structure is the same as the structure of other SQLDAs, and as in other SQLDA structures, the number of SQLVAR occurrences can vary. But in SQLDAPARA, each occurrence of SQLVAR is used to describe one host variable that replaces one parameter marker at execution time, either when (for a SELECT statement) a cursor is opened, or when a non-SELECT statement is executed. With SQLDAPARA, there must therefore be one SQLVAR for every parameter marker.

In SQLDAPARA you can ignore some of the SQLDA fields, but in the case of other fields, you must fill them before you use an EXECUTE or OPEN statement. See SQLDA (EXECUTE, FETCH, or OPEN Statement).

Following is an example of a program that executes arbitrary SQL statements.

To allow for the case in which USERSTMT gets prepared as a select-statement, do this DECLARE statement:
Note: Alternately, this DECLARE statement could be located (as noted later in this example) in the part of the program labeled: "* The statement is a select-statement. *"
```
 PROC HANDLEALL

 EXEC SQL DECLARE CURSOR1 CURSOR FOR USERSTMT;
```

Allocate a host variable called SMLSQLDA of data type SQLDA, with 100 SQLVARs, SQLN=100.

Note: 100 in the previous is an arbitrary number. To save static storage it could be much less than 100, but the lower number would result in a lengthened execution time because of the increased likelihood of needing the second DESCRIBE that is shown later in this example.

 SQLSTRING := the SQL statement, constructed in some way
 IF the statement in SQLSTRING has parameter markers THEN
     Analyze the SQL statement: find the parameter markers
       and decide what host variables could be used to
       contain values for each one.  Allocate the host
       variables and indicator variables if necessary.
       (*These parameter markers all describe input host
       variables, values that must be passed to the DBMS when the
       statement is executed.*)
    Declare a host variable called SQLDAPARA of data type
       SQLDA and fill it in to correctly describe and
       point to host variables that correspond to the
       parameter markers.

 ENDIF

 EXEC SQL PREPARE USERSTMT INTO SMLSQLDA FROM :SQLSTRING;

Or alternately:

 EXEC SQL PREPARE USERSTMT FROM :SQLSTRING;

 EXEC SQL DESCRIBE USERSTMT INTO SMLSQLDA;

The output result of the PREPARE INTO statement (or the PREPARE and DESCRIBE statements) is that the SMLSQLDA is filled in by the DBMS to describe the result table of the statement in USERSTMT. If the statement in USERSTMT is not a select-statement, there is no result table.

 IF SMLSQLDA.SQLD = 0 THEN
     (* The statement is not a select-statement. *)
     IF the statement in SQLSTRING has no parameter markers THEN
         EXEC SQL EXECUTE USERSTMT;
     ELSE
         IF the statement in SQLSTRING does have parameter
          markers THEN
             EXEC SQL EXECUTE USERSTMT USING DESCRIPTOR SQLDAPARA;
         ENDIF
     ENDIF
 ELSE
     (* The statement is a select-statement. *)
     (*NOTE: The DECLARE CURSOR1 CURSOR FOR USERSTMT statement could
       go here, if desired.  It is not executed, but must
       appear physically in the application program source
       before any other statement uses the cursor name.*)
     (*If needed, allocate a bigger SQLDA:*)

     IF SMLSQLDA.SQLD > SMLSQLDA.SQLN THEN

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 must now allocate an SQLDA of the size indicated by SMLSQLDA.SQLD. In this example, this full-sized SQLDA is called LRGSQLDA.

Allocate a host variable of data type SQLDA, called LRGSQLDA:

           LRGSQLDA, SQLN := SMLSQLDA.SQLD, SQLDABC :=
           16 + SQLN * 44, with SQLN SQLVAR's.

To get the description of the result table filled in, the application program must now execute a DESCRIBE statement using LRGSQLDA.
```
         EXEC SQL DESCRIBE USERSTMT INTO LRGSQLDA;
     ENDIF
```
We now have an SQLDA that describes, in its SQLVAR section, all of the columns of the result table of the select-statement in SQLSTRING.

Allocate storage for a result row of the select-statement by examining the SQLDA (either SMLSQLDA or LRGSQLDA). Set the addresses in the SQLVAR entries to point to the host variables allocated for each column of the result table.

     IF the statement in SQLSTRING has no parameter markers THEN
         EXEC SQL OPEN CURSOR1;
     ELSE
         EXEC SQL OPEN CURSOR1 USING DESCRIPTOR SQLDAPARA;
     ENDIF
     DO WHILE SQLCODE NOT = 100
         IF LRGSQLDA was allocated
             EXEC SQL FETCH CURSOR1 USING DESCRIPTOR LRGSQLDA;
         ELSE
             EXEC SQL FETCH CURSOR1 USING DESCRIPTOR SMLSQLDA;
         ENDIF

At this point, the program can decide whether to delete the row (that was just read) or to update it. If the program wants to update or delete the current row, it can build the UPDATE or DELETE statement and execute it dynamically, as follows:

          IF an update is desired THEN
              VAR5 := the UPDATE WHERE CURRENT statement,
                 constructed in some way, for example,
                      'UPDATE TABLEX SET COL1 = 5
                       WHERE CURRENT OF CURSOR1'
              EXEC SQL PREPARE S2 FROM :VAR5 END-EXEC;
              EXEC SQL  EXECUTE S2  END-EXEC;
          ELSEIF a delete is desired THEN
              VAR5 := the DELETE WHERE CURRENT statement,
                 constructed in some way, for example,
                      'DELETE FROM TABLEX
                       WHERE CURRENT OF CURSOR1'
              EXEC SQL PREPARE S2 FROM :VAR5 END-EXEC;

              EXEC SQL EXECUTE S2 END-EXEC;
          ENDIF
     ENDDO
     EXEC SQL CLOSE CURSOR1;
 ENDIF
 ENDPROC HANDLEALL

Deal with any SQL return codes that indicate errors.