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

Read syntax diagramSkip visual syntax diagramDESCRIBE INPUTstatement-nameINTOdescriptor-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.
When the DESCRIBE INPUT 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 parameter markers described:
  • If the SQLDA contains two SQLVAR entries for every input parameter, the seventh byte is set to '2'. This technique is used to accommodate LOB or structured type input 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 input parameter markers.

The eighth byte is set to the space character.

SQLDABC
Length of the SQLDA in bytes.
SQLD
The number of IN and INOUT parameters of the procedure.
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 the first n occurrences of SQLVAR. The values describe parameter markers for the input parameters of the procedure. The first occurrence of SQLVAR describes the first input parameter marker, the second occurrence of SQLVAR describes the second input parameter marker, and so on.

Base SQLVAR
SQLTYPE
A code showing the data type of the parameter and whether or not it can contain null values.
SQLLEN
A length value depending on the data type of the parameter. SQLLEN is 0 for LOB data types.
SQLNAME
The sqlname is derived as follows:
  • 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 the CREATE PROCEDURE statement.
  • If the SQLVAR corresponds to a named parameter marker, sqlname contains the name of the parameter marker.
  • 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 are doubled to accommodate LOB, distinct type, structured type, or reference type parameters.
SQLLONGLEN
The length attribute of a BLOB, CLOB, or DBCLOB parameter.
SQLDATATYPE_NAME
For any user-defined type (distinct or structured) parameter, the database manager sets this to the fully qualified user-defined type name. For a reference type 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

  • 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:  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 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

Execute a DESCRIBE INPUT statement with an SQLDA that has enough SQLVAR occurrences to describe any number of input parameters a prepared statement might have. Assume that five parameter markers at most will need to be described and that the input data does not contain LOBs.
       /* 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;
   .
   .
   .
This example uses the first technique described under Allocating the SQLDA in DESCRIBE OUTPUT.