Call Level Interface Guide and Reference

SQLGetTypeInfo - Get Data Type Information

Purpose

Specification: DB2 CLI 1.1 ODBC 1.0 ISO CLI

SQLGetTypeInfo() returns information about the data types that are supported by the DBMSs associated with DB2 CLI. The information is returned in an SQL result set. The columns can be received using the same functions that are used to process a query.

Syntax

SQLRETURN   SQLGetTypeInfo   (SQLHSTMT          StatementHandle,
                              SQLSMALLINT       DataType);

Function Arguments

Table 122. SQLGetTypeInfo Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLSMALLINT DataType input The SQL data type being queried. The supported types are:

SQL_ALL_TYPES
SQL_BIGINT
SQL_BINARY
SQL_BLOB
SQL_CHAR
SQL_CLOB
SQL_DATE
SQL_DBCLOB
SQL_DECIMAL
SQL_DOUBLE
SQL_FLOAT
SQL_GRAPHIC
SQL_INTEGER
SQL_LONGVARBINARY
SQL_LONGVARCHAR
SQL_LONGVARGRAPHIC
SQL_NUMERIC
SQL_REAL
SQL_SMALLINT
SQL_TIME
SQL_TIMESTAMP
SQL_VARBINARY
SQL_VARCHAR
SQL_VARGRAPHIC

If SQL_ALL_TYPES is specified, information about all supported data types would be returned in ascending order by TYPE_NAME. All unsupported data types would be absent from the result set.

Data Type	Argument	Use	Description
SQLHSTMT	`StatementHandle`	input	Statement handle.
SQLSMALLINT	`DataType`	input	The SQL data type being queried. The supported types are: SQL_ALL_TYPES SQL_BIGINT SQL_BINARY SQL_BLOB SQL_CHAR SQL_CLOB SQL_DATE SQL_DBCLOB SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TIME SQL_TIMESTAMP SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC If SQL_ALL_TYPES is specified, information about all supported data types would be returned in ascending order by TYPE_NAME. All unsupported data types would be absent from the result set.

Usage

Since SQLGetTypeInfo() generates a result set and is equivalent to executing a query, it will generate a cursor and begin a transaction. To prepare and execute another statement on this statement handle, the cursor must be closed.

If SQLGetTypeInfo() is called with an invalid DataType, an empty result set is returned.

If either the LONGDATACOMPAT keyword or the SQL_ATTR_LONGDATA_COMPAT connection attribute is set, then SQL_LONGVARBINARY, SQL_LONGVARCHAR and SQL_LONGVARGRAPHIC will be returned for the DATA_TYPE argument instead of SQL_BLOB, SQL_CLOB and SQL_DBCLOB.

The columns of the result set generated by this function are described below.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change. The data types returned are those that can be used in a CREATE TABLE, ALTER TABLE, DDL statement. Non-persistent data types such as the locator data types are not part of the returned result set. User defined data types are not returned either.

Columns Returned by SQLGetTypeInfo

Column 1 TYPE_NAME (VARCHAR(128) NOT NULL Data Type)

Character representation of the SQL data type name, e.g. VARCHAR, BLOB, DATE, INTEGER

Column 2 DATA_TYPE (SMALLINT NOT NULL Data Type)

SQL data type define values, e.g. SQL_VARCHAR, SQL_BLOB, SQL_DATE, SQL_INTEGER.

Column 3 COLUMN_SIZE (INTEGER Data Type)

If the data type is a character or binary string, then this column contains the maximum length in bytes; if it is a graphic (DBCS) string, this is the number of double byte characters for the column.

For date, time, timestamp data types, this is the total number of characters required to display the value when converted to character.

For numeric data types, this is the total number of digits.

Column 4 LITERAL_PREFIX (VARCHAR(128) Data Type)

Character that DB2 recognizes as a prefix for a literal of this data type. This column is null for data types where a literal prefix is not applicable.

Column 5 LITERAL_SUFFIX (VARCHAR(128) Data Type)

Character that DB2 recognizes as a suffix for a literal of this data type. This column is null for data types where a literal prefix is not applicable.

Column 6 CREATE_PARAMS (VARCHAR(128) Data Type)

The text of this column contains a list of keywords, separated by commas, corresponding to each parameter the application may specify in parenthesis when using the name in the TYPE_NAME column as a data type in SQL. The keywords in the list can be any of the following: LENGTH, PRECISION, SCALE. They appear in the order that the SQL syntax requires that they be used.

A NULL indicator is returned if there are no parameters for the data type definition, (such as INTEGER).
Note: The intent of CREATE_PARAMS is to enable an application to customize the interface for a DDL builder. An application should expect, using this, only to be able to determine the number of arguments required to define the data type and to have localized text that could be used to label an edit control.

Column 7 NULLABLE (SMALLINT NOT NULL Data Type)

Indicates whether the data type accepts a NULL value

Set to SQL_NO_NULLS if NULL values are disallowed.
Set to SQL_NULLABLE if NULL values are allowed.

Column 8 CASE_SENSITIVE (SMALLINT NOT NULL Data Type)

Indicates whether the data type can be treated as case sensitive for collation purposes; valid values are SQL_TRUE and SQL_FALSE.

Column 9 SEARCHABLE (SMALLINT NOT NULL Data Type)

Indicates how the data type is used in a WHERE clause. Valid values are:

SQL_UNSEARCHABLE : if the data type cannot be used in a WHERE clause.
SQL_LIKE_ONLY : if the data type can be used in a WHERE clause only with the LIKE predicate.
SQL_ALL_EXCEPT_LIKE : if the data type can be used in a WHERE clause with all comparison operators except LIKE.
SQL_SEARCHABLE : if the data type can be used in a WHERE clause with any comparison operator.

Column 10 UNSIGNED_ATTRIBUTE (SMALLINT Data Type)

Indicates where the data type is unsigned. The valid values are: SQL_TRUE, SQL_FALSE or NULL. A NULL indicator is returned if this attribute is not applicable to the data type.

Column 11 FIXED_PREC_SCALE (SMALLINT NOT NULL Data Type)

Contains the value SQL_TRUE if the data type is exact numeric and always has the same precision and scale; otherwise, it contains SQL_FALSE.

Column 12 AUTO_INCREMENT (SMALLINT Data Type)

Contains SQL_TRUE if a column of this data type is automatically set to a unique value when a row is inserted; otherwise, contains SQL_FALSE.

Column 13 LOCAL_TYPE_NAME (VARCHAR(128) Data Type)

This column contains any localized (native language) name for the data type that is different from the regular name of the data type. If there is no localized name, this column is NULL.

This column is intended for display only. The character set of the string is locale-dependent and is typically the default character set of the database.

Column 14 MINIMUM_SCALE (INTEGER Data Type)

The minimum scale of the SQL data type. If a data type has a fixed scale, the MINIMUM_SCALE and MAXIMUM_SCALE columns both contain the same value. NULL is returned where scale is not applicable.

Column 15 MAXIMUM_SCALE (INTEGER Data Type)

The maximum scale of the SQL data type. NULL is returned where scale is not applicable. If the maximum scale is not defined separately in the DBMS, but is defined instead to be the same as the maximum length of the column, then this column contains the same value as the COLUMN_SIZE column.

Column 16 SQL_DATA_TYPE (SMALLINT NOT NULL Data Type)

The value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the DATA_TYPE column (except for interval and datetime data types which DB2 CLI does not support).

Column 17 SQL_DATETIME_SUB (SMALLINT Data Type)

This field is always NULL (DB2 CLI does not support interval and datetime data types).

Column 18 NUM_PREC_RADIX (INTEGER Data Type)

If the data type is an approximate numeric type, this column contains the value 2 to indicate that COLUMN_SIZE specifies a number of bits. For exact numeric types, this column contains the value 10 to indicate that COLUMN_SIZE specifies a number of decimal digits. Otherwise, this column is NULL.

Column 19 INTERVAL_PRECISION (SMALLINT Data Type)

This field is always NULL (DB2 CLI does not support interval data types).

Return Codes

SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 123. SQLGetTypeInfo SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle. StatementHandle had not been closed.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY004 SQL data type out of range. An invalid DataType was specified.
HY010 Function sequence error. The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle. `StatementHandle` had not been closed.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY004	SQL data type out of range.	An invalid `DataType` was specified.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

The following ODBC specified SQL data types (and their corresponding DataType define values) are not supported by any IBM RDBMS:

Data Type: DataType
TINY INT: SQL_TINYINT
BIG INT: SQL_BIGINT
BIT: SQL_BIT

CLI Sample dtinfo.c

(The complete sample dtinfo.c is also available here .)

 
/* From the CLI sample DTINFO.C */
/* ... */
 
    /* call SQLTables */ 
    printf("\n    Call SQLGetTypeInfo.\n");
    sqlrc = SQLGetTypeInfo( hstmt, SQL_ALL_TYPES); 
    STMT_HANDLE_CHECK( hstmt, sqlrc);

References

[ Top of Page | Previous Page | Next Page ]