Like the other parts of the SQL language, dynamic SQL varies from one brand of DBMS to another. In fact, the differences in dynamic SQL support are more serious than for static SQL, because dynamic SQL exposes more of the nuts and bolts of the underlying DBMS—data types, data formats, and so on. As a practical matter, these differences make it impossible to write a single, general-purpose database front-end that is portable across different DBMS brands using dynamic SQL. Instead, database front-end programs must include a translation layer, often called a driver, for each brand of DBMS that they support to accommodate the differences.
The early front-end products usually shipped with a separate driver for each of the popular DBMS brands. The introduction of ODBC as a uniform SQL API layer made this job simpler, since an ODBC driver could be written once for each DBMS brand, and the front-end program could be written to solely use the ODBC interface. In practice, however, ODBC’s least-common-denominator approach meant that the front-end programs couldn’t take advantage of the unique capabilities of the various supported DBMS systems, and it limited the performance of the application. As a result, most modern front-end programs and tools still include a separate, explicit driver for each of the popular DBMS brands. An ODBC driver is usually included to provide access to the others.
A detailed description of the dynamic SQL features supported by all of the major DBMS brands is beyond the scope of this book. However, it is instructive to examine the dynamic SQL support provided by SQL/DS and by Oracle as examples of the kinds of differences and extensions to dynamic SQL that you may find in your particular DBMS.
1. Dynamic SQL in Oracle *
The Oracle DBMS preceded DB2 into the market and based its dynamic SQL support on IBM’s System/R prototype. For this reason, the Oracle support for dynamic SQL differs somewhat from the IBM SQL standard. Although Oracle and DB2 are broadly compatible, they differ substantially at the detail level. These differences include Oracle’s use of parameter markers, its use of the SQLDA, the format of its SQLDA, and its support for data type conversion. The Oracle differences from DB2 are similar to those you may encounter in other DBMS brands. For that reason, it is instructive to briefly examine Oracle’s dynamic SQL support and its points of difference from DB2.
1.1. Named Parameters
Recall that DB2 does not allow host variable references in a dynamically prepared statement. Instead, parameters in the statement are identified by question marks (parameter markers), and values for the parameters are specified in the EXECUTE or OPEN statement. Oracle allows you to specify parameters in a dynamically prepared statement using the syntax for host variables. For example, this sequence of embedded SQL statements is legal for Oracle:
exec sql begin declare section;
char stmtbuf[1001];
int employee_number;
exec sql end declare section;
.
.
.
strcpy(stmtbuf, “delete from salesreps
where empl_num = :rep_number;”);
exec sql prepare delstmt from :stmtbuf;
exec sql execute delstmt using :employee_number;
Although rep_number appears to be a host variable in the dynamic DELETE statement, it is in fact a named parameter. As shown in the example, the named parameter behaves exactly like the parameter markers in DB2. A value for the parameter is supplied from a real host variable in the EXECUTE statement. Named parameters are a real convenience when you use dynamic statements with a variable number of parameters.
2. The describe Statement
The Oracle DESCRIBE statement is used, like the DB2 DESCRIBE statement, to describe the query results of a dynamic query. Like DB2, Oracle returns the descriptions in a SQLDA. The Oracle DESCRIBE statement can also be used to request a description of the named parameters in a dynamically prepared statement. Oracle also returns these parameter descriptions in a SQLDA.
This Oracle DESCRIBE statement requests a description of the columns of query results from a previously prepared dynamic query:
exec sql describe select list for qrystmt into qry_sqlda;
It corresponds to the DB2 statement:
exec sql describe qrystmt into qry_sqlda;
This Oracle DESCRIBE statement requests a description of the named parameters in a previously prepared dynamic statement. The statement might be a query or some other SQL statement:
exec sql describe bind list for thestmt into the_sqlda;
This Oracle statement has no DB2 equivalent. Following this DESCRIBE statement, your program would typically examine the information in the SQLDA, fill in the pointers in the SQLDA to point to the parameter values the program wants to supply, and then execute the statement using the SQLDA form of the OPEN or EXECUTE statement:
exec sql execute thestmt using descriptor the_sqlda;
exec sql open qrycursor using descriptor the_sqlda;
The information returned by both forms of the Oracle DESCRIBE statement is the same, and is described in the next section.
3. The Oracle sqlda
The Oracle SQLDA performs the same functions as the DB2 SQLDA, but its format, shown in Figure 18-16, differs substantially from that of DB2. The two important fields in the DB2 SQLDA header both have counterparts in the Oracle SQLDA:
- The N field in the Oracle SQLDA specifies the size of the arrays used to hold column definitions. It corresponds to the SQLN field in the DB2 SQLDA.
- The F field in the Oracle SQLDA indicates how many columns are currently described in the arrays of the SQLDA. It corresponds to the SQLD field in the DB2 SQLDA.
Instead of DB2’s single array of SQLVAR structures that contain column descriptions, the Oracle SQLDA contains pointers to a series of arrays, each of which describes one aspect of a column:
- The T field points to an array of integers that specify the data type for each query results column or named parameter. The integers in this array correspond to the SQLTYPE field in each DB2 SQLVAR structure.
- The V field points to an array of pointers that specify the buffer for each column of query results or each passed parameter value. The pointers in this array correspond to the SQLDATA field in each DB2 SQLVAR structure.
- The L field points to an array of integers that specify the length of each buffer pointed to by the V array. The integers in this array correspond to the SQLLEN field in each DB2 SQLVAR structure.
- The I field points to an array of data pointers that specify the indicator variable for each query results column or named parameter. The pointers in this array correspond to the SQLIND field in each DB2 SQLVAR structure.
- The S field points to an array of string pointers that specify the buffers where Oracle is to return the name of each query results column or named parameter. The buffers pointed to by this array correspond to the SQLNAME structure in each DB2 SQLVAR structure.
- The M field points to an array of integers that specify the size of each buffer pointed to by the S array. For DB2, the SQLNAME structure has a fixed-length buffer, so there is no equivalent to the M field.
- The C field points to an array of integers that specify the actual lengths of the names pointed to by the S array. When Oracle returns the column or parameter names, it sets the integers in this array to indicate their actual lengths. For DB2, the SQLNAME structure has a fixed-length buffer, so there is no equivalent to the C field.
- The X field points to an array of string pointers that specify the buffers where Oracle is to return the name of each named indicator parameter. These buffers are used only by the Oracle DESCRIBE BLIND LIST statement; they have no DB2 equivalent.
- The Y field points to an array of integers specifying the size of each buffer pointed to by the X array. There is no DB2 equivalent.
- The Z field points to an array of integers specifying actual lengths of the indicator parameter names pointed to by the X array. When Oracle returns the indicator parameter names, it sets the integers in this array to indicate their actual lengths. There is no DB2 equivalent.
3.1. Data Type Conversions
The data type formats that DB2 uses to receive parameter values and return query results are those supported by the IBM S/370 architecture mainframes that run DB2. Because it was designed as a portable DBMS, Oracle uses its own internal data type formats. Oracle automatically converts between its internal data formats and those of the computer system on which it is running when it receives parameter values from your program and when it returns query results to your program.
Your program can use the Oracle SQLDA to control the data type conversion performed by Oracle. For example, suppose your program uses the DESCRIBE statement to describe the results of a dynamic query and discovers (from the data type code in the SQLDA) that the first column contains numeric data. Your program can request conversion of the numeric data by changing the data type code in the SQLDA before it fetches the data. If the program places the data type code for a character string into the SQLDA, for example, Oracle will convert the first column of query results and return it to your program as a string of digits.
The data type conversion feature of the Oracle SQLDA provides excellent portability, both across different computer systems and across different programming languages.
A similar feature is supported by several other DBMS brands, but not by the IBM SQL products.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.