SQL Reference

DESCRIBE

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

statement-name
Identifies the statement about which information is required. When the DESCRIBE statement is executed, the name must identify a prepared statement.

INTO descriptor-name
Identifies an SQL descriptor area (SQLDA), which is described in Appendix C, SQL Descriptor Area (SQLDA). Before the DESCRIBE statement is executed, the following variables in the SQLDA must be set:

SQLN
Indicates the number of variables represented by SQLVAR. (SQLN provides the dimension of the SQLVAR array.) SQLN must be set to a value greater than or equal to zero before the DESCRIBE statement is executed.

When the DESCRIBE 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, 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.

SQLDABC
Length of the SQLDA.

SQLD
If the prepared statement is a SELECT, the number of columns in its result table; otherwise, 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 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

SQLTYPE
A code showing the data type of the column and whether or not it can contain null values.

SQLLEN
A length value depending on the data type of the result columns. SQLLEN is 0 for LOB data types.

SQLNAME
If the derived column is not a simple column reference, then sqlname contains an ASCII numeric literal value, which represents the derived column's original position within the select list; otherwise, sqlname contains the name of the column.

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.

SQLLONGLEN
The length attribute of a BLOB, CLOB, or DBCLOB column.

SQLDATATYPE_NAME
For any user-defined type (distinct or structured) column, the database manager sets this to the fully qualified user-defined type name. For a reference type column, 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

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


Footnotes:

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


[ Top of Page | Previous Page | Next Page ]