SQL symbolic and default data types for CLI applications

The SQL data types that are available for use with the Call Level Interface (CLI) applications, its corresponding symbolic name, and the default C symbolic name.

The following table lists the SQL data types.

SQL data type
This column contains the SQL data types as they would display in an SQL CREATE statement. The SQL data types are dependent on the DBMS.
Symbolic SQL data type
This column contains the SQL symbolic names that are defined (in sqlcli.h) as an integer value. Various functions use these values to identify the SQL data types that are listed in the first column.
Default C symbolic data type
This column contains the C symbolic names, which are also defined as integer values. These values are used in various function arguments to identify the C data type. Various functions use the symbolic names, such as SQLBindParameter(), SQLGetData(), and SQLBindCol(), to indicate the C data types of the application variables. Instead of explicitly identifying C data types when calling these functions, you can specify SQL_C_DEFAULT instead, and CLI assumes a default C data type based on the SQL data type of the parameter or column as shown by the following table. For example, the default C data type of SQL_DECIMAL is SQL_C_CHAR.

Applications should not use the SQL_C_DEFAULT data type to define C data types because it is less efficient for CLI. Explicitly indicating the C data type in the application is preferred, because it yields better performance than using the SQL_C_DEFAULT data type.

Table 1. SQL symbolic and default data types
SQL data type Symbolic SQL data type Default symbolic C data type
BIGINT SQL_BIGINT SQL_C_SBIGINT
BINARY f SQL_BINARY SQL_C_BINARY
BLOB SQL_BLOB SQL_C_BINARY
BLOB LOCATOR a SQL_BLOB_LOCATOR SQL_C_BLOB_LOCATOR
BOOLEAN d SQL_BOOLEAN SQL_C_DEFAULT
CHAR SQL_CHAR SQL_C_CHAR
CHAR SQL_TINYINT SQL_C_TINYINT
CHAR FOR BIT DATA b SQL_BINARY SQL_C_BINARY
CHAR FOR BIT DATA SQL_BIT SQL_C_BINARY
CLOB SQL_CLOB SQL_C_CHAR
CLOB LOCATOR a SQL_CLOB_LOCATOR SQL_C_CLOB_LOCATOR
CURSOR d, g SQL_CURSORHANDLE SQL_C_CURSORHANDLE
DATE SQL_TYPE_DATE SQL_C_TYPE_DATE
DBCLOB SQL_DBCLOB SQL_C_DBCHAR
DBCLOB LOCATOR a SQL_DBCLOB_LOCATOR SQL_C_DBCLOB_LOCATOR
DECIMAL SQL_DECIMAL SQL_C_CHAR
DECFLOAT(16) SQL_DECFLOAT SQL_C_CHAR
DECFLOAT(34) SQL_DECFLOAT SQL_C_CHAR
DOUBLE SQL_DOUBLE SQL_C_DOUBLE
FLOAT SQL_FLOAT SQL_C_DOUBLE
GRAPHIC SQL_GRAPHIC SQL_C_DBCHAR
INTEGER SQL_INTEGER SQL_C_LONG
LONG VARCHAR b SQL_LONGVARCHAR SQL_C_CHAR
LONG VARCHAR FOR BIT DATA b SQL_LONGVARBINARY SQL_C_BINARY
LONG VARGRAPHIC b SQL_LONGVARGRAPHIC SQL_C_DBCHAR
LONG VARGRAPHIC b SQL_WLONGVARCHAR SQL_C_DBCHAR
NUMERIC c SQL_NUMERIC c SQL_C_CHAR
REAL SQL_REAL SQL_C_FLOAT
ROW d SQL_ROW SQL_C_DEFAULT
SMALLINT SQL_SMALLINT SQL_C_SHORT
TIME SQL_TYPE_TIME SQL_C_TYPE_TIME
TIMESTAMP SQL_TYPE_TIMESTAMP SQL_C_TYPE_TIMESTAMP
TIMESTAMP WITH TIMEZONE SQL_TYPE_TIMESTAMP_ WITH_ TIMEZONE SQL_C_TYPE_TIMESTAMP_ EXT_TZ
VARBINARY f SQL_VARBINARY SQL_C_BINARY
VARCHAR SQL_VARCHAR SQL_C_CHAR
VARCHAR FOR BIT DATA b SQL_VARBINARY SQL_C_BINARY
VARGRAPHIC SQL_VARGRAPHIC SQL_C_DBCHAR
VARGRAPHIC SQL_WVARCHAR SQL_C_DBCHAR
WCHAR SQL_WCHAR SQL_C_WCHAR
XML e SQL_XML SQL_C_BINARY
  • a LOB locator types are not persistent SQL data types. Columns cannot be defined with a locator type as they are used only to describe parameter markers, or to represent a LOB value.
  • b LONG data types and FOR BIT DATA data types should be replaced by an appropriate LOB type whenever possible.
  • c NUMERIC is a synonym for DECIMAL on Db2® for z/OS® (Version 9.1 and later), Db2 Server for VSE & VM and Db2.
  • d BOOLEAN, CURSOR, and ROW types are only supported to provide applications with correct DESCRIBE information for database table columns or procedure parameters. No bind-in or bind-out is supported for these types. These types are recognized by the SQLDescribeParam() function.
  • e The SQL_XML data type is supported for Db2 for IBM® i Version 7 Release 1 servers or later releases.
  • f The SQL_BINARY and SQL_VARBINARY data types are supported for Db2 for IBM i Version 6 Release 1 servers or later releases.
  • g The SQL_C_CURSORHANDLE C data type is supported for use with the SQL_CURSORHANDLE SQL data type. Only the SQL_C_DEFAULT and SQL_C_CURSORHANDLE C data types are supported with the SQL_CURORHANDLE SQL data type. Binding of the SQL_C_CURSORHANDLE C data type is supported with Db2 servers Version 9.7 and later.
Note:

You cannot transfer the data types DATE, DECIMAL, DECFLOAT(16), DECFLOAT(34), NUMERIC, TIME, and TIMESTAMP to their default C buffer types without a conversion.