The DESCRIBE statement obtains information about a prepared statement.
For an explanation of prepared statements, see PREPARE.
This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.
None required. See PREPARE for the authorization required to create a prepared statement.
.-OUTPUT-. >>-DESCRIBE--+--------+--statement-name-------------------------> .-SQL-. .-LOCAL--. >--+-USING--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name-+->< | '-GLOBAL-' | '-INTO----descriptor-name----+-------------------------+------' '-USING--+-NAMES--------+-' +-SYSTEM NAMES-+ +-LABELS-------+ +-ANY----------+ +-BOTH---------+ '-ALL----------'
If
the prepared statement is a
fullselect
or VALUES
INTO statement, the information returned describes the columns in
its result table. If the prepared statement is a CALL statement, the
information returned describes the OUT and INOUT parameters of the
procedure.
See GET DESCRIPTOR for an explanation of the information
that is placed in the SQL descriptor.
When the DESCRIBE statement is executed, the database manager assigns values to the variables of the SQLDA as follows:
The seventh byte is set based on the result columns described:
The seventh byte is set to the space character if there is not enough room in the SQLDA to contain the description of all result columns.
The eighth byte is set to the space character.
If the value of SQLD 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 (or parameter), the second occurrence of SQLVAR contains a description of the second column of the result table (or parameter), and so on. For information about the values assigned to SQLVAR occurrences, see Field descriptions in an occurrence of SQLVAR.
PREPARE INTO: Information about a prepared statement can also be obtained by using the INTO clause of the PREPARE statement.
Allocating the SQL descriptor: Before the DESCRIBE statement is executed, the SQL descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. If the number of descriptor items allocated is less than the number of result columns, a warning (SQLSTATE 01005) is returned.
Allocating the SQLDA: In C, COBOL, PL/I, and RPG, before the DESCRIBE or PREPARE INTO statement is executed, enough storage must be allocated for some number of SQLVAR occurrences. SQLN must then be set to the number of SQLVAR occurrences that were allocated. To obtain the description of the columns of the result table of a prepared SELECT statement, the number of occurrences of SQLVAR entries must not be less than the number of columns. Furthermore, if the columns include LOBs or distinct types, the number of occurrences of SQLVAR entries should be two times the number of columns. See Determining how many SQLVAR occurrences are needed for more information. Among the possible ways to allocate the SQLDA are the three described below:
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.
This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE statements.
This technique is a compromise between the first two techniques. Its effectiveness depends on a good choice of size for the original SQLDA.
Considerations for implicitly hidden
columns: A DESCRIBE OUTPUT statement only returns information
about implicitly hidden columns if the column (of a base table that
is defined as implicitly hidden) is explicitly specified as part of
the SELECT list of the final result table of the query described.
If implicitly hidden columns are not part of the result table of a
query, a DESCRIBE OUTPUT statement that returns information about
that query will not contain information about any implicitly hidden
columns.
In a C program, execute a DESCRIBE statement with an SQLDA that has no occurrences of SQLVAR entries. If SQLD is greater than zero, use the value to allocate an SQLDA with the necessary number of occurrences of SQLVAR entrires 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; struct sqlda initialsqlda; struct sqlda *sqldaPtr; 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 :initialsqlda; if (initialsqlda.sqld == 0); /* statement is a select-statement */ { … /* Code to allocate correct size SQLDA (sets sqldaPtr) */ if (strcmp(SQLSTATE,"01005") == 0) { sqldaPtr->sqln = 2*initialsqlda.sqld; SETSQLDOUBLED(sqldaPtr, SQLDOUBLED); } else { sqldaPtr->sqln = initialsqlda.sqld; SETSQLDOUBLED(sqldaPtr, SQLSINGLED); } EXEC SQL DESCRIBE STMT1_NAME INTO :*sqldaPtr; … /* code to prepare for the use of the SQLDA */ EXEC SQL OPEN DYN_CURSOR; … /* loop to fetch rows from result table */ EXEC SQL FETCH DYN_CURSOR USING DESCRIPTOR :*sqldaPtr; … } …