DESCRIBE OUTPUT 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.

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:

  1. 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.)
  2. 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

   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;
   .
   .
   .