The DESCRIBE statement obtains information about a prepared statement. For an explanation of prepared statements, see PREPARE.
Invocation
This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization
None required.
Syntax
>>-DESCRIBE--statement-name--INTO--descriptor-name-------------><
Description
When the DESCRIBE statement is executed, the database manager assigns values to the variables of the SQLDA as follows:
The seventh byte, called SQLDOUBLED, is set to '2' if the SQLDA contains two SQLVAR entries for every select-list item (or, column of the result table). This technique is used in order to accommodate LOB, distinct type, structured type, or reference type result columns. Otherwise, SQLDOUBLED is set to the space character.
The doubled flag is set to space if there is not enough room in the SQLDA to contain the entire DESCRIBE reply.
The eighth byte is set to the space character.
If the value is n, where n is greater than 0 but less than or equal to the value of SQLN, values are assigned to the first n occurrences of SQLVAR so that the first occurrence of SQLVAR contains a description of the first column of the result table, the second occurrence of SQLVAR contains a description of the second column of the result table, and so on. The description of a column consists of the values assigned to SQLTYPE, SQLLEN, SQLNAME, SQLLONGLEN, and SQLDATATYPE_NAME.
Basic SQLVAR
Secondary SQLVAR
These variables are only used if the number of SQLVAR entries are doubled to accommodate LOB, distinct type, structured type, or reference type columns.
See the Application Development Guide for more information on using locators and file reference variables with the SQLDA.
First Technique: Allocate an SQLDA with enough occurrences of SQLVAR to accommodate any select list that the application will have to process. If the table contains any LOB, distinct type, structured type, or reference type columns, the number of SQLVARs should be double the maximum number of columns; otherwise the number should be the same as the maximum number of columns. Having done the allocation, the application can use this SQLDA repeatedly.
This technique uses a large amount of storage that is never deallocated, even when most of this storage is not used for a particular select list.
Second Technique: Repeat the following two steps for every processed select list:
This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE statements.
Third Technique: Allocate an SQLDA that is large enough to handle most, and perhaps all, select lists but is also reasonably small. Execute DESCRIBE and check the SQLD value. Use the SQLD value for the number of occurrences of SQLVAR to allocate a larger SQLDA, if necessary.
This technique is a compromise between the first two techniques. Its effectiveness depends on a good choice of size for the original SQLDA.
Example
In a C program, execute a DESCRIBE statement with an SQLDA that has no occurrences of SQLVAR. If SQLD is greater than zero, use the value to allocate an SQLDA with the necessary number of occurrences of SQLVAR and then execute a DESCRIBE statement using that SQLDA.
EXEC SQL BEGIN DECLARE SECTION;
char stmt1_str[200];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLDA;
EXEC SQL DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;
... /* code to prompt user for a query, then to generate */
/* a select-statement in the stmt1_str */
EXEC SQL PREPARE STMT1_NAME FROM :stmt1_str;
... /* code to set SQLN to zero and to allocate the SQLDA */
EXEC SQL DESCRIBE STMT1_NAME INTO :sqlda;
... /* code to check that SQLD is greater than zero, to set */
/* SQLN to SQLD, then to re-allocate the SQLDA */
EXEC SQL DESCRIBE STMT1_NAME INTO :sqlda;
... /* code to prepare for the use of the SQLDA */
/* and allocate buffers to receive the data */
EXEC SQL OPEN DYN_CURSOR;
... /* loop to fetch rows from result table */
EXEC SQL FETCH DYN_CURSOR USING DESCRIPTOR :sqlda;
.
.
.