SQLGetTypeInfo - Get data type information

SQLGetTypeInfo() returns information about the data types that are supported by the Database Management Systems (DBMSs) associated with DB2® for i 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.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLGetTypeInfoW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

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_DECFLOAT 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). 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 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_UNIQUE_VAL	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 for i CLI does not support).
17 SQL_DATETIME_SUB	SMALLINT	This field is always NULL (DB2 for i 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 for i 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 for i 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

Note: 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 ) ;