A somewhat more complex facility is needed for executing a dynamically defined select-statement. Usually, a select-statement returns the result of a query into one or more host variables. When the query is read from a terminal at run time, however, you cannot know in advance how many and what type of variables to allocate to receive the result. The database manager therefore provides a special statement called DESCRIBE by which a program can obtain a description of the data types of a query result. After using the DESCRIBE statement, the program can dynamically allocate storage areas of the correct size and type to receive the result of the query. If DESCRIBE is used on a prepared SQL statement that was not a SELECT, the system indicates this by returning a zero in the variable SQLD of the SQL descriptor area.
When handling a run-time query, the program first uses the PREPARE statement which (as in the previous section) preprocesses the SQL statement. The PREPARE step also associates a statement-name with the query. The DESCRIBE statement is then used to obtain a description of the answer set. On the basis of this description, the program dynamically allocates a storage area suitable to hold one row of the result. The program then reads the query result by associating the name of the statement with a cursor and by using cursor manipulation statements (OPEN, FETCH, and CLOSE).
SELECT INTO statements cannot be executed dynamically.
Dynamically defined queries center around a structure called the SQL Descriptor Area (SQLDA).
The SQLDA is usually a based structure; that is, storage for it is allocated dynamically at run time. Figure 63 is a representation of the SQLDA structure with host-language-independent data type descriptions. Each host language has different considerations for the SQLDA structure; you should read the section on dynamic statements in the appropriate appendix before you attempt to code a program that uses the SQLDA. In addition, see Summarizing the Fields of the SQLDA for information about the fields of the SQLDA.
Figure 63. SQLDA Structure (in Pseudocode)
SQLDA -- a based structure composed of:
SQLDAID -- character string of length 8
SQLDABC -- 31-bit binary integer
SQLN -- 15-bit binary integer
SQLD -- 15-bit binary integer
SQLVAR -- an array composed of:
SQLTYPE -- 15-bit binary integer
SQLLEN -- 15-bit binary integer
SQLPRCSN -- 1-byte (used for DECIMAL)
SQLSCALE -- 1-byte (used for DECIMAL)
SQLDATA -- 31-bit binary integer (pointer)
SQLIND -- 31-bit binary integer (pointer)
SQLNAME -- varying-length character string
of up to 30 characters
|
| Note: | The SQLLEN field can be divided into two subfields. The subfields are used only when working with DECIMAL values. Such usage is described in the following section. |
To include the declaration of the descriptor area in an assembler, C, or PL/I program, specify:
INCLUDE SQLDA
The INCLUDE SQLDA statement must not be placed in the SQL declare section. As with the SQLCA, you can code this structure directly instead of using the INCLUDE SQLDA statement. If you choose to declare the structure directly, you can specify any name for it. For example, you can call it SPACE1 or DAREA instead of SQLDA.
To process a run-time query, you must declare the SQLDA structure. Below is an illustration showing the SQLDA structure as a box; similar illustrations are used in following examples. Remember that SQLDA is a based structure (or, in assembler, a DSECT); no storage has actually been allocated yet.
The meanings of the various fields are described as they are used. A summary of the meanings of the fields of the SQLDA is presented later for quick reference.
If a select-statement is assigned to the variable QSTRING, it can be read in from SYSIPT (DB2 Server for VSE) a terminal (DB2 Server for VM) or assigned within the program itself. In this example, the following select-statement is read in from the terminal:
SELECT DESCRIPTION, QONHAND FROM INVENTORY WHERE PARTNO = 221
This select-statement has no INTO clause. When it is read in, it is assigned to the host variable QSTRING, which is then preprocessed by the PREPARE statement:
READ QSTRING FROM TERMINAL EXEC SQL PREPARE S1 FROM :QSTRING
Now you can allocate storage for the SQLDA. The techniques for acquiring storage are language dependent; refer to the appropriate compiler or assembler manual.
| Note: | The usage of the SQLDA depends on the USING clause option of the DESCRIBE
statement (discussed later in this chapter). In this section, it is
assumed that the NAMES option of the USING clause has been specified.
The amount of storage you need to allocate depends upon how many elements you
want to have in the SQLVAR array. Each select_list item
must have a corresponding SQLVAR array element. Therefore, the number
of select_list items determines how many SQLVAR array elements you
should allocate. However, because select-statements are specified at
run time, it is not possible to know how many select_list items
there will be. Consequently, you must guess.
Suppose, in this example, that no more than three items are ever expected in the select_list. This means that the SQLVAR array should have a dimension of three, because each item in a select_list must have a corresponding entry in SQLVAR. |
Having allocated an SQLDA of what you hope will be adequate size, you must now initialize the SQLDA field called SQLN. SQLN is set to the number of SQLVAR array elements you have allocated (that is, SQLN is the dimension of the SQLVAR array). In this example, you must set SQLN to 3. Here's the pseudocode for what was done so far:
Allocate an SQLDA of size 3 SQLN = 3
Having allocated storage, you can now DESCRIBE the statement. (Make sure that SQLN is set before the DESCRIBE.)
DESCRIBE S1 INTO SQLDA
When the DESCRIBE is executed, the system places values in the SQLDA. These values provide information about the select_list.
Figure 64 shows the contents of the SQLDA after the DESCRIBE is executed for the example select-statement. The third SQLVAR element is not shown because it was not used.
Figure 64. Contents of SQLDA after Executing the DESCRIBE
The SQLDAID and SQLDABC fields are initialized by the system when a DESCRIBE statement is executed (you can ignore these for now).
If you do not allocate a large enough SQLDA structure, SQLD will be set to the number of required SQLVAR elements after the DESCRIBE. Suppose, for example, that the select-statement contained four select_list expressions instead of two. The SQLDA was allocated with an SQLVAR dimension of three. The system cannot describe the entire select_list because there is not enough storage. In this case, SQLD is set to the actual number of select_list expressions; the rest of the structure is ignored. Thus, after a DESCRIBE it is a good practice to check SQLN. If SQLN is less than SQLD, you need to allocate a larger SQLDA based on the value in SQLD:
EXEC SQL DESCRIBE S1 INTO SQLDA
IF (SQLN < SQLD)
Allocate a larger SQLDA using the value of SQLD.
Reset SQLN to the larger value.
EXEC SQL DESCRIBE S1 INTO SQLDA
END-IF
For the example select-statement, however, the SQLDA was of adequate size. SQLVAR has a dimension of three, and there are only two select_list expressions. SQLN remains set to 3, and SQLD is set to 2.
If you use DESCRIBE on a non-select-statement, SQLD is set to 0. If your program is designed to process both query and non-query statements, you can describe each statement (after it is prepared) to determine whether it is a query. This example routine is designed to process only query statements, so no test is provided.
Your program must now analyze the elements of SQLVAR. Remember that each element describes a single select_list expression. Consider again the select-statement that is being processed:
SELECT DESCRIPTION, QONHAND FROM INVENTORY WHERE PARTNO = 221
The first item in the select_list is DESCRIPTION. As illustrated in the beginning of this section, each SQLVAR element contains the fields SQLTYPE, SQLLEN, SQLDATA, SQLIND, and SQLNAME. The system returns a code in SQLTYPE that describes the data type of the expression and tells you whether nulls are applicable. For a detailed explanation on how to interpret the codes returned in SQLTYPE, refer to the DB2 Server for VSE & VM SQL Reference manual.
For example, SQLTYPE is set to 449 in the first SQLVAR element. This indicates that DESCRIPTION is a VARCHAR column and that nulls are permitted in the column.
The system sets SQLLEN to the length of the column. For character strings, SQLLEN is set to the maximum number of bytes of the string. For graphic strings, SQLLEN is set to the maximum number of double-byte characters in the string. For decimal data, the precision and scale are returned in the first and second bytes, respectively. (Recall that the SQLLEN field has two sub-fields called SQLPRCSN and SQLSCALE for this purpose.) For other data types, SQLLEN is set as follows:
SMALLINT -- SQLLEN = 2 INTEGER -- SQLLEN = 4 Single precision float -- SQLLEN = 4 Double precision float -- SQLLEN = 8 DATE -- SQLLEN = 10 or LOCAL TIME -- SQLLEN = 8 or LOCAL TIMESTAMP -- SQLLEN = 26
| Note: | For DATE, TIME, and TIMESTAMP, see Using Datetime Data Types. |
Because the data type of DESCRIPTION is VARCHAR, SQLLEN is set equal to the maximum length of the character string. For DESCRIPTION, that length is 24. When the select-statement is later executed, a storage area large enough to hold a VARCHAR(24) string will be needed. In addition, because nulls are permitted in DESCRIPTION, a storage area for a null indicator variable would also be needed.
For character and graphic string columns, the system puts the CCSID attribute of the column in bytes 3 and 4 of the SQLDATA field. In Figure 64, DESCRIPTION is a character column; therefore, the CCSID of DESCRIPTION is stored in the SQLDATA field of element 1. The example shows a CCSID of 500, which means that the data stored in the column is stored in CCSID 500 format.
For character string columns, the database manager stores an indicator in byte 1 of the SQLIND field. The indicator is set according to the subtype associated with the column. In Figure 64, the indicator for DESCRIPTION is set to X'01', which means that DESCRIPTION has a subtype of SBCS. Columns with a subtype of SBCS can contain single-byte character set characters only. For DB2 Server for VM, byte 1 is not set when DRDA protocol is in use.
The last field in an SQLVAR element is a varying-length character string called SQLNAME. The first two bytes of SQLNAME contain the length of the character data. The character data itself is usually the name of the field used in the select_list expression (DESCRIPTION in the above example). The exceptions to this are select_list items that are unnamed, such as functions (for example, SUM(SALARY)) and expressions (A+B-C). These exceptions are described in greater detail under Summarizing the Fields of the SQLDA.
The second SQLVAR element in the above example contains the information for the QONHAND select_list item. The 497 code in SQLTYPE indicates that QONHAND is an INTEGER column that permits nulls. For an INTEGER data type, SQLLEN is set to 4. SQLNAME contains the character string QONHAND, and has the length byte set to 7.
After analyzing the result of the DESCRIBE, you can allocate storage for variables that will contain the result of the select-statement. For DESCRIPTION, a varying character field of length 24 must be allocated; for QONHAND, a binary integer of 31 bits (plus sign) must be allocated. Both QONHAND and DESCRIPTION permit nulls, so you must allocate two additional halfwords to function as indicator variables.
After the storage is allocated, you must change the SQLDA. For each element of the SQLVAR array, do the following:
| Note: | When no override is present, the CCSID of the data area defaults to the application requester's default. |
In the following example, the SQLDA is updated to contain the appropriate addresses. Because a CCSID override is not required, the SQLNAME field is not modified. Here is what the structure now looks like:
This is the pseudocode for what was done so far:
EXEC SQL INCLUDE SQLDA
.
.
READ QSTRING FROM TERMINAL
EXEC SQL PREPARE S1 FROM :QSTRING
Allocate an SQLDA of size 3.
SQLN = 3
EXEC SQL DESCRIBE S1 INTO SQLDA
IF (SQLN < SQLD)
Allocate a larger SQLDA using the value of SQLD.
Reset SQLN to the larger value.
EXEC SQL DESCRIBE S1 INTO SQLDA
END-IF
Analyze the results of the DESCRIBE.
Allocate storage to hold select_list results.
Set SQLDATA and SQLIND for each select_list item.
Now comes the easy part: retrieving the query result. Dynamically defined queries, as noted earlier, must not have an INTO clause. Thus, all dynamically defined queries must use a cursor. Special forms of the DECLARE, OPEN, and FETCH statements are used for dynamically defined queries.
The DECLARE CURSOR statement for the example query is as follows:
DECLARE C1 CURSOR FOR S1
The only difference is that the name of the prepared select-statement (S1) is used instead of the select-statement.
The actual retrieval of result rows is as follows:
EXEC SQL OPEN C1
EXEC SQL FETCH C1 USING DESCRIPTOR SQLDA
DO WHILE (SQLCODE = 0)
DISPLAY (results pointed to by SQLDATA and SQLIND
for all pertinent SQLVAR elements)
EXEC SQL FETCH C1 USING DESCRIPTOR SQLDA
END-DO
DISPLAY ('END OF LIST')
EXEC SQL CLOSE C1
The cursor is opened, and the result table is evaluated. (Note that there are no input host variables needed for the example query. Methods of providing input host variables are discussed later.) The query result rows are then returned using a FETCH statement (which does not have output host variables in this example). This statement returns results into the data areas referenced in the descriptor called SQLDA. The same SQLDA that was set up by DESCRIBE is now being used for the output of the select-statement.
The next section describes a more general routine in which you can process queries that have parameters in the WHERE clause. You should not read that section until you have coded some of the simpler dynamic queries discussed thus far.
In the example above, the query that was dynamically executed had no parameters (input host variables) in the WHERE clause:
SELECT DESCRIPTION, QONHAND FROM INVENTORY WHERE PARTNO = 221
Suppose you wanted to execute the same query a number of times using different values for PARTNO. A parameterized SQL statement is needed:
SELECT DESCRIPTION, QONHAND FROM INVENTORY WHERE PARTNO = ?
In previous parameterized SQL statements, the number of parameters and their data types had to be known. What if they are unknown? The DESCRIBE statement, at first glance, is not feasible because it describes only select_lists. With some additional programming, however, you can use the DESCRIBE statement to obtain information about the parameter markers (?). Specifically, the code must scan the FROM and WHERE clauses to determine the table and column with which the parameter marker (?) is associated. The code can then construct a select-statement using those column names in the select_list. For the parameterized statement above, the following query can be generated:
SELECT PARTNO FROM INVENTORY
The query (assigned to WSTRING below) can then be preprocessed and described:
Allocate an SQLDA of size 3. SQLN = 3 EXEC SQL PREPARE S2 FROM :WSTRING EXEC SQL DESCRIBE S2 INTO SQLDA
Here is what the SQLDA looks like after the fabricated select-statement is described. Only the first element of SQLVAR is shown because the others are not used:
An analysis of the SQLDA shows that there is only one parameter marker (?), and that parameter is associated with PARTNO. The SQLTYPE value (500) indicates that PARTNO contains integer halfwords. Thus, you need to allocate a binary integer halfword for the parameter marker (?) variable. SQLDATA must then be set to point to this area.
Previously, the SQLDA was used in a FETCH statement, and query results were returned into the storage areas pointed to by SQLDATA and SQLIND. In other words, the SQLDA was used for output. Now, the SQLDA is going to be used to provide input values for the WHERE clause by an OPEN statement. When the SQLDA is being used for input, you must assign values to the dynamically allocated storage areas pointed to by SQLDATA. If the SQLTYPE value returned by DESCRIBE indicates that the field permits nulls, you must either supply an indicator variable pointed to by SQLIND, or reset SQLTYPE to indicate that nulls are not permitted. If indicator variables are not required, you should reset SQLTYPE. For example, if the SQLTYPE returned by DESCRIBE is 501, you should set it to 500 before using the SQLDA to provide input. After the storage for the parameter markers is allocated, you should read in values and assign them to those areas. Here is the completed SQLDA (assuming 221 is read in for the parameter marker (?)):
After an SQLDA is set up in this fashion, it can be referred to in an OPEN statement that contains a USING clause. For example, a previously declared cursor called C1 is opened using SQLDA:
OPEN C1 USING DESCRIPTOR SQLDA
Because SQLDA currently has 221 in the field pointed to by SQLDATA, C1 is evaluated using that value.
Figure 65 shows the pseudocode for the complete example. Two SQLDA-like structures are used. One is called SQLDA, and is the usual structure; the other (declared directly) is called SQLDA1. The fields of SQLDA1 are suffixed with a "1"; for example, SQLDATA1 and SQLN1. An asterisk in position 1 of the pseudocode denotes a comment.
Figure 65. Parameterized Query Statement
EXEC SQL INCLUDE SQLDA
Directly declare SQLDA1.
.
.
.
* Read in a parameterized query.
*
READ QSTRING FROM TERMINAL
*
* PREPARE and DESCRIBE the query; set up the output SQLDA.
*
EXEC SQL PREPARE S1 FROM :QSTRING
Allocate an SQLDA of size 3.
SQLN = 3
EXEC SQL DESCRIBE S1 INTO SQLDA
IF (SQLN < SQLD)
Allocate a larger SQLDA using the value of SQLD.
Reset SQLN to the larger value.
EXEC SQL DESCRIBE S1 INTO SQLDA
END-IF
Analyze the results of the DESCRIBE.
Allocate storage to hold select list results.
Set SQLDATA and SQLIND for each select_list item.
*
* Declare a cursor.
*
EXEC SQL DECLARE C1 CURSOR FOR S1
*
* Fabricate a query so PREPARE and DESCRIBE can be used to
* set up the input SQLDA1.
*
Scan the FROM clause and the WHERE clause of QSTRING for
parameter markers (?) and generate an appropriate
query in WSTRING.
Allocate an SQLDA1 of size 1 (1 was obtained from the scan).
SQLN1 = 1
EXEC SQL PREPARE S2 FROM :WSTRING
EXEC SQL DESCRIBE S2 INTO SQLDA1
Analyze the results of the DESCRIBE.
Reset SQLTYPE1 to reflect that there is no indicator variable.
Allocate storage to hold the input values (the parameter marker (?)
values).
Set SQLDATA1 for each parameter marker (?) value.
*
* Read in input parameters and retrieve the query results using
* cursor C1. Note that the pseudocode reads in only one parameter
* marker (?). Your actual code must provide for the possibility
* that more than one parameter marker (?) might be provided.
*
READ PARM FROM TERMINAL
DO WHILE (PARM ¬= 0)
Assign PARM to area pointed to by SQLDATA1.
EXEC SQL OPEN C1 USING DESCRIPTOR SQLDA1
EXEC SQL FETCH C1 USING DESCRIPTOR SQLDA
DO WHILE (SQLCODE = 0)
DISPLAY (results pointed to by SQLDATA and SQLIND)
EXEC SQL FETCH C1 USING DESCRIPTOR SQLDA
END-DO
EXEC SQL CLOSE C1
DISPLAY ('ENTER ANOTHER VALUE OR 0')
READ PARM FROM TERMINAL
END-DO
DISPLAY ('END OF QUERY')
|