| 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, /* hstmt */
SQLSMALLINT DataType); /* fSqlType */
Function Arguments
Table 116. 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:
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.
Table 117. 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, e.g. VARCHAR, BLOB, DATE, INTEGER | ||
| 2 DATA_TYPE | SMALLINT NOT NULL | SQL data type define values, e.g. SQL_VARCHAR, SQL_BLOB, 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 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).
| ||
| 7 NULLABLE | SMALLINT NOT NULL | Indicates whether the data type accepts a NULL value
| ||
| 8 CASE_SENSITIVE | SMALLINT NOT NULL | 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 | Indicates how the data type is used in a WHERE clause. Valid values
are:
| ||
| 10 UNSIGNED_ATTRIBUTE | SMALLINT | 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 | 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 | 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 (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. | ||
| 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
Diagnostics
Table 118. 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(). |
Restrictions
The following ODBC specified SQL data types (and their corresponding DataType define values) are not supported by any IBM RDBMS:
/* 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