SQLGetTypeInfo - Get data type information

SQLGetTypeInfo - Get data type information

SQLGetTypeInfo() returns information about the data types that are supported by the Database Management Systems (DBMSs) associated with DB2^® UDB 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 1. 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_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 is returned in ascending order by TYPE_NAME. All unsupported data types are absent from the result set.

Usage

Because SQLGetTypeInfo() generates a result set and is equivalent to executing a query, it generates a cursor and begins a transaction. To prepare and process another statement on this statement handle, the cursor must be closed.
If SQLGetTypeInfo() is called with a DataType that is not valid, an empty result set is returned.
The columns of the result set that is generated by this function are described below.
Although new columns might be added and the names of the existing columns might be changed in future releases, the position of the current columns does not change. The data types that are returned are those that can be used in a CREATE TABLE, ALTER TABLE, DDL statement. Nonpersistent data types are not part of the returned result set. User-defined data types are not returned either.
Table 2. Columns returned by SQLGetTypeInfo
Column number/name Data type Description

1 TYPE_NAME VARCHAR(128) NOT NULL Character representation of the SQL data type name (for example, VARCHAR, DATE, INTEGER)
2 DATA_TYPE SMALLINT NOT NULL SQL data type define values (for example, SQL_VARCHAR, SQL_DATE, SQL_INTEGER)
3 COLUMN_SIZE INTEGER 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.
4 LITERAL_PREFIX VARCHAR(128) 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.
5 LITERAL_SUFFIX VARCHAR(128) 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.
6 CREATE_PARAMS VARCHAR(128) The text of this column contains a list of keywords, separated by commas, corresponding to each parameter the application might 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: 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).
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 can be used to label an edit control.
7 NULLABLE SMALLINT NOT NULL This 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.

Set to SQL_NULLABLE_UNKNOWN if it is not known whether NULL values are allowed or not.

8 CASE_SENSITIVE SMALLINT NOT NULL This indicates whether the data type can be treated as case sensitive for collation purposes; valid values are SQL_TRUE and SQL_FALSE.
9 SEARCHABLE SMALLINT NOT NULL This 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.

10 UNSIGNED_ATTRIBUTE SMALLINT This 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.
11 FIXED_PREC_SCALE SMALLINT NOT NULL This 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.
12 AUTO_INCREMENT SMALLINT This 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.
13 LOCAL_TYPE_NAME VARCHAR(128)
This column contains any localized 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.
14 MINIMUM_SCALE INTEGER 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.
15 MAXIMUM_SCALE INTEGER 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.
16 SQL_DATA_TYPE SMALLINT NOT NULL 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).
17 SQL_DATETIME_SUB SMALLINT This field is always NULL (DB2 CLI does not support interval and datetime data types).
18 NUM_PREC_RADIX INTEGER 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.
19 INTERVAL_PRECISION SMALLINT This field is always NULL (DB2 CLI does not support interval data types).

Return codes

SQL_SUCCESS

SQL_ERROR

SQL_INVALID_HANDLE

Error conditions

Table 3. SQLGetTypeInfo SQLSTATEs
SQLSTATE Description Explanation

24000 Cursor state that is not valid A cursor is already opened on the statement handle. StatementHandle has not been closed.
40003 08S01 Communication link failure The communication link between the application and data source fails before the function is completed.
HY001 Memory allocation failure DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY004 SQL data type out of range A DataType that is not valid is specified.
HY010 Function sequence error The function is called while in a data-at-processing (SQLParamData(), SQLPutData()) operation.

HY021

Internal descriptor that is not valid

The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYT00 Timeout expired

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
BIT SQL_BIT

Example

By using the code examples, you agree to the terms of the Code license and disclaimer information.
/* From CLI sample typeinfo.c */
/* ... */
    rc = SQLGetTypeInfo(hstmt, SQL_ALL_TYPES);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER) typename.s, 128, &typename.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 2, SQL_C_DEFAULT, (SQLPOINTER) & datatype,
                    sizeof(datatype), &datatype_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 3, SQL_C_DEFAULT, (SQLPOINTER) & precision,
                    sizeof(precision), &precision_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 7, SQL_C_DEFAULT, (SQLPOINTER) & nullable,
                    sizeof(nullable), &nullable_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 8, SQL_C_DEFAULT, (SQLPOINTER) & casesens,
                    sizeof(casesens), &casesens_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    printf("Datatype                  Datatype Precision  Nullable   Case\n");
    printf("Typename                   (int)                       Sensitive\n");
    printf("------------------------- -------- ---------- -------- ---------\n");
    /* LONG VARCHAR FOR BIT DATA      99  2147483647   FALSE    FALSE */
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        printf("%-25s ", typename.s);
        printf("%8d ", datatype);
        printf("%10ld ", precision);
        printf("%-8s ", truefalse[nullable]);
        printf("%-9s\n", truefalse[casesens]);
    }                           /* endwhile */
 
    if ( rc != SQL_NO_DATA_FOUND )
       CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
References

SQLBindCol - Bind a column to an application variable

SQLGetInfo - Get general information

Parent topic:
DB2 UDB CLI functions

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_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 is returned in ascending order by TYPE_NAME. All unsupported data types are absent from the result set.

Column number/name	Data type	Description
1 TYPE_NAME	VARCHAR(128) NOT NULL	Character representation of the SQL data type name (for example, VARCHAR, DATE, INTEGER)
2 DATA_TYPE	SMALLINT NOT NULL	SQL data type define values (for example, SQL_VARCHAR, SQL_DATE, SQL_INTEGER)
3 COLUMN_SIZE	INTEGER	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.
4 LITERAL_PREFIX	VARCHAR(128)	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.
5 LITERAL_SUFFIX	VARCHAR(128)	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.
6 CREATE_PARAMS	VARCHAR(128)	The text of this column contains a list of keywords, separated by commas, corresponding to each parameter the application might 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: 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). 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 can be used to label an edit control.
7 NULLABLE	SMALLINT NOT NULL	This 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. Set to SQL_NULLABLE_UNKNOWN if it is not known whether NULL values are allowed or not.
8 CASE_SENSITIVE	SMALLINT NOT NULL	This indicates whether the data type can be treated as case sensitive for collation purposes; valid values are SQL_TRUE and SQL_FALSE.
9 SEARCHABLE	SMALLINT NOT NULL	This 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.
10 UNSIGNED_ATTRIBUTE	SMALLINT	This 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.
11 FIXED_PREC_SCALE	SMALLINT NOT NULL	This 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.
12 AUTO_INCREMENT	SMALLINT	This 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.
13 LOCAL_TYPE_NAME	VARCHAR(128)	This column contains any localized 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.
14 MINIMUM_SCALE	INTEGER	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.
15 MAXIMUM_SCALE	INTEGER	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.
16 SQL_DATA_TYPE	SMALLINT NOT NULL	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).
17 SQL_DATETIME_SUB	SMALLINT	This field is always NULL (DB2 CLI does not support interval and datetime data types).
18 NUM_PREC_RADIX	INTEGER	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.
19 INTERVAL_PRECISION	SMALLINT	This field is always NULL (DB2 CLI does not support interval data types).

SQLSTATE	Description	Explanation
24000	Cursor state that is not valid	A cursor is already opened on the statement handle. StatementHandle has not been closed.
40003 08S01	Communication link failure	The communication link between the application and data source fails before the function is completed.
HY001	Memory allocation failure	DB2 UDB CLI is unable to allocate memory required to support the processing or completion of the function.
HY004	SQL data type out of range	A DataType that is not valid is specified.
HY010	Function sequence error	The function is called while in a data-at-processing (`SQLParamData()`, `SQLPutData()`) operation.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYT00	Timeout expired