SQLColAttribute() - Get column attributes

SQLColAttribute() returns descriptor information about a column in a result set. Descriptor information is returned as a character string, a 32-bit descriptor-dependent value, or an integer value.

ODBC specifications for SQLColAttribute()

Table 1. SQLColAttribute() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
3.0 Yes Yes

Syntax

SQLRETURN  SQLColAttribute  (SQLHSTMT         StatementHandle,
                            SQLSMALLINT      ColumnNumber,
                            SQLSMALLINT      FieldIdentifier,
                            SQLPOINTER       CharacterAttributePtr,
                            SQLSMALLINT      BufferLength,
                            SQLSMALLINT      *StringLengthPtr,
                            SQLPOINTER       NumericAttributePtr);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLColAttribute() arguments
Data type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLUSMALLINT ColumnNumber input Number of the column you want to be described. Columns are numbered sequentially from left to right, starting at 1. Column zero might not be defined. The Db2 ODBC 3.0 driver does not support bookmarks. See Restrictions.
SQLSMALLINT FieldIdentifier input The field in row ColumnNumber that is to be returned. See Table 3.
SQLPOINTER CharacterAttributePtr output Pointer to a buffer in which to return the value in the FieldIdentifier field of the ColumnNumber row if the field is a character string. Otherwise, this field is ignored.
SQLSMALLINT BufferLength input The length, in bytes, of the buffer you specified for the *CharacterAttributePtr argument, if the field is a character string. Otherwise, this field is ignored.
SQLSMALLINT * StringLengthPtr output Pointer to a buffer in which to return the total number of bytes (excluding the nul-termination character) that are available to return in *CharacterAttributePtr.

For character data, if the number of bytes that are available to return is greater than or equal to BufferLength, the descriptor information in *CharacterAttributePtr is truncated to BufferLength minus the length (in bytes) of a nul-termination character. Db2 ODBC then nul-terminates the value.

For all other types of data, the value of BufferLength is ignored, and Db2 ODBC assumes that the size of *CharacterAttributePtr is 32 bits.
SQLPOINTER NumericAttributePtr output Pointer to an integer buffer in which to return the value in the FieldIdentifier field of the ColumnNumber row, if the field is a numeric descriptor type, such as SQL_DESC_LENGTH. Otherwise, this field is ignored.

Usage

SQLColAttribute() returns information in either *NumericAttributePtr or *CharacterAttributePtr. Integer information is returned in *NumericAttributePtr as a 32-bit, signed value; all other formats of information are returned in *CharacterAttributePtr. When information is returned in *NumericAttributePtr, Db2 ODBC ignores CharacterAttributePtr, BufferLength, and StringLengthPtr. When information is returned in *CharacterAttributePtr, Db2 ODBC ignores NumericAttributePtr.

SQLColAttribute() allows access to the more extensive set of descriptor information that is available in ANSI ANSI/ISO SQL standard of 1992 and database management system vendor extensions. SQLColAttribute() is a more extensible alternative to the SQLDescribeCol() function, but that function can return only one attribute per call.

Db2 ODBC must return a value for each of the descriptor types. If a descriptor type does not apply to a data source, Db2 ODBC returns 0 in *StringLengthPtr or an empty string in *CharacterAttributePtr unless otherwise stated.

The following table lists the descriptor types that are returned by ODBC 3.0 SQLColAttribute(), along with the ODBC 2.0 SQLColAttributes() attribute values (in parentheses) that were replaced or renamed.

Table 3. SQLColAttribute() field identifiers
Field identifier Information returned in arguments Description
SQL_DESC_AUTO_UNIQUE_VALUE (SQL_COLUMN_AUTO_INCREMENT)1 NumericAttributePtr Indicates whether the column data type automatically increments. SQL_FALSE is returned in NumericAttributePtr for all Db2 SQL data types.
SQL_DESC_BASE_COLUMN_NAME CharacterAttributePtr The base column name for the set column. If a base column name does not exist (for example, columns that are expressions), this variable contains an empty string.
SQL_DESC_BASE_TABLE_NAME CharacterAttributePtr The name of the base table that contains the column. If the base table name cannot be defined or is not applicable, this variable contains an empty string.
SQL_DESC_CASE_SENSITIVE (SQL_COLUMN_CASE_SENSITIVE1 NumericAttributePtr Indicates if the column data type is case sensitive. Either SQL_TRUE or SQL_FALSE is returned in NumericAttributePtr, depending on the data type. Case sensitivity does not apply to graphic data types. SQL_FALSE is returned for non-character data types and for the XML data type.
SQL_DESC_CATALOG_NAME (SQL_COLUMN_CATALOG_NAME)1 (SQL_COLUMN_QUALIFIER_NAME)1 CharacterAttributePtr The name of the catalog table that contains the column. An empty string is returned because Db2 ODBC supports two-part naming for a table.
SQL_DESC_CONCISE_TYPE CharacterAttributePtr The concise data type. For datetime data types, this field returns the concise data type, such as SQL_TYPE_TIME.
SQL_DESC_COUNT (SQL_COLUMN_COUNT)1 NumericAttributePtr The number of columns in the result set.
SQL_DESC_DISPLAY_SIZE (SQL_COLUMN_DISPLAY_SIZE)1 NumericAttributePtr The maximum number of bytes that are needed to display the data in character form.
SQL_DESC_DISTINCT_TYPE (SQL_COLUMN_DISTINCT_TYPE)1 CharacterAttributePtr

The distinct type name that is used for a column. If the column is a built-in SQL type and not a distinct type, an empty string is returned.

IBM® specific: This is an IBM-defined extension to the list of descriptor attributes as defined by ODBC.
SQL_DESC_FIXED_PREC_SCALE (SQL_COLUMN_MONEY)1 NumericAttributePtr

SQL_TRUE if the column has a fixed precision and nonzero scale that are data-source-specific. This value is SQL_FALSE if the column does not have a fixed precision and nonzero scale that are data-source-specific.

SQL_FALSE is returned in NumericAttributePtr for all Db2 SQL data types.
SQL_DESC_LABEL (SQL_COLUMN_LABEL)1 CharacterAttributePtr The column label. If the column does not have a label, the column name or the column expression is returned. If the column is not labeled or named, an empty string is returned.
SQL_DESC_LENGTH NumericAttributePtr A numeric value that is either the maximum or actual length, in bytes, of a character string or binary data type. This value is the maximum length for a fixed-length data type, or the actual length for a varying-length data type. This value always excludes the nul-termination character that ends the character string. This value is 0 for the XML data type.
SQL_DESC_LITERAL_PREFIX CharacterAttributePtr A VARCHAR(128) record field that contains the character or characters that Db2 ODBC recognizes as a prefix for a literal of this data type. This field contains an empty string if a literal prefix is not applicable to this data type.
SQL_DESC_LITERAL_SUFFIX CharacterAttributePtr A VARCHAR(128) record field that contains the character or characters that Db2 ODBC recognizes as a suffix for a literal of this data type. This field contains an empty string if a literal suffix is not applicable to this data type.
SQL_DESC_LOCAL_TYPE_NAME CharacterAttributePtr A VARCHAR(128) record field that contains any localized (native language) name for the data type that might be different from the regular name of the data type. If a localized name does not exist, an empty string is returned. This field is for display purposes only. The character set of the string is location dependent; it is typically the default character set of the server.
SQL_DESC_NAME (SQL_COLUMN_NAME)1 CharacterAttributePtr The name of the column specified with ColumnNumber. If the column is an expression, the column number is returned.

In either case, SQL_DESC_UNNAMED is set to SQL_NAMED. If the column is unnamed or has no alias, an empty string is returned and SQL_DESC_UNNAMED is set to SQL_UNNAMED.
SQL_DESC_NULLABLE (SQL_COLUMN_NULLABLE)1 NumericAttributePtr If the column that is identified by ColumnNumber can contain null values, SQL_NULLABLE is returned. If the column cannot accept null values, SQL_NO_NULLS is returned.
SQL_DESC_NUM_PREC_RADIX NumericAttributePtr The precision of each digit in a numeric value. The following values are commonly returned:
  • If the data type in the SQL_DESC_TYPE field is an approximate data type, this SQLINTEGER field contains a value of 2 because the SQL_DESC_PRECISION field contains the number of bits.
  • If the data type in the SQL_DESC_TYPE field is an exact numeric data type, this field contains a value of 10 because the SQL_DESC_PRECISION field contains the number of decimal digits.
  • This field is set to 0 for all nonnumeric data types.
SQL_DESC_OCTET_LENGTH (SQL_COLUMN_LENGTH)1 NumericAttributePtr The number of bytes of data that is associated with the column. This is the length in bytes of data that is transferred on the fetch or SQLGetData() for this column if SQL_C_DEFAULT is specified as the C data type.

If the column that is identified in ColumnNumber is a fixed-length character or binary string, (for example, SQL_CHAR or SQL_BINARY), the actual length is returned.

If the column that is identified in ColumnNumber is a varying-length character or binary string, (for example, SQL_VARCHAR or SQL_BLOB), the maximum length is returned.
SQL_DESC_PRECISION (SQL_COLUMN_PRECISION)1 NumericAttributePtr The precision in units of digits if the column is:
  • SQL_BIGINT
  • SQL_DECIMAL
  • SQL_NUMERIC
  • SQL_DOUBLE
  • SQL_FLOAT
  • SQL_DECFLOAT
  • SQL_INTEGER
  • SQL_REAL
  • SQL_SMALLINT

If the column is a character SQL data type, the precision that is returned indicates the maximum number of characters that the column can hold.

If the column is a graphic SQL data type, the precision indicates the maximum number of double-byte characters that the column can hold.

If the column is the XML data type, the precision is 0.
SQL_DESC_SCALE (SQL_COLUMN_SCALE)1 NumericAttributePtr The scale attribute of the column.
SQL_DESC_SCHEMA_NAME (SQL_COLUMN_OWNER_NAME)1 CharacterAttributePtr The schema of the table that contains the column. An empty string is returned; Db2 is not able to determine this attribute.
SQL_DESC_SEARCHABLE (SQL_COLUMN_SEARCHABLE)1 NumericAttributePtr Indicates if the column data type is searchable:
  • SQL_PRED_NONE (SQL_UNSEARCHABLE in ODBC 2.0) if the column cannot be used in a WHERE clause.
  • SQL_PRED_CHAR (SQL_LIKE_ONLY in ODBC 2.0) if the column can be used in a WHERE clause only with the LIKE predicate.
  • SQL_PRED_BASIC (SQL_ALL_EXCEPT_LIKE in ODBC 2.0) if the column can be used in a WHERE clause with all comparison operators except LIKE.
  • SQL_SEARCHABLE if the column can be used in a WHERE clause with any comparison operator.
SQL_DESC_TABLE_NAME (SQL_COLUMN_TABLE_NAME)1 CharacterAttributePtr The name of the table that contains the column. An empty string is returned; Db2 ODBC cannot determine this attribute.
SQL_DESC_TYPE (SQL_COLUMN_TYPE)1 NumericAttributePtr The SQL data type of the column. For the datetime data types, this field returns the verbose data type, such as SQL_DATETIME.
SQL_DESC_TYPE_NAME (SQL_COLUMN_TYPE_NAME)1 CharacterAttributePtr The type of the column (specified in an SQL statement).
SQL_DESC_UNNAMED NumericAttributePtr Returns SQL_NAMED or SQL_UNNAMED. If the SQL_DESC_NAME contains a column name, SQL_NAMED is returned. If the column is unnamed, SQL_UNNAMED is returned.
SQL_DESC_UNSIGNED (SQL_COLUMN_UNSIGNED)1 NumericAttributePtr Indicates if the column data type is an unsigned type. SQL_TRUE is returned in NumericAttributePtr for all nonnumeric data types. SQL_FALSE is returned for all numeric data types.
SQL_DESC_UPDATABLE (SQL_COLUMN_UPDATABLE)1 NumericAttributePtr Indicates if the column data type is a data type that can be updated:
  • SQL_ATTR_READWRITE_UNKNOWN is returned in NumericAttributePtr for all Db2 SQL data types.
  • SQL_ATTR_READONLY is returned if the column is obtained from a catalog function call. ODBC also defines the following values, however Db2 ODBC does not return these values:
    • SQL_DESC_UPDATABLE
    • SQL_UPDT_WRITE
Note:
  1. These descriptor values (values for argument fDescType) are for the deprecated ODBC 2.0 SQLColAttributes() API. Both SQLColAttribute() and SQLColAttributes() support these values.

Return codes

After you call SQLColAttribute(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_INVALID_HANDLE
  • SQL_ERROR

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. SQLColAttribute() SQLSTATEs
SQLSTATE Description Explanation
01000 Warning. Informational message. (SQLColAttribute() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
01004 Data truncated. The buffer to which the CharacterAttributePtr argument points is not large enough to return the entire string value, so the string value was truncated. The length, in bytes, of the untruncated string value is returned in the buffer to which the StringLengthPtr argument points. (SQLColumnAttribute() returns SQL_SUCCESS_WITH_INFO for this SQLSTATE.)
07005 The statement did not return a result set. The statement that is associated with the StatementHandle argument did not return a result set. There are no columns to describe.
HY000 General error. An error occurred for which there is no specific SQLSTATE. The error message that is returned by SQLGetDiagRec() in the buffer to which the MessageText argument points, describes the error and its cause.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate memory that is required to support execution or completion of the function.
HY002 Invalid column number. The value that is specified for the ColumnNumber argument is less than 0, or greater than the number of columns in the result set.
HY010 Function sequence error. This SQLSTATE is returned for one or more of the following reasons:
  • The function is called prior to SQLPrepare() or SQLExecDirect() for the statement handle that the StatementHandle argument specifies.
  • SQLExecute() or SQLExecDirect() is called for the statement handle that the StatementHandle argument specifies and returns SQL_NEED_DATA. SQLColAttribute() is called before data is sent for all data-at-execution parameters or columns.
HY090 Invalid string or buffer length. The value that is specified for the BufferLength argument is less than 0.
HY091 Descriptor type out of range. The value that is specified for the FieldIdentifier argument is neither one of the defined values nor an implementation-defined value.
HYC00 Driver not capable. Db2 ODBC does not support the specified value for the FieldIdentifier argument.

Restrictions

ColumnNumber zero might not be defined. The Db2 ODBC 3.0 driver does not support bookmarks.

Example

Refer to SQLColAttribute() for a related example. In this example, SQLColAttribute() retrieves the display length for a column.