The SQL1 standard did not address dynamic SQL, so the de facto standard for dynamic SQL, as described in the preceding sections, was set by IBM’s implementation in DB2. The SQL2 standard explicitly included a standard for dynamic SQL, specified in a separate chapter of the standard that is nearly 50 pages long. In the simplest areas of dynamic SQL, the new SQL2 standard closely follows the dynamic SQL currently used by commercial DBMS products. But in other areas, including even the most basic dynamic SQL queries, the new standard introduces incompatibilities with existing DBMS products, which will require the rewriting of applications. The next several sections describe the SQL2 standard for dynamic SQL in detail, with an emphasis on the differences from the DB2-style dynamic SQL described in the preceding sections.
In practice, support for SQL2-style dynamic SQL is appearing slowly in commercial DBMS products, and most dynamic SQL programming still requires the use of the old, DB2-style dynamic SQL. Even when a new version of a DBMS product supports the new SQL2 statements, the DBMS vendor always provides a precompiler option that accepts the old dynamic SQL structure used by the particular DBMS. Often, this is the default option for the precompiler, because with thousands and thousands of SQL programs already in existence, the DBMS vendor has an absolute requirement that new DBMS versions do not break old programs. Thus, the migration to portions of SQL2 that represent incompatibilities with current practice will be a slow and evolutionary one.
1. Basic Dynamic SQL2 Statements
The SQL2 statements that implement basic dynamic SQL statement execution (that is, dynamic SQL that does not involve database queries) are shown in Figure 18-17. These statements closely follow the DB2 structure and language. This includes the single-step and two-step methods of executing dynamic SQL statements.
The SQL2 EXECUTE IMMEDIATE statement has an identical syntax and operation to that of its DB2 counterpart. It immediately executes the SQL statement passed to the DBMS as a character string. Thus, the EXECUTE IMMEDIATE statement in Figure 18-3 conforms to the SQL2 standard.
The SQL2 PREPARE and EXECUTE statements also operate identically to their DB2-style counterparts. The PREPARE statement passes a text string containing a SQL statement to the DBMS and causes the DBMS to analyze the statement, optimize it, and build an application plan for it. The EXECUTE statement causes the DBMS to actually execute a previously prepared statement. Like the DB2 version, the SQL2 EXECUTE statement optionally accepts host variables that pass the specific values to be used when executing the SQL statement. The PREPARE and EXECUTE statements in Figure 18-4 (called out as item 2) thus conform to the SQL2 standard.
Two useful extensions to the PREPARE/EXECUTE structure are a part of the Full compliance level SQL2 standard specification (neither is part of the Entry or Intermediate compliance levels). The first is a useful companion to the PREPARE statement that unprepares a previously compiled dynamic SQL statement. The DEALLOCATE PREPARE statement provides this capability. When the DBMS processes this statement, it can free the resources associated with the compiled statement, which will usually include some internal representation of the application plan for the statement. The statement named in the DEALLOCATE PREPARE statement must match the name specified in a previously executed PREPARE statement.
In the absence of a capability like that provided by DEALLOCATE PREPARE, the DBMS has no way of knowing whether a previously prepared statement will be executed again or not, and so must retain all of the information associated with the statement. In practice, some DBMS brands maintain the compiled version of the statement only until the end of a transaction; in these systems, a statement must be reprepared for each subsequent transaction where it is used. Because of the overhead involved in this process, other DBMS brands maintain the compiled statement information indefinitely. The DEALLOCATE PREPARE can play a more important role in these systems, where a database session might last for hours. Note, however, that the SQL2 standard explicitly says that whether a prepared statement is valid outside of the transaction in which it is prepared is implementation dependent.
The SQL2 extension to the DB2-style EXECUTE statement may be even more useful in practice. It allows the EXECUTE statement to be used to process simple singleton SELECT statements that return a single row of query results. Like the DB2 EXECUTE statement, the SQL2 statement includes a USING clause that names the host variables that supply the values for parameters in the statement being executed. But the SQL2 statement also permits an optional INTO clause that names the host variables that receive the values returned by a single-row query.
Suppose you have written a program that dynamically generates a query statement that retrieves the name and quota of a salesperson, with the salesperson’s employee number as an input parameter. Using DB2-style dynamic SQL, even this simple query involves the use of a SQLDA, cursors, a FETCH statement loop, and so on. Using SQL2 dynamic SQL, the statement can be executed using the simple two-statement sequence:
PREPARE qrystmt FROM :statement_buffer;
EXECUTE qrystmt USING :emplnum INTO :name, :quota;
As with any prepared statement, this single-row query could be executed repeatedly after being prepared once. It still suffers from the restriction that the number of returned columns, and their data types, must know when the program is written, since they must match exactly the number and data types of the host variables in the INTO clause. This restriction is removed by allowing the use of a SQLDA-style descriptor area instead of a list of host variables, as described in the next section.
2. SQL2 and the sqlda
Although its support for PREPARE/EXECUTE processing closely parallels that of DB2 dynamic SQL, the SQL2 standard diverges substantially from DB2 style in the area of dynamic query processing. In particular, the SQL2 standard includes major changes to the DB2 SQL Data Area (SQLDA), which is at the heart of dynamic multirow queries. Recall that a SQL Data Area (SQLDA) provides two important functions:
- A flexible way to pass parameters to be used in the execution of a dynamic SQL statement (passing data from the host program to the DBMS), as described earlier in the section “EXECUTE with SQLDA.”
- The way that the query results are returned to the program in the execution of a dynamic SQL query (passing data from the DBMS back to the host program), as described earlier in the section “The Dynamic FETCH Statement.”
The DB2-style SQLDA handles these functions with flexibility, but it has some serious disadvantages. It is a very low-level data structure, which tends to be specific to a particular programming language. For example, the variable-length structure of a DB2-style SQLDA makes it very difficult to represent in the FORTRAN language. The SQLDA structure also implicitly makes assumptions about the memory of the computer system on which the dynamic SQL program is running, how data items in a structure are aligned on such a system, and so on. For the writers of the SQL2 standard, these low- level dependencies were unacceptable barriers to portability. Therefore, they replaced the DB2 SQLDA structure with a set of statements for manipulating a more abstract structure called a dynamic SQL descriptor.
The structure of a SQL2 descriptor is shown in Figure 18-18. Conceptually, the SQL2 descriptor is parallel to, and plays exactly the same role as, the DB2-style SQLDA shown in Figure 18-7. The fixed part of the SQL2 descriptor specifies a count of the number of items in the variable part of the descriptor. Each item in the variable part contains information about a single parameter being passed, such as its data type, its length, an indicator telling whether a NULL value is being passed, and so on.
But unlike the DB2 SQLDA, the SQL2 descriptor is not an actual data structure within the host program. Instead, it is a collection of data items owned by the DBMS software. The host program manipulates SQL2 descriptors—creating them, destroying them, placing data items into them, extracting data from them—via a new set of dynamic SQL statements specially designed for that purpose. Figure 18-19 summarizes these SQL2 descriptor management statements.
To understand how the SQL2 descriptor management statements work, it’s instructive to reexamine the dynamic SQL update program in Figure 18-8. This program illustrates the use of a DB2-style SQLDA in an EXECUTE statement. The flow of the program remains identical if a SQL2 descriptor is used instead, but the specifics change quite a lot.
Before using the descriptor, the program must create it, using the statement:
ALLOCATE DESCRIPTOR parmdesc WITH MAX :parmcnt;
This statement replaces the allocation of storage for the parmda data structure at callout 1 in Figure 18-8. The descriptor (named parmdesc) will perform the same functions as the parmda. Note that the program in Figure 18-8 had to calculate how much storage would be required for the parmda structure before allocating it. With the SQL2 descriptor, that calculation is eliminated, and the host program simply tells the DBMS how many items the variable part of the descriptor must be able to hold.
The next step in the program is to set up the descriptor so that it describes the parameters to be passed—their data types, lengths, and so on. The loop at callout 2 of the program remains intact, but again, the details of how the descriptor is initialized differ from those for the SQLDA. At callout 3 and callout 4, the data type and length for the parameter are specified with a form of the SET DESCRIPTOR statement, with this code excerpt:
typecode = columns[i].typecode;
length = columns[i].buflen;
SET DESCRIPTOR parmdesc VALUE (:i + l) TYPE = :typecode
SET DESCRIPTOR parmdesc VALUE (:i + l) LENGTH = :length;
The differences from Figure 18-8 are instructive. Because the descriptor is maintained by the DBMS, the data type and length must be passed to the DBMS, through the SET DESCRIPTOR statement, using host variables. In this particular example, the simple variables typecode and length are used. Additionally, the data type codes in Figure 18-8 were specific to DB2. The fact that each DBMS vendor used different codes to represent different SQL data types was a major source of portability problems in dynamic SQL. The SQL2 standard specifies integer data type codes for all of the data types specified in the standard, eliminating this issue. The SQL2 data type codes are summarized in Table 18-2. So, in addition to the other changes, the data type codes in the columns structure of Figure 18-8 would need to be modified to use these SQL2 standard data type codes.
The statements at callouts 5 and 6 in Figure 18-8 were used to bind the SQLDA structure to the program buffers used to contain the parameter data and the corresponding indicator variable. Effectively, they put pointers to these program buffers into the SQLDA for the use of the DBMS. With SQL2 descriptors, this type of binding is not possible. Instead, the data value and indicator value are specifically passed as host variables, later in the program. Thus, the statements at callouts 5 and 6 would be eliminated in the conversion to SQL2.
The statement at callout 7 in Figure 18-8 sets the SQLDA to indicate how many parameter values are actually being passed to the DBMS. The SQL2 descriptor must similarly be set to indicate the number of passed parameters. This is done with a form of the SET DESCRIPTOR statement:
SET DESCRIPTOR parmdesc COUNT = :parmcnt;
Strictly speaking, this SET DESCRIPTOR statement should probably be placed earlier in the program and should be executed before those for the individual items. The SQL2 standard specifies a complete set of rules that describe how setting values in some parts of the descriptor causes values in other parts of the descriptor to be reset. For the most part, these rules simply specify the natural hierarchy of information.
For example, if you set the data type for a particular item to indicate an integer, the standard says that the corresponding information in the descriptor that tells the length of the same item will be reset to some implementation-dependent value. Normally this doesn’t impact your programming, but it does mean that you can’t assume that just because you set some value within the descriptor previously, that it still retains the same value. It’s best to fill the descriptor hierarchically, starting with higher-level information (for example, the number of items and their data types) and then proceeding to lower-level information (data type lengths, subtypes, whether NULL values are allowed, and so on).
The flow of the program in Figure 18-8 can now continue unmodified. The PREPARE statement compiles the dynamic UPDATE statement, and its form does not change for SQL2. The program then enters the for loop, prompting the user for parameters. Here again, the concepts are the same, but the details of manipulating the SQLDA structure and the SQL2 descriptor differ.
If the user indicates that a NULL value is to be assigned (by typing an asterisk in response to the prompt), the program in Figure 18-8 sets the parameter indicator buffer appropriately with the statement:
*(parmvar->sqlind) = -1;
and if the value is not NULL, the program again sets the indicator buffer with the statement:
*(parmvar->sqlind) = 0;
For the SQL2 descriptor, these statements would again be converted to a pair of SET DESCRIPTOR statements:
SET DESCRIPTOR parmdesc VALUE(:j + l) INDICATOR = -1;
SET DESCRIPTOR parmdesc VALUE (:j + 1) INDICATOR = 0;
Note again the use of the loop control variable to specify which item in the descriptor is being set, and the direct passing of data (in this case, constants) rather than the use of pointers to buffers in the SQLDA structure.
Finally, the program in Figure 18-8 passes the actual parameter value typed by the user to the DBMS, via the SQLDA. The statements at callout 8 accomplish this for data of different types, by first converting the typed characters into binary representations of the data and placing the binary data into the data buffers pointed to by the SQLDA. Again, the conversion to SQL2 involves replacing these pointers and direct SQLDA manipulation with a SET DESCRIPTOR statement. For example, these statements pass the data and its length for a variable-length character string:
length = strlen(inbuf);
SET DESCRIPTOR parmdesc VALUE (:j + 1) DATA = :inbuf;
SET DESCRIPTOR parmdesc VALUE (:j + 1) LENGTH = :length;
For data items that do not require a length specification, passing the data is even easier, since only the DATA form of the SET DESCRIPTOR statement is required. It’s also useful to note that SQL2 specifies implicit data type conversions between host variables (such as inbuf) and SQL data types. Following the SQL standard, it would be necessary for the program in Figure 18-8 to perform all of the data type conversion in the sscanf() functions. Instead, the data could be passed to the DBMS as character data, for automatic conversion and error detection.
With the SQLDA finally set up as required, the program in Figure 18-8 executes the dynamic UPDATE statement with the passed parameters at callout 9, using an EXECUTE statement that specifies a SQLDA. The conversion of this statement to a SQL2 descriptor is straightforward; it becomes:
EXECUTE updatestmt USING SQL DESCRIPTOR parmdesc;
The keywords in the EXECUTE statement change slightly, and the name of the descriptor is specified instead of the name of the SQLDA.
Finally, the program in Figure 18-8 should be modified like this to tell the DBMS to deallocate the SQL2 descriptor. The statement that does this is:
DEALLOCATE DESCRIPTOR parmdesc;
In a simple program like this one, the DEALLOCATE is not very necessary, but in a more complex real-world program with multiple descriptors, it’s a very good idea to deallocate the descriptors when the program no longer requires them.
3. SQL2 and Dynamic SQL Queries
In the dynamic SQL statements of the preceding sections, the SQL2 descriptor, like the SQLDA it replaces, is used to pass parameter information from the host program to the DBMS, for use in dynamic statement execution. The SQL2 standard also uses the SQL descriptor in dynamic query statements where, like the SQLDA it replaces, it controls the passing of query result from the DBMS back to the host program. Figure 18-9 lists a DB2-style dynamic SQL query program. It’s useful to examine how the program in Figure 18-9 would change to conform to the SQL2 standard. Again, the flow of the program remains identical under SQL2, but the specifics change quite a lot. The SQL2 forms of the dynamic SQL query-processing statements are shown in Figure 18-20.
The declaration of the cursor for the dynamic query, in callout 1 of Figure 18-9, remains unchanged under SQL2. The construction of the dynamic SELECT statement in callout 2 is also unchanged, as is the PREPARE statement of callout 3. The changes to the program begin at callout 4, where the program uses the DESCRIBE statement to obtain a description of the query results, which is returned in a SQLDA named qry_da. For SQL2, this DESCRIBE statement must be modified to refer to a SQL2 descriptor, which must have been previously allocated. Assuming the descriptor is named qrydesc, the statements would be:
ALLOCATE DESCRIPTOR qrydesc WITH MAX :colcount;
DESCRIBE querystmt USING SQL DESCRIPTOR qrydesc;
The SQL2 form of the DESCRIBE statement has a parallel effect on the one it replaces. Descriptions of the query result columns are returned, column by column, into the SQL2 descriptor, instead of into the SQLDA. Because the descriptor is a DBMS structure, rather than an actual data structure in the program, the host program must retrieve the information from the descriptor, piece by piece, as required. The GET DESCRIPTOR statement performs this function, just as the SET DESCRIPTOR function performs the opposite function of putting information into the SQL2 descriptor. In the program of Figure 18-9, the statements at callout 5, which obtains the length of a particular column of query results from a SQLDA, would be replaced with this statement:
GET DESCRIPTOR qrydesc VALUE (:i + 1) :length = LENGTH;
qry_var -> sqldat = malloc(length);
The statement at callout 5 that allocates buffers for each item of query results is still needed, but the method for telling the DBMS where to put the results changes for SQL2. Instead of placing the address of the program destination for each item into the SQLDA, the program must place these addresses into the SQL2 descriptor, using the SET DESCRIPTOR statement. The buffers for the indicator variables are not needed with the SQL2 descriptor. Instead, the information about whether a column contains a NULL value can be obtained from the descriptor for each row as it is fetched, as seen later in the program example.
In this particular example, the number of columns in the query results are calculated by the program as it builds the query. The program could also obtain the number of columns from the SQL2 descriptor with this form of the GET DESCRIPTOR statement:
GET DESCRIPTOR qrydesc :colcount = COUNT;
Having obtained the description of the query results, the program performs the query by opening the cursor at callout 6. The simple form of the OPEN statement, without any input parameters, conforms to the SQL2 standard. If the dynamic query specified parameters, they could be passed to the DBMS either as a series of host variables or via a SQL2 descriptor. The SQL2 OPEN statement using host variables is identical to the DB2 style, shown in the program in Figure 18-13. The SQL2 OPEN statement using a descriptor is parallel to the SQL2 EXECUTE statement using a descriptor, and differs from the DB2 style. For example, the OPEN statement of Figure 18-14:
OPEN qrycursor USING DESCRIPTOR :parmda;
is changed for SQL2 into this OPEN statement:
OPEN qrycursor USING SQL DESCRIPTOR parmdesc;
The technique for passing input parameters to the OPEN statement via the SQL2 descriptor is exactly the same as that described earlier for the EXECUTE statement.
Like the Oracle implementation of dynamic SQL, the SQL2 standard provides a way for the host program to obtain a description of the parameters in a dynamic query as well as a description of the query results. For the program fragment in Figure 18-14, this DESCRIBE statement:
DESCRIBE INPUT querystmt USING SQL DESCRIPTOR parmdesc;
will return, in the SQL2 descriptor named parmdesc, a description of each of the parameters that appears in the dynamic query. The number of parameters can be obtained with the GET DESCRIPTOR statement, retrieving the COUNT item from the descriptor. As with the Oracle implementation, the SQL2 standard can have two descriptors associated with a dynamic query. The input descriptor, obtained with the DESCRIBE INPUT statement, contains descriptions of the parameters. The output descriptor contains descriptions of the query results columns. The standard allows you to explicitly ask for the output description:
DESCRIBE OUTPUT querystmt USING SQL DESCRIPTOR qrydesc;
but the DESCRIBE OUTPUT form of the statement is the default, and the most common practice is to omit the keyword OUTPUT.
Returning to the dynamic query example of Figure 18-9, the cursor has been opened at callout 7, and it’s time to fetch rows of query results at callout 8. Again, the SQL2 form of the FETCH statement is slightly modified to use the SQL2-style descriptor:
FETCH sqlcurs USING SQL DESCRIPTOR qrydesc;
The FETCH statement advances the cursor to the next row of query results and brings the values for that row into the program buffers, as specified within the descriptor structure. The program must still use the descriptor to determine information about each column of returned results, such as its length or whether it contains a NULL value. For example, to determine the returned length of a column of character data, the program might use the statement:
GET DESCRIPTOR qrydesc VALUE(:i + 1) :length = RETURNED_LENGTH;
To determine whether the value in the column was NULL, the program can use the statement:
GET DESCRIPTOR qrydesc VALUE(:i + 1) :indbuf = INDICATOR;
and similarly to determine the data type of the column, the program can use the statement:
GET DESCRIPTOR qrydesc VALUE(:i + 1) :type = TYPE;
As you can see, the details of row-by-row query processing within the for loop of the program will differ dramatically from those in Figure 18-9.
Having processed all rows of query results, the program closes the cursor at callout 8. The CLOSE statement remains unchanged under SQL2. Following the closing of the cursor, it would be good practice to deallocate the SQL2 descriptor(s), which would have been allocated at the very beginning of the program.
The changes required to the dynamic SQL programs in Figures 18-8, 18-9, and 18-14 to make them conform to the SQL2 standard illustrate, in detail, the new features specified by the standard and the degree to which they differ from common dynamic SQL usage today. In summary, the changes from DB2-style dynamic SQL are:
- The SQLDA structure is replaced with a named SQL2 descriptor.
- The ALLOCATE DESCRIPTOR and DEALLOCATE DESCRIPTOR statements are used to create and destroy descriptors, replacing allocation and deallocation of host program SQLDA data structures.
- Instead of directly manipulating elements of the SQLDA, the program specifies parameter values and information through the SET DESCRIPTOR statement.
- Instead of directly manipulating elements of the SQLDA, the program obtains information about query results and obtains the query result data itself through the GET DESCRIPTOR statement.
- The DESCRIBE statement is used both to obtain descriptions of query results (DESCRIBE OUTPUT) and to obtain descriptions of parameters (DESCRIBE INPUT).
- The EXECUTE, OPEN, and FETCH statements are slightly modified to specify the SQL2 descriptor by name instead of the SQLDA.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.