The DESCRIBE INPUT statement obtains information about the IN and INOUT parameter markers of a prepared statement.
For an explanation of prepared statements, see PREPARE.
This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™ or REXX.
None required. See PREPARE for the authorization required to create a prepared statement.
>>-DESCRIBE INPUT--statement-name-------------------------------> .-SQL-. .-LOCAL--. >--+-USING--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name-+->< | '-GLOBAL-' | '-INTO----descriptor-name-------------------------------------'
See GET DESCRIPTOR for an explanation of the information
that is placed in the SQL descriptor.
The seventh byte is set based on the parameter markers described:
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.
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 so that the first occurrence of SQLVAR contains a description of the first input parameter marker, the second occurrence of SQLVAR contains a description of the second input parameter marker, and so on. For information about the values assigned to SQLVAR occurrences, see Field descriptions in an occurrence of SQLVAR.
Allocating the SQL descriptor: Before the DESCRIBE INPUT statement is executed, the SQL descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. The number of descriptor items allocated must not be less than the number of input parameter markers or an error is returned.
Allocating the SQLDA: Before the DESCRIBE INPUT statement is executed, enough storage must be allocated for some number of SQLVAR occurrences. SQLN must then be set to the number of SQLVAR occurrences that were allocated. 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 distinct types, the number of occurrences of SQLVAR should be two times the number of input parameter markers. See Determining how many SQLVAR occurrences are needed for more information.
If not enough occurrences are provided to return all sets of occurrences, SQLN is set to the total number of occurrences necessary to return all information. Otherwise, SQLN is set to the number of input parameter markers.
Among the possible ways to allocate the SQLDA are the three described below:
This technique uses a large amount of storage that is never deallocated, even when most of this storage is not used for a particular prepared statement.
This technique allows better storage management than the first technique, but it doubles the number of DESCRIBE INPUT statements.
This technique is a compromise between the first two techniques. Its effectiveness depends on a good choice of size for the original SQLDA.
Example 1: In a C program, execute a DESCRIBE INPUT statement with an SQLDA that has enough to describe any number of input parameter markers 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.
EXEC SQL BEGIN DECLARE SECTION; char stmt1_str [200]; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLDA; struct sqlda initialsqlda; struct sqlda *sqldaPtr; … /* stmt1_str contains INSERT statement with VALUES */ /* clause */ EXEC SQL PREPARE STMT1_NAME FROM :stmt1_str; … /* code to set SQLN to five and to allocate the SQLDA */ EXEC SQL DESCRIBE INPUT STMT1_NAME INTO :SQLDA; …
Example 2: Allocate a descriptor called 'NEWDA' large enough to hold 20 item descriptor areas and use it on DESCRIBE INPUT.
EXEC SQL ALLOCATE DESCRIPTOR 'NEWDA' WITH MAX 20; EXEC SQL DESCRIBE INPUT STMT1 USING SQL DESCRIPTOR 'NEWDA';