`SQLGetTypeInfo()` - Get data type information

SQLGetTypeInfo() returns information about the data types that are supported by the database management systems that are associated with Db2 ODBC. This information is returned in an SQL result set. The columns of this result set can be received by using the same functions that you use to process a query.

ODBC specifications for `SQLGetTypeInfo()`

Table 1. `SQLGetTypeInfo()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	Yes

Syntax

SQLRETURN   SQLGetTypeInfo   (SQLHSTMT          hstmt,
                              SQLSMALLINT       fSqlType);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLGetTypeInfo()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Specifies a statement handle.
SQLSMALLINT	fSqlType	input	Specifies the SQL data type that is queried. The following values that specify data types are supported: SQL_ALL_TYPES SQL_BIGINT SQL_BINARY SQL_BLOB SQL_CHAR SQL_CLOB SQL_DBCLOB SQL_DECFLOAT SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_NUMERIC SQL_REAL SQL_ROWID SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP_WITH_TIMEZONE SQL_VARBINARY SQL_VARCHAR SQL_VARGRAPHIC SQL_XML If the value 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 it is essentially equivalent to executing a query. Like a query, calling SQLGetTypeInfo() generates a cursor and begins a transaction. To prepare and execute another statement on this statement handle, the cursor must be closed.

If you call SQLGetTypeInfo() with an invalid value in the fSqlType argument, an empty result set is returned.

Table 3 describes each column in the result set that this function generates.

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 or ALTER TABLE, statement. Nonpersistent 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 3. Columns returned by `SQLGetTypeInfo()`
Position	Column name	Data type	Description
1	TYPE_NAME	VARCHAR(128) NOT NULL	Contains a character representation of the SQL Data Definition Language data type name. For example, VARCHAR, BLOB, DATE, INTEGER.
2	DATA_TYPE	SMALLINT NOT NULL	Contains the SQL data type definition values. For example, SQL_VARCHAR, SQL_BLOB, SQL_TYPE_DATE, SQL_INTEGER.
3	COLUMN_SIZE	INTEGER	If the data type is a character or binary string, this column contains the maximum length in bytes. If this data type is a graphic (DBCS) string, this column contains the number of double-byte characters for the column. If the data type is XML, zero is returned. For date, time, timestamp data types, this is the total number of characters required to display the value when converted to characters. For numeric data types, this column contains the total number of digits.
4	LITERAL_PREFIX	VARCHAR(128)	Contains the 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)	Contains the character that Db2 recognizes as a suffix for a literal of this data type. This column is null for data types where a literal suffix is not applicable.
6	CREATE_PARAMS	VARCHAR(128)	Contains a list of values, that are separated by commas. These values correspond to each parameter that you can specify for a data type in a CREATE TABLE or an ALTER TABLE SQL statement. One or more of the following values appear in this result-set column: LENGTH, which indicates you can specify a length for the data type in the TYPE_NAME column PRECISION, which indicates you can specify the precision for the data type in the TYPE_NAME column SCALE, which indicates you can specify a scale for the data type in the TYPE_NAME column A null indicator, which indicates you cannot specify any parameters for the data type in the TYPE_NAME column Usage: The CREATE_PARAMS column enables you to customize the interface of Data Definition Language builders in your applications. A Data Definition Language builder is a piece of your application that creates database objects, such as tables. Use the CREATE_PARAMS to determine the number of arguments that are required to define a data type, then use localized text to label the controls on the Data Definition Language builder.
7	NULLABLE	SMALLINT NOT NULL	Indicates whether the data type accepts a null value. This column contains one of the following values: SQL_NO_NULLS, which indicates that null values are disallowed SQL_NULLABLE, which indicates that null values are allowed
8	CASE_SENSITIVE	SMALLINT NOT NULL	Indicates whether the data type can be treated as case sensitive for collation purposes. This column contains one of the following values: SQL_TRUE, which indicates case sensitivity SQL_FALSE, which indicates no case sensitivity
9	SEARCHABLE	SMALLINT NOT NULL	Indicates how the data type is used in a WHERE clause. This column contains one of the following values: SQL_UNSEARCHABLE, which indicates that you cannot use the data type in a WHERE clause SQL_LIKE_ONLY, which indicates that you can use the data type in a WHERE clause, but only with the LIKE predicate. SQL_ALL_EXCEPT_LIKE, which indicates that you can use the data type in a WHERE clause with all comparison operators except LIKE. SQL_SEARCHABLE, which indicates that you can use the data type in a WHERE clause with any comparison operator.
10	UNSIGNED_ATTRIBUTE	SMALLINT	Indicates whether the data type is unsigned. This column contains one of the following values: SQL_TRUE, which indicates that the data type is unsigned SQL_FALSE, which indicates the data type is signed NULL, which indicates this attribute does not apply 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)	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 contains a null indicator. 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	SMALLINT	Contains 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	SMALLINT	Contains 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 database management system, 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.

Return codes

After you call SQLGetTypeInfo(), it returns one of the following values:

SQL_SUCCESS
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLGetTypeInfo()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is open on the statement handle.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY004	Invalid SQL data type.	An invalid value for the fSqlType argument is specified.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)

Restrictions

The following ODBC specified SQL data types (and their corresponding fSqlType define values) are not supported by any IBM® relational database management system:

Data type: fSqlType
TINYINT: SQL_TINYINT
BIT: SQL_BIT

Example

The following example shows an application that uses SQLGetTypeInfo() to check which ODBC data types the database management system supports.

Figure 1. An application that checks data types that the current server supports

 /******************************************************************/
 /*  Invoke SQLGetTypeInfo to retrieve SQL data types supported.   */
 /******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "sqlcli1.h"
  /******************************************************************/
  /* Invoke SQLGetTypeInfo to retrieve all SQL data types supported */
  /* by data source.                                                */
  /******************************************************************/
int main( )
{
   SQLHENV         hEnv    = SQL_NULL_HENV;
   SQLHDBC         hDbc    = SQL_NULL_HDBC;
   SQLHSTMT        hStmt   = SQL_NULL_HSTMT;
   SQLRETURN       rc      = SQL_SUCCESS;
   SQLINTEGER      RETCODE = 0;
   (void) printf ("**** Entering CLIP06.\n\n");
  /*****************************************************************/
  /* Allocate environment handle                                   */
  /*****************************************************************/
   RETCODE = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Allocate connection handle to DSN                             */
  /*****************************************************************/
   RETCODE = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
   if( RETCODE != SQL_SUCCESS )      // Could not get a Connect Handle
     goto dberror;
  /*****************************************************************/
  /* CONNECT TO data source (STLEC1)                               */
  /*****************************************************************/
   RETCODE = SQLConnect(hDbc,        // Connect handle
                        (SQLCHAR *) "STLEC1", // DSN
                        SQL_NTS,     // DSN is nul-terminated
                        NULL,        // Null UID
                        0   ,
                        NULL,        // Null Auth string
                        0);
   if( RETCODE != SQL_SUCCESS )      // Connect failed
     goto dberror;
  /*****************************************************************/
  /* Retrieve SQL data types from DSN                              */
  /*****************************************************************/
  // local variables to Bind to retrieve TYPE_NAME, DATA_TYPE,
  // COLUMN_SIZE and NULLABLE
   struct                    // TYPE_NAME is VARCHAR(128)
   {
     SQLSMALLINT  length;
     SQLCHAR      name [128];
     SQLINTEGER   ind;
   } typename;
   SQLSMALLINT data_type;    // DATA_TYPE is SMALLINT
   SQLINTEGER  data_type_ind;
   SQLINTEGER  column_size;  // COLUMN_SIZE is integer
   SQLINTEGER  column_size_ind;
   SQLSMALLINT nullable;     // NULLABLE is SMALLINT
   SQLINTEGER  nullable_ind;
  /*****************************************************************/
  /* Allocate statement handle                                     */
  /*****************************************************************/
   rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
   if (rc != SQL_SUCCESS)
     goto exit;
  /*****************************************************************/
  /*                                                               */
  /* Retrieve native SQL types from DSN ------------>              */
  /*                                                               */
  /*  The result set consists of 15 columns. We only bind          */
  /*  TYPE_NAME, DATA_TYPE, COLUMN_SIZE and NULLABLE. Note: Need   */
  /*  not bind all columns of result set -- only those required.   */
  /*                                                               */
  /*****************************************************************/
   rc = SQLGetTypeInfo (hStmt,
                        SQL_ALL_TYPES);
   if (rc != SQL_SUCCESS)
     goto exit;
   rc = SQLBindCol (hStmt,           // bind TYPE_NAME
                    1,
                    SQL_CHAR,
                    (SQLPOINTER) typename.name,
                    128,
                    &typename.ind);
   if (rc != SQL_SUCCESS)
     goto exit;
   rc = SQLBindCol (hStmt,           // bind DATA_NAME
                    2,
                    SQL_C_DEFAULT,
                    (SQLPOINTER) &data_type,
                    sizeof(data_type),
                    &data_type_ind);
   if (rc != SQL_SUCCESS)
     goto exit;rc = SQLBindCol (hStmt,           // bind COLUMN_SIZE
                    3,
                    SQL_C_DEFAULT,
                    (SQLPOINTER) &column_size,
                    sizeof(column_size),
                    &column_size_ind);
   if (rc != SQL_SUCCESS)
     goto exit;
   rc = SQLBindCol (hStmt,           // bind NULLABLE
                    7,
                    SQL_C_DEFAULT,
                    (SQLPOINTER) &nullable,
                    sizeof(nullable),
                    &nullable_ind);
   if (rc != SQL_SUCCESS)
     goto exit;
  /*****************************************************************/
  /* Fetch all native DSN SQL Types and print Type Name, Type,     */
  /* Precision and nullability.                                    */
  /*****************************************************************/
   while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
   {
     (void) printf ("**** Type Name is %s. Type is %d. Precision is %d.",
                    typename.name,
                    data_type,
                    column_size);
     if (nullable == SQL_NULLABLE)
       (void) printf (" Type is nullable.\n");
     else
       (void) printf (" Type is not nullable.\n");
   }
   if (rc == SQL_NO_DATA_FOUND)   // if result set exhausted reset
     rc = SQL_SUCCESS;            // rc to OK
  /*****************************************************************/
  /* Free statement handle                                         */
  /*****************************************************************/
   rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
   if (RETCODE != SQL_SUCCESS)       // An advertised API failed
     goto dberror;
  /*****************************************************************/
  /* DISCONNECT from data source                                   */
  /*****************************************************************/
   RETCODE = SQLDisconnect(hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Deallocate connection handle                                  */
  /*****************************************************************/
   RETCODE = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
   /*****************************************************************/
  /* Free environment handle                                       */
  /*****************************************************************/
   RETCODE = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
   if (RETCODE == SQL_SUCCESS)
     goto exit;
     dberror:
     RETCODE=12;
     exit:
     (void) printf ("**** Exiting  CLIP06.\n\n");
     return(RETCODE);
}

SQLGetTypeInfo() - Get data type information

ODBC specifications for SQLGetTypeInfo()