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.
Syntax
SQLRETURN SQLGetTypeInfo (SQLHSTMT StatementHandle,
SQLSMALLINT DataType);
Function arguments
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | StatementHandle | Input | Statement handle |
SQLSMALLINT | DataType | Input | The SQL data type being queried.
The supported types are:
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.
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
|
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:
|
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
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
Data type | DataType |
---|---|
TINY INT | SQL_TINYINT |
BIT | SQL_BIT |
Example
/* 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 ) ;