SQL DESCRIBE COLUMN Statement

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

The SQL DESCRIBE COLUMN statement returns a group that describes a column in the result set 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 result column is to be described. This value must have been returned from a successful SQL PREPARE QUERY statement or any of the browse statements.

ColNameOrNumberN (input). This argument must specify a nonnumeric value or a numeric integer value. If the argument is nonnumeric, then its value identifies the column name of a column in the result set that is to be described. If the argument is numeric, then its value identifies the column number of a column in the result set that is to be described. Columns in the result set are numbered from left to right starting with one (1).

ColDescGroupN (output). This argument must refer to a group data item that has the same data description as either sql-Column-Description or sql-Column-Description-Ext defined in the copy file lisqldef.cpy. The description of the column is stored in the specified group data item.

The SQL DESCRIBE COLUMN statement can be used to return a brief column description or an extended column description. If sql-Column-Description, or another group with the same description, is specified for the ColDescGroupN argument, then the brief form of the column description is returned. If sql-Column-Description-Ext, or another group with the same description, is specified for the ColDescGroupN argument, then the extended form of the column description is returned.

The data description entries for the elementary items of an sql-Column-Description group are provided in the copy file lisqldcl.cpy. Only one column description group is provided in the copy file lisqldef.cpy. Since SQL statements frequently have multiple result columns, it is usually desirable to define a table of column description entries. This is easily accomplished by copying the copy file lisqldcl.cpy following a data description entry that names the table and specifies an OCCURS clause with the desired number of entries. If extended column descriptions are desired, the copy file lisqlxcl.cpy should also be copied in this table data description entry. For example, a table of extended column descriptions can be defined as follows:

78 MaxColumns            VALUE 25.
01 ColDescGroup.
    02 ColDescTable       OCCURS MaxColumns TIMES.
       COPY "lisqldcl.cpy".
       COPY "lisqlxcl.cpy".

The column descriptions could then be obtained as follows:

SQL DESCRIBE QUERY sql-QueryHandle   *> Get number of columns
    sql-Query-Description.           *>   in sql-QryNoCols.
PERFORM VARYING sql-ColumnNumber FROM 1 BY 1
    UNTIL sql-ColumnNumber > sql-QryNoCols
SQL DESCRIBE COLUMN sql-QueryHandle
      sql-ColumnNumber, ColDescTable(sql-ColumnNumber)
END-PERFORM.

Since the copy file lisqldef.cpy defines sql-Column-Description-Ext as a group that also copies lisqldcl.cpy and lisqlxcl.cpy, references to data items in the above definition of ColDescTable require qualification as shown in the following code fragment:

DISPLAY "Column data type = "
    sql-ColType OF ColDescGroup(sql-ColumnNumber).
DISPLAY "Column length    = "
    sql-ColLength OF ColDescGroup(sql-ColumnNumber).

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

20 sql-ColType        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-ColType is the SQL data type of the column in the result set.

20 sql-ColLength PIC S9(10) LEADING.

The value of sql-ColLength is the length of the column in the result set. This is the length of the C data item transferred from the data source to an InstantSQL buffer when rows are fetched.

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 sql-LongVarChar and sql-LongVarBinary data types, the length initially is the length specified by the configuration options LongVarCharDataSize and LongVarBinaryDataSize, respectively, unless the length of the column in the data source is less, in which case the length of the column in the data source takes precedence. Typically, the length for sql-LongVarChar and sql-LongVarBinary data types is very large, for example, 1073741824 (X"40000000"), even though the longest data actually present in the column may be much smaller. After an SQL BIND COLUMN statement is executed that binds a COBOL data item to a column 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-ColPrecision PIC S9(10) LEADING.

For character and binary data, the value of sql-ColPrecision is the same as sql-ColLength. For numeric data, the value is the number of digits of precision supported by the column in the data source.

20 sql-ColScale PIC S9(04) LEADING.

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

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

The value of sql-ColUnsigned indicates whether the column is unsigned or signed.

20 sql-ColNullable    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-ColNullable indicates whether the column allows NULLs, does not allow NULLs, or that it is unknown whether the column allows NULLs.

20 sql-ColNumber PIC 9(04).

The value of sql-ColNumber is the column number (useful if the column was described by using the column name).

20 sql-ColNameGroup.
24 sql-ColNameLength PIC S9(04) LEADING.
24 sql-ColName PIC X(sql-ColumnNameSize).

The value of sql-ColNameLength is the length of the column name in the sql-ColName data item, which follows. The value is zero for columns without a name or for which the driver cannot determine the name.

The value of sql-ColName is the column name. A string of all spaces is returned for columns without a name or for which the driver cannot determine the name.

An sql-Column-Description-Ext group includes the items described for an sql-Column-Description group plus an extension set of items. The data description entries for the elementary items of the extension portion of the sql-Column-Description-Ext group are provided in the copy file lisqlxcl.cpy. The meanings of the extension items in an sql-Col-Description-Ext group are as follows:

20 sql-ColAutoUniqueValue PIC 9(01).
88 sql-IsAutoUniqueValue VALUE 1 FALSE 0.

The value of sql-ColAutoUniqueValue indicates whether the column is auto-unique or not auto-unique.

This field is valid for numeric data type columns only. An application can insert values into a row containing an auto-unique column, but typically cannot update values in the column.

When an insert is made into an auto-unique column, a unique value is inserted into the column at insert time. This is frequently accomplished with an auto-incrementing column. The increment is not defined, but is data source – specific. A portable application should not assume that an auto-unique column starts with or increments by any particular value.

20 sql-ColCaseSensitive PIC 9(01).
88 sql-IsCaseSensitive VALUE 1 FALSE 0.

The value of sql-ColCaseSensitive indicates whether the column is treated as case sensitive for collations and comparisons. The value is only meaningful for character data. The value 0 is returned for numeric data.

20 sql-ColDisplaySize PIC S9(10) LEADING.

The value of sql-ColDisplaySize indicates the maximum number of characters required for displaying data from the column.

20 sql-ColFixedPrecScale PIC 9(01).
88 sql-IsFixedPrecScale VALUE 1 FALSE 0.

The value of sql-ColFixedPrecScale indicates whether the column has fixed precision and nonzero scale that are data source - specific, such as a "money" value might have.

20 sql-ColLabelGroup.
24 sql-ColLabelLength PIC S9(04) LEADING.
24 sql-ColLabel PIC X(sql-ColumnNameSize).

The value of sql-ColLabelLength is the length of the column label in the sql-ColLabel data item, which follows. The value is zero if the column is unlabeled and unnamed.

The value of sql-ColLabel is the column label or title. For example, a column named EmpName might be labeled Employee Name, or might be labeled with an alias. If a column does not have a label, the column name is returned. If the column is unlabeled and unnamed, a string of all spaces is returned.

20 sql-ColSearchable      PIC 9(04).
88 sql-IsPredNone        VALUE sql-Pred-None.
88 sql-IsPredChar        VALUE sql-Pred-Char.
88 sql-IsPredBasic       VALUE sql-Pred-Basic.
88 sql-IsPredSearchable VALUE sql-Pred-Searchable.

The value of sql-ColSearchable indicates whether the column may be specified in predicates as follows:

sql-Pred-None if the column may not be used in a WHERE clause.

sql-Pred-Char if the column may be used in a WHERE clause, but only with the LIKE predicate.

sql-Pred-Basic if the column may be used in a WHERE clause with all the comparison operators except LIKE.

sql-Pred-Searchable if the column may be used in a WHERE clause with any comparison operator.

Columns of type sql-LongVarChar and sql-LongVarBinary usually return sql-Pred-Char.

20 sql-ColTableNameGroup.
24 sql-ColTableNameLength PIC S9(04) LEADING.
24 sql-ColTableName PIC X(sql-TableNameSize).

The value of sql-ColTableNameLength is the length of the table name in the sql-ColTableName data item, which follows. The value is zero if the table name cannot be determined.

The value of sql-ColTableName is the name of the table that contains the column. The returned value is implementation-defined if the column is an expression or if the column is part of a view. If the table name cannot be determined, a string of all spaces is returned.

20 sql-ColTypeNameGroup.
24 sql-ColTypeNameLength PIC S9(04) LEADING.
24 sql-ColTypeName PIC X(sql-DataTypeNameSize).

The value of sql-ColTypeNameLength is the length of the type name in the sql-ColTypeName data item, which follows. The value is zero if the type is unknown.

The value of sql-ColTypeName indicates the data source – dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINARY", or "CHAR ( ) FOR BIT DATA". If the type is unknown, a string of all spaces is returned.

20 sql-ColUpdatable       PIC 9(04).
88 sql-IsReadOnly         VALUE sql-Attr-ReadOnly.
88 sql-IsWritable         VALUE sql-Attr-Write.
88 sql-IsReadWriteUnknown
          VALUE sql-Attr-ReadWrite-Unknown.

The value of sql-ColUpdatable indicates the updatability of the column in the result set as follows:

sql-Attr-ReadOnly if the column is read-only (cannot be updated).

sql-Attr-Write if the column is read-write (can be updated).

sql-Attr-ReadWrite-Unknown if it is unknown whether the column can be updated.

This value does not describe the updatability of the column in the base table. The updatability of the base column on which the result set column is based may be different from the value in this field. Whether a column is updatable may be based on the data type, user privileges, and the definition of the result set itself. If it is unclear whether a column is updatable, sql-Attr-ReadWrite-Unknown should be returned by the driver.

SQL DESCRIBE COLUMN Statement Example:

           PERFORM VARYING ColumnNumber FROM 1 BY 1
               UNTIL ColumnNumber > sql-QryNoCols
             SQL DESCRIBE COLUMN
                 sql-QueryHandle,
                 ColumnNumber, ColumnDesc(ColumnNumber)
           END-PERFORM.