The
DESCRIBE OUTPUT statement obtains information about a prepared statement.
Invocation
This statement can only be embedded
in an application program. It is an executable statement that cannot
be dynamically prepared.
Authorization
None required.
Syntax
.-OUTPUT-.
>>-DESCRIBE--+--------+--statement-name------------------------->
>--INTO--descriptor-name---------------------------------------><
Description
- statement-name
- Identifies the prepared statement. When the DESCRIBE OUTPUT statement
is executed, the name must identify a statement that has been prepared
by the application process at the current server.
If the prepared
statement is a SELECT 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 output
parameters, defined as OUT or INOUT, of the procedure.
- INTO descriptor-name
- Identifies an SQL descriptor area (SQLDA). Before the DESCRIBE
OUTPUT statement is executed, the following variable in the SQLDA
must be set:
- SQLN
- Specifies the number of SQLVAR occurrences provided in the SQLDA.
SQLN must be set to a value greater than or equal to zero before the
DESCRIBE OUTPUT statement is executed.
When the DESCRIBE OUTPUT statement is executed,
the database manager assigns values to the variables of the SQLDA
as follows:
- SQLDAID
- The first 6 bytes are set to 'SQLDA ' (that is, 5 letters followed
by the space character).
The seventh byte, defined as SQLDOUBLED,
is set based on the results columns or parameter markers described:
- If the SQLDA contains two SQLVAR entries for every column or output
parameter, the seventh byte is set to '2'. This technique is used
to accommodate LOB, distinct type, structured type, or reference type
columns, or output parameters.
- Otherwise, the seventh byte is set to the space character.
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 or output parameter markers.
The eighth byte
is set to the space character.
- SQLDABC
- Length of the SQLDA in bytes.
- SQLD
- If the prepared statement is a SELECT, SQLD is set to the number
of columns in its result table. If the prepared statement is a CALL
statement, SQLD is set to the number of OUT and INOUT parameters of
the procedure. Otherwise, SQLD is set to 0.
- SQLVAR
- If the value of SQLD is 0, or greater than the value of SQLN,
no values are assigned to occurrences of SQLVAR.
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 SQLTYPE, SQLLEN, SQLNAME, SQLLONGLEN, and SQLDATATYPE_NAME for
the first n occurrences of SQLVAR. These values
describe either columns of the result table or parameter markers for
the output parameters of the procedure. The first occurrence of SQLVAR
describes the first column or output parameter marker, the second
occurrence of SQLVAR describes the second column or output parameter
marker, and so on.
- Base SQLVAR
- SQLTYPE
- A code showing the data type of the column or parameter and whether
or not it can contain null values.
- SQLLEN
- A length value depending on the data type of the column or parameter.
SQLLEN is 0 for LOB data types.
- SQLNAME
- The sqlname is derived as follows:
- If the SQLVAR corresponds to a derived column for a simple column
reference in the select list of a select-statement, sqlname is the
name of the column.
- If the SQLVAR corresponds to a parameter marker that is in the
parameter list of a procedure and is not part
of an expression, sqlname contains the name of the parameter
if one was specified on CREATE PROCEDURE.
- Otherwise sqlname contains an ASCII numeric literal value that
represents the SQLVAR's position within the SQLDA.
- Secondary SQLVAR
- These variables are only used if the number of SQLVAR entries
is doubled to accommodate LOB, distinct type, structured type, or
reference type columns or parameters.
- SQLLONGLEN
- The length attribute of a BLOB, CLOB, or DBCLOB column or parameter.
- SQLDATATYPE_NAME
- For any user-defined type (distinct or structured) column or parameter,
the database manager sets this to the fully qualified user-defined
type name. For a reference type column or parameter, the database
manager sets this to the fully qualified user-defined type name of
the target type of the reference. Otherwise, schema name is SYSIBM
and the type name is the name in the TYPENAME column of the SYSCAT.DATATYPES
catalog view.
Notes
- Before the DESCRIBE OUTPUT statement is executed, the value of
SQLN must be set to indicate how many occurrences of SQLVAR are provided
in the SQLDA and enough storage must be allocated to contain SQLN
occurrences. For example, to obtain the description of the columns
of the result table of a prepared SELECT statement, the number of
occurrences of SQLVAR must not be less than the number of columns.
- If a LOB of a large size is expected, then remember that manipulating
this large object will affect application memory. Given this condition,
consider using locators or file reference variables. Modify the SQLDA
after the DESCRIBE OUTPUT statement is executed but before allocating
storage so that an SQLTYPE of SQL_TYP_xLOB is changed to SQL_TYP_xLOB_LOCATOR
or SQL_TYP_xLOB_FILE with corresponding changes to other fields such
as SQLLEN. Then allocate storage based on SQLTYPE and continue.
- Code page conversions between extended UNIX code (EUC) code pages and DBCS code pages,
or between Unicode and non-Unicode code pages, can result in the expansion
and contraction of character lengths.
- If a structured type is being selected, but no FROM SQL transform
is defined (either because no TRANSFORM GROUP was specified using
the CURRENT DEFAULT TRANSFORM GROUP special register (SQLSTATE 428EM),
or because the named group does not have a FROM SQL transform function
defined (SQLSTATE 42744), an error is returned.
- Allocating the SQLDA: Three of the possible ways to allocate
the SQLDA are as follows:
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:
- Execute a DESCRIBE OUTPUT statement with an SQLDA that has no
occurrences of SQLVAR; that is, an SQLDA for which SQLN is zero. The
value returned for SQLD is the number of columns in the result table.
This is either the required number of occurrences of SQLVAR or half
the required number. Because there were no SQLVAR entries, a warning
with SQLSTATE 01005 will be issued. If the SQLCODE accompanying that
warning is equal to one of +237, +238 or +239, the number of SQLVAR
entries should be double the value returned in SQLD. (The return of
these positive SQLCODEs assumes that the SQLWARN bind option setting
was YES (return positive SQLCODEs). If SQLWARN was set to NO, +238
is still returned to indicate that the number of SQLVAR entries must
be double the value returned in SQLD.)
- Allocate an SQLDA with enough occurrences of SQLVAR. Then execute
the DESCRIBE OUTPUT statement again, using this new SQLDA.
This technique allows better storage management than
the first technique, but it doubles the number of DESCRIBE OUTPUT
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.
- Considerations for implicitly hidden columns: A
DESCRIBE OUTPUT statement returns only information about an implicitly
hidden column if the column is explicitly specified as part of the
SELECT list of the final result table of the query being 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.
Example
In a C program, execute a DESCRIBE
OUTPUT 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;
.
.
.