SQL DESCRIBE PARAMETER Statement

< > Status Inquiry Statements Query Statements Example Flow Chart Table of Contents

The SQL DESCRIBE PARAMETER statement returns a group that describes a parameter of a query.

QueryHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the query for which a parameter is to be described. This value must have been returned by a successful SQL PREPARE QUERY statement.

ParamNumberN (input). This argument must specify a numeric integer value. The value identifies the parameter number of a parameter marker in the SQL string of the prepared query that is to be described. Parameter markers are numbered from left to right starting with 1.

ParamDescGroupN (output). This argument must refer to a group data item that has the same data description as sql-Param-Description, sql-Column-Description, or sql-Column-Description-Ext defined in the copy file lisqldef.cpy. The description of the parameter is stored in this group data item. If the argument refers to either an sql-Column-Description or an sql-Column-Description-Ext group, the data items in the group that do not apply to a parameter are set to default values (zero for numeric values and space for string values).

Parameters in a query are denoted by parameter markers, the ? character, in the SQL statement text string for the query. Parameter markers may generally be placed anywhere an SQL literal may be placed in the statement. The return value for a procedure must be specified as a parameter. Also, interoperable applications should always use parameter markers for procedure parameters, because some data sources do not accept literal procedure parameter values. Parameters are numbered, starting with 1, as the parameter markers are placed from left to right in the SQL statement text string.

The SQL DESCRIBE PARAMETER statement cannot be used to obtain the information needed in the SQL BIND PARAMETERStmtSqlBindParameter statement when the driver for the data source does not support the SQLDescribeParam function. If the SQL DESCRIBE PARAMETER statement is executed prior to an SQL BIND PARAMETER for a query associated with a connection that does not support the SQLDescribeParam function, then much of the parameter information returned contains default values supplied by InstantSQL. The SQL DESCRIBE CONNECTIONStmtSqlDescribeConnection statement can be used to determine if a connection supports the SQLDescribeParam function. The InstantSQL statements SQL DESCRIBE COLUMN, SQL QUERY COLUMNS, and SQL QUERY PROCEDURE-COLUMNS can be used to obtain the SQL data type of a parameter when the program knows the column with which a parameter is associated.

The data description entries for the elementary items of an sql-Param-Description group are provided in the copy file lisqldpm.cpy. Only one parameter description is available in the provided definition of sql-Param-Description group in the copy file lisqldef.cpy. Since SQL statements frequently have multiple parameters, it is usually desirable to define a table of parameter description entries. This is easily accomplished by copying the copy file lisqldpm.cpy following a data description entry that names the table and specifies an OCCURS clause with the desired number of entries. For example, a table of parameter descriptions can be defined as follows:

78 MaxParams             VALUE 10.
01 ParamDescGroup.
    02 ParamDescTable     OCCURS MaxParams TIMES.
       COPY "lisqldpm.cpy".

The parameter descriptions could then be obtained as follows:

SQL DESCRIBE QUERY sql-QueryHandle   *> Get number of parameters
    sql-Query-Description.           *>   in sql-QryNoParams.
PERFORM VARYING sql-ParamNumber FROM 1 BY 1
    UNTIL sql-ParamNumber > sql-QryNoParams
SQL DESCRIBE PARAMETER sql-QueryHandle
      sql-ParamNumber, ParamDescTable(sql-ParamNumber)
END-PERFORM.

Since the copy file lisqldef.cpy defines sql-Param-Description as a group that also copies lisqldpm.cpy, references to data items in the above definition of ParamDescTable require qualification as shown in the following code fragment:

DISPLAY "Param data type = "
    sql-ParamType OF ParamDescGroup(sql-ParamNumber).
DISPLAY "Param length    = "
    sql-ParamLength OF ParamDescGroup(sql-ParamNumber).

The data description entries in an sql-Param-Description group match the first entries in an sql-Column-Description and sql-Column-Description-Ext group except for the data-names. Therefore, a program can save memory by sharing a common table of column/parameter description entries, if desired. The additional items in these column description groups that do not apply to parameters are set to default values (zero for numeric and spaces for nonnumeric data items) in this case. Since there are similarities between parameter and column descriptions, sharing can also simplify program logic in some cases, although at the expense of clarity, because a single set of data-names can be used.

The meanings of the values returned in an sql-Param-Description group are as follows:

20 sql-ParamType       PIC S9(06) LEADING.
88 sql-IsTypeChar          VALUE sql-Char.
88 sql-IsTypeVarChar       VALUE sql-VarChar.
88 sql-IsTypeLongVarChar   VALUE sql-LongVarChar.
88 sql-IsTypeNumeric       VALUE sql-Numeric.
88 sql-IsTypeDecimal       VALUE sql-Decimal.
88 sql-IsTypeBit           VALUE sql-Bit.
88 sql-IsTypeTinyInt       VALUE sql-TinyInt.
88 sql-IsTypeSmallInt      VALUE sql-SmallInt.
88 sql-IsTypeInteger       VALUE sql-Integer.
88 sql-IsTypeBigInt        VALUE sql-BigInt.
88 sql-IsTypeFloat         VALUE sql-Float.
88 sql-IsTypeReal          VALUE sql-Real.
88 sql-IsTypeDouble        VALUE sql-Double.
88 sql-IsTypeDate          VALUE sql-Date.
88 sql-IsTypeTime          VALUE sql-Time.
88 sql-IsTypeTimeStamp     VALUE sql-TimeStamp.
88 sql-IsTypeBinary        VALUE sql-Binary.
88 sql-IsTypeVarBinary     VALUE sql-VarBinary.
88 sql-IsTypeLongVarBinary VALUE sql-LongVarBinary.
88 sql-IsTypeNull          VALUE sql-Type-Null.

The value of sql-ParamType is the SQL data type of the parameter. The value sql-Type-Null occurs only when the data source does not support the SQLDescribeParam function and the SQL BIND PARAMETER statement has not yet been successfully executed for the specified parameter.

20 sql-ParamLength PIC S9(10) LEADING.

The value of sql-ParamLength is the length of the parameter. This is the length of the C data item transferred to the data source from an InstantSQL buffer when input parameter values are transferred at the start of query.

For the sql-Bit and sql-TinyInt data types, the length is 1.

For the sql-SmallInt data type, the length is 2.

For the sql-Integer and sql-Real data types, the length is 4.

For the sql-BigInt, sql-Double, and sql-Float data types, the length is 8.

For the sql-Numeric and sql-Decimal data types, the length is the column precision plus 2, which allows for a sign and decimal point character.

For the sql-Date and sql-Time data types, the length is 6.

For the sql-Timestamp data type, the length is 16.

For the sql-Char, sql-VarChar, sql-Binary, and sql-VarBinary data types, the length is the length of the column in the data source.

For the sql-LongVarChar and sql-LongVarBinary data types, the length initially is the length specified by the configuration options LongVarCharDataSize and LongVarBinaryDataSize, respectively, unless the SQLDescribeParam function reported a shorter length, in which case the shorter length takes precedence. Typically, the length for sql-LongVarChar and sql-LongVarBinary data types is very large, for example, 1073741824 (X"40000000"). After an SQL BIND PARAMETER statement is executed that binds a COBOL data item to a parameter of this type, the length is changed to the length of the COBOL data item if the length of the COBOL data item is longer than the configured length.

20 sql-ParamPrecision PIC S9(10) LEADING.

For character and binary data, the value of sql-ParamPrecision is the same as the value of sql-ParamLength. For numeric data, the value is the number of digits of precision of the parameter.

20 sql-ParamScale PIC S9(04) LEADING.

For numeric parameters, the value of sql-ParamScale is the number of digits to the right of the decimal point.

20 sql-ParamUnsigned   PIC 9(01).
88 sql-IsUnsigned          VALUE 1 FALSE 0.
88 sql-IsSigned            VALUE 0 FALSE 1.

The value of sql-ParamUnsigned indicates whether the parameter is unsigned or signed.

20 sql-ParamNullable   PIC 9(01).
88 sql-IsNotNullable       VALUE sql-No-Nulls.
88 sql-IsNullable          VALUE sql-Nullable.
88 sql-IsNullableUnknown VALUE sql-Nullable-Unknown.

The value of sql-ParamNullable indicates whether the parameter allows NULLs, does not allow NULLs, or that it is unknown whether the parameter allows NULLs.

If the data source does not support the SQLDescribeParam function, then sql-Nullable-Unknown is returned. The SQL DESCRIBE CONNECTION statement can be used to determine if the driver for a particular data source supports the SQLDescribeParam function.

SQL DESCRIBE PARAMETER Statement Example:

           PERFORM VARYING ParamNumber FROM 1 BY 1
               UNTIL ParamNumber > sql-QryNoParams
             SQL DESCRIBE PARAMETER
                 sql-QueryHandle,
                 ParamNumber, ParamDesc(ParamNumber)
           END-PERFORM.