DESCRIBE INPUT statement

The DESCRIBE INPUT statement obtains information about the input parameter markers of 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

>>-DESCRIBE INPUT--statement-name--INTO--descriptor-name-------><

Description

statement-name

Identifies the prepared statement. When the DESCRIBE INPUT statement is executed, the name must identify a statement that has been prepared by the application process at the current server.

For a CALL statement, the information returned describes the input parameters, defined as IN or INOUT, of the procedure. Input parameter markers are always considered nullable, regardless of usage.

INTO descriptor-name

Identifies an SQL descriptor area (SQLDA). Before the DESCRIBE INPUT 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 INPUT statement is executed.

Notes

• Preparing the SQLDA: Before the DESCRIBE INPUT statement is executed, the SQLDA must be allocated and the value of SQLN must be set to a value greater than or equal to zero to indicate how many occurrences of SQLVAR are provided in the SQLDA. Enough storage must be allocated to contain SQLN occurrences. To obtain the description of the input parameter markers in the prepared statement, the number of occurrences of SQLVAR must not be less than the number of input parameter markers. Furthermore, if the input parameter markers include LOBs or structured types, the number of occurrences of SQLVAR should be two times the number of input parameter markers.
• 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 expansion or 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:  Among the possible ways to allocate the SQLDA are the three described below.

  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 INPUT 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 statement again, using this new SQLDA.

  This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE INPUT 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 INPUT 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

       /* STMT1_STR contains INSERT statement with VALUES clause */
   EXEC SQL PREPARE STMT1_NAME FROM :STMT1_STR;
   ... /* code to set SQLN to 5 and to allocate the SQLDA        */
   EXEC SQL DESCRIBE INPUT STMT1_NAME INTO :SQLDA;
   .
   .
   .