Dynamic SQL: Dynamic Queries

The EXECUTE IMMEDIATE, PREPARE, and EXECUTE statements as described thus far support dynamic execution of most SQL statements. However, they can’t support dynamic queries because they lack a mechanism for retrieving the query results. To support dynamic queries, SQL combines the dynamic SQL features of the PREPARE and EXECUTE statements with extensions to the static SQL query-processing statements, and adds a new statement. Here is an overview of how a program performs a dynamic query:

1. A dynamic version of the DECLARE CURSOR statement declares a cursor for the query. Unlike the static DECLARE CURSOR statement, which includes a hard-coded SELECT statement, the dynamic form of the DECLARE CURSOR statement specifies the statement name that will be associated with the dynamic SELECT statement.
2. The program constructs a valid SELECT statement in a buffer, just as it would construct a dynamic UPDATE or DELETE statement. The SELECT statement may contain parameter markers like those used in other dynamic SQL statements.
3. The program uses the PREPARE statement to pass the statement string to the DBMS, which parses, validates, and optimizes the statement and generates an application plan. This is identical to the PREPARE processing used for other dynamic SQL statements.
4. The program uses the DESCRIBE statement to request a description of the query results that will be produced by the query. The DBMS returns a column-by-column description of the query results in a SQL Data Area (SQLDA) supplied by the program, telling the program how many columns of query results there are, and the name, data type, and length of each column. The DESCRIBE statement is used exclusively for dynamic queries.
5. The program uses the column descriptions in the SQLDA to allocate a block of memory to receive each column of query results. The program may also allocate space for an indicator variable for the column. The program places the address of the data area and the address of the indicator variable into the SQLDA to tell the DBMS where to return the query results.
6. A dynamic version of the OPEN statement asks the DBMS to start executing the query and passes values for the parameters specified in the dynamic SELECT statement. The OPEN statement positions the cursor before the first row of query results.
7. A dynamic version of the FETCH statement advances the cursor to the first row of query results and retrieves the data into the program’s data areas and indicator variables. Unlike the static FETCH statement, which specifies a list of host variables to receive the data, the dynamic FETCH statement uses the SQLDA to tell the DBMS where to return the data. Subsequent FETCH statements move through the query results row by row, advancing the cursor to the next row of query results and retrieving its data into the program’s data areas.
8. The CLOSE statement ends access to the query results and breaks the association between the cursor and the query results. This CLOSE statement is identical to the static SQL CLOSE statement; no extensions are required for dynamic queries.

The programming required to perform a dynamic query is more extensive than the programming for any other embedded SQL statement. However, the programming is typically more tedious than complex. Figure 18-9 shows a small query program that uses dynamic SQL to retrieve and display selected columns from a user-specified table. The callouts in the figure identify the eight steps in the preceding list.

The program in the figure begins by prompting the user for the table name and then queries the system catalog to discover the names of the columns in that table. It asks the user to select the column(s) to be retrieved and constructs a dynamic SELECT statement based on the user’s responses. The step-by-step mechanical construction of a select list in this example is very typical of database front-end programs that generate dynamic SQL. In real applications, the generated select list might include expressions or aggregate functions, and there might be additional program logic to generate GROUP BY, HAVING, and ORDER BY clauses. A graphical user interface would also be used instead of the primitive user prompting in the sample program. However, the programming steps and concepts remain the same. Notice that the generated SELECT statement is identical to the interactive SELECT statement that you would use to perform the requested query.

The handling of the PREPARE and DESCRIBE statements and the method of allocating storage for the retrieved data in this program are also typical of dynamic query programs. Note how the program uses the column descriptions placed in the SQLVAR array to allocate a data storage block of the proper size for each column. This program also allocates space for an indicator variable for each column. The program places the address of the data block and indicator variable back into the SQLVAR structure.

The OPEN, FETCH, and CLOSE statements play the same role for dynamic queries as they do for static queries, as illustrated by this program. Note that the FETCH statement specifies the SQLDA instead of a list of host variables. Because the program has previously filled in the SQLDATA and SQLIND fields of the SQLVAR array, the DBMS knows where to place each retrieved column of data.

As this example shows, much of the programming required for a dynamic query is concerned with setting up the SQLDA and allocating storage for the SQLDA and the retrieved data. The program must also sort out the various types of data that can be returned by the query and handle each one correctly, taking into account the possibility that the returned data will be NULL. These characteristics of the sample program are typical of production applications that use dynamic queries. Despite the complexity, the programming is not too difficult in languages like C, C++, Pascal, PL/I, or Java. Languages such as COBOL and FORTRAN, which lack the capability to dynamically allocate storage and work with variable-length data structures, cannot be used for dynamic query processing.

The following sections discuss the DESCRIBE statement and the dynamic versions of the DECLARE CURSOR, OPEN, and FETCH statements.

1. The describe Statement

The DESCRIBE statement, shown in Figure 18-10, is unique to dynamic queries. It is used to request a description of a dynamic query from the DBMS. The DESCRIBE statement is used after the dynamic query has been compiled with the PREPARE statement but before it is executed with the OPEN statement. The query to be described is identified by its statement name. The DBMS returns the query description in a SQLDA supplied by the program.

The SQLDA is a variable-length structure with an array of one or more SQLVAR structures, as described earlier in the section “EXECUTE with SQLDA,” and shown in Figure 18-7. Before passing the SQLDA to the DESCRIBE statement, your program must fill in the SQLN field in the SQLDA header, telling the DBMS how large the SQLVAR array is in this particular SQLDA. As the first step of its DESCRIBE processing, the DBMS fills in the SQLD field in the SQLDA header with the number of columns of query results. If the size of the SQLVAR array (as specified by the SQLN field) is too small to hold all of the column descriptions, the DBMS does not fill in the remainder of the SQLDA. Otherwise, the DBMS fills in one SQLVAR structure for each column of query results, in left-to-right order. The fields of each SQLVAR describe the corresponding column:

• The SQLNAME structure specifies the name of the column (with the name in the DATA field and the length of the name in the LENGTH field). If the column is derived from an expression, the SQLNAME field is not used.
• The SQLTYPE field specifies an integer data type code for the column. The data type codes used by different brands of DBMS vary. For the IBM SQL products, the data type code indicates both the data type and whether NULL values are allowed, as shown in Table 18-1.
• The SQLLEN field specifies the length of the column. For variable-length data types (such as VARCHAR), the reported length is the maximum length of the data; the length of the columns in individual rows of query results will not exceed this length. For DB2 (and many other SQL products), the length returned for a DECIMAL data type specifies both the size of the decimal number (in the upper byte) and the scale of the number (in the lower byte).
• The SQLDATA and SQLIND fields are not filled in by the DBMS. Your application program fills in these fields with the addresses of the data buffer and indicator variable for the column before using the SQLDA later in a FETCH statement.

A complication of using the DESCRIBE statement is that your program may not know in advance how many columns of query results there will be, and therefore, it may not know how large a SQLDA must be allocated to receive the description. One of three strategies is typically used to ensure that the SQLDA has enough space for the returned descriptions.

• If the program has generated the select list of the query, it can keep a running count of the select items as it generates them. In this case, the program can allocate a SQLDA with exactly the right number of SQLVAR structures to receive the column descriptions. This approach was used in the program shown in Figure 18-9.
• If it is inconvenient for the program to count the number of select list items, it can initially DESCRIBE the dynamic query into a minimal SQLDA with a one-element SQLVAR array. When the DESCRIBE statement returns, the SQLD value tells the program how large the SQLDA must be. The program can then allocate a SQLDA of the correct size and reexecute the DESCRIBE statement, specifying the new SQLDA. There is no limit to the number of times that a prepared statement can be described.
• Alternatively, the program can allocate a SQLDA with a SQLVAR array large enough to accommodate a typical query. A DESCRIBE statement using this SQLDA will succeed most of the time. If the SQLDA turns out to be too small for the query, the SQLD value tells the program how large the SQLDA must be, and it can allocate a larger one and DESCRIBE the statement again into that SQLDA.

The DESCRIBE statement is normally used for dynamic queries, but you can ask the DBMS to DESCRIBE any previously prepared statement. This feature is useful, for example, if a program needs to process an unknown SQL statement typed by a user. The program can PREPARE and DESCRIBE the statement and examine the SQLD field in the SQLDA. If the SQLD field is zero, the statement text was not a query, and the EXECUTE statement can be used to execute it. If the SQLD field is positive, the statement text was a query, and the OPEN/FETCH/CLOSE statement sequence must be used to execute it.

2. The declare cursor Statement

The dynamic DECLARE CURSOR statement, shown in Figure 18-11, is a variation of the static DECLARE CURSOR statement. Recall from Chapter 17 that the static DECLARE CURSOR statement literally specifies a query by including the SELECT statement as one of its clauses. By contrast, the dynamic DECLARE CURSOR statement specifies the query indirectly, by specifying the statement name associated with the query by the PREPARE statement.

Like the static DECLARE CURSOR statement, the dynamic DECLARE CURSOR statement is a directive to the SQL precompiler rather than an executable statement. It must appear before any other references to the cursor that it declares. The cursor name declared by this statement is used in subsequent OPEN, FETCH, and CLOSE statements to process the results of the dynamic query.

3. The Dynamic open Statement

The dynamic OPEN statement, shown in Figure 18-12, is a variation of the static OPEN statement. It causes the DBMS to begin executing a query and positions the associated cursor just before the first row of query results. When the OPEN statement completes successfully, the cursor is in an open state and is ready to be used in a FETCH statement.

The role of the OPEN statement for dynamic queries parallels the role of the EXECUTE statement for other dynamic SQL statements. Both the EXECUTE and the OPEN statements cause the DBMS to execute a statement previously compiled by the PREPARE statement. If the dynamic query text includes one or more parameter markers, then the OPEN statement, like the EXECUTE statement, must supply values for these parameters. The USING clause is used to specify parameter values, and it has an identical format in both the EXECUTE and OPEN statements.

If the number of parameters that will appear in a dynamic query is known in advance, the program can pass the parameter values to the DBMS through a list of host variables in the USING clause of the OPEN statement. As in the EXECUTE statement, the number of host variables must match the number of parameters, the data type of each host variable must be compatible with the type required by the corresponding parameter, and an indicator variable can be specified for each host variable, if necessary. Figure 18-13 shows a program excerpt where the dynamic query has three parameters whose values are specified by host variables.

If the number of parameters is not known until runtime, the program must pass the parameter values using a SQLDA structure. This technique for passing parameter values was described for the EXECUTE statement earlier in the section “EXECUTE with SQLDA.” The same technique is used for the OPEN statement. Figure 18-14 shows a program excerpt like the one in Figure 18-13, except that it uses a SQLDA to pass parameters.

Note carefully that the SQLDA used in the OPEN statement has absolutely nothing to do with the SQLDA used in the DESCRIBE and FETCH statements:

• The SQLDA in the OPEN statement is used to pass parameter values to the DBMS for dynamic query execution. The elements of its SQLVAR array correspond to the parameter markers in the dynamic statement text.
• The SQLDA in the DESCRIBE and FETCH statements receives descriptions of the query results columns from the DBMS and tells the DBMS where to place the retrieved query results. The elements of its SQLVAR array correspond to the columns of query results produced by the dynamic query.

4. The Dynamic fetch Statement

The dynamic FETCH statement, shown in Figure 18-15, is a variation of the static FETCH statement. It advances the cursor to the next available row of query results and retrieves the values of its columns into the program’s data areas. Recall from Chapter 17 that the static FETCH statement includes an INTO clause with a list of host variables that receive the retrieved column values. In the dynamic FETCH statement, the list of host variables is replaced by a SQLDA.

Before using the dynamic FETCH statement, it is the application program’s responsibility to provide data areas to receive the retrieved data and indicator variable for each column. The application program must also fill in the SQLDATA, SQLIND, and SQLLEN fields in the SQLVAR structure for each column, as follows:

• The SQLDATA field must point to the data area for the retrieved data.
• The SQLLEN field must specify the length of the data area pointed to by the SQLDATA field. This value must be correctly specified to make sure the DBMS does not copy retrieved data beyond the end of the data area.
• The SQLIND field must point to an indicator variable for the column (a 2-byte integer). If no indicator variable is used for a particular column, the SQLIND field for the corresponding SQLVAR structure should be set to zero.

Normally, the application program allocates a SQLDA, uses the DESCRIBE statement to get a description of the query results, allocates storage for each column of query results, and sets the SQLDATA and SQLIND values, all before opening the cursor. This same SQLDA is then passed to the FETCH statement. However, there is no requirement that the same SQLDA be used or that the SQLDA specify the same data areas for each FETCH statement. It is perfectly acceptable for the application program to change the SQLDATA and SQLIND pointers between FETCH statements, retrieving two successive rows into different locations.

5. The Dynamic close Statement

The dynamic form of the CLOSE statement is identical in syntax and function to the static CLOSE statement shown in Figure 17-25. In both cases, the CLOSE statement ends access to the query results. When a program closes a cursor for a dynamic query, the program normally should also deallocate the resources associated with the dynamic query, including:

• The SQLDA allocated for the dynamic query and used in the DESCRIBE and FETCH statements
• A possible second SQLDA, used to pass parameter values to the OPEN statement
• The data areas allocated to receive each column of query results retrieved by a FETCH statement
• The data areas allocated as indicator variables for the columns of query results

It may not be necessary to deallocate these data areas if the program will terminate immediately after the CLOSE statement.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.