C and SQL data types

Db2 ODBC defines a set of SQL symbolic data types. Each SQL symbolic data type has a corresponding default C data type.

These data types represent the combination of the ODBC 3.0 minimum, core, and extended data types. Db2 ODBC supports the following additional data types:
  • SQL_GRAPHIC
  • SQL_VARGRAPHIC
  • SQL_LONGVARGRAPHIC
Table 1 lists each of the SQL data types, with its corresponding symbolic name, and the default C symbolic name. The table contains the following columns:
SQL data type
This column contains the SQL data types as they would appear in an SQL CREATE DDL statement. The SQL data types are dependent on the database server.
Symbolic SQL data type
This column contains SQL symbolic names that are defined (in sqlcli1.h) as an integer value. These values are used by various functions to identify the SQL data types listed in the first column.
Default C symbolic data type
This column contains C symbolic names, also defined as integer values. These values are used in various function arguments to identify the C data type as shown in Table 2. The symbolic names are used by various functions (such as SQLBindParameter(), SQLGetData(), and SQLBindCol() calls) to indicate the C data types of the application variables. Instead of explicitly identifying the C data type when calling these functions, SQL_C_DEFAULT can be specified instead, and Db2 ODBC assumes a default C data type based on the SQL data type of the parameter or column, as shown by this table. For example, the default C data type of SQL_DECIMAL is SQL_C_CHAR.
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_BIGINT
BINARY SQL_BINARY SQL_C_BINARY
BLOB SQL_BLOB SQL_C_BINARY
BLOB LOCATOR1 SQL_BLOB_LOCATOR SQL_C_BLOB_LOCATOR
CHAR SQL_CHAR SQL_C_CHAR
CHAR FOR BIT DATA 6 SQL_BINARY SQL_C_BINARY
CLOB SQL_CLOB SQL_C_CHAR
CLOB LOCATOR SQL_CLOB_LOCATOR SQL_C_CLOB_LOCATOR
DATE SQL_TYPE_DATE2 SQL_C_TYPE_DATE
DBCLOB SQL_DBCLOB SQL_C_DBCHAR
DBCLOB LOCATOR1 SQL_DBCLOB_LOCATOR SQL_C_DBCLOB_LOCATOR
DECFLOAT(16) or DECFLOAT(34) SQL_DECFLOAT SQL_C_CHAR
DECIMAL SQL_DECIMAL SQL_C_CHAR
DOUBLE SQL_DOUBLE SQL_C_DOUBLE
FLOAT SQL_FLOAT SQL_C_DOUBLE
GRAPHIC SQL_GRAPHIC SQL_C_DBCHAR or SQL_C_WCHAR4
INTEGER SQL_INTEGER SQL_C_LONG
LONG VARCHAR5 SQL_LONGVARCHAR SQL_C_CHAR
LONG VARCHAR FOR BIT DATA5,6 SQL_LONGVARBINARY SQL_C_BINARY
LONG VARGRAPHIC5 SQL_LONGVARGRAPHIC SQL_C_DBCHAR or SQL_C_WCHAR4
NUMERIC7 SQL_NUMERIC7 SQL_C_CHAR
REAL SQL_REAL SQL_C_FLOAT
ROWID SQL_ROWID SQL_C_CHAR
SMALLINT SQL_SMALLINT SQL_C_SHORT
TIME SQL_TYPE_TIME2 SQL_C_TYPE_TIME
TIMESTAMP SQL_TYPE_TIMESTAMP2
SQL_C_TYPE_TIMESTAMP
SQL_C_TYPE_TIMESTAMP_EXT3
Start of changeTIMESTAMP WITH TIME ZONE End of change Start of changeSQL_TYPE_TIMESTAMP_WITH_TIMEZONE2 End of change Start of change
SQL_C_TYPE_TIMESTAMP_EXT_TZ8
End of change
VARBINARY SQL_VARBINARY SQL_C_BINARY
VARCHAR SQL_VARCHAR SQL_C_CHAR
VARCHAR FOR BIT DATA6 SQL_VARBINARY SQL_C_BINARY
VARGRAPHIC SQL_VARGRAPHIC SQL_C_DBCHAR or SQL_C_WCHAR4
XML SQL_XML SQL_C_BINARY
Notes:
  1. LOB locator types are not persistent SQL data types (columns cannot be defined by a locator type; instead, it describes parameter markers, or represents a LOB value).
  2. Changes to datetime data types have been made since previous releases.
  3. SQL_C_TYPE_TIMESTAMP_EXT is used for TIMESTAMP values in which the fractional precision is 0 to 12 digits.
  4. The default C data type conversion for this SQL data type depends upon the encoding scheme your application uses. If your application uses UCS-2 Unicode encoding, the default conversion is to SQL_C_WCHAR. For all other encoding schemes the default conversion is to SQL_C_DBCHAR.
  5. Whenever possible, replace LONG data types with LOB types.
  6. Whenever possible, replace FOR BIT DATA data types with BINARY or VARBINARY types.
  7. NUMERIC is a synonym for DECIMAL on Db2 for z/OS®, Db2 server for VSE and VM and Db2 for Linux®, UNIX, and Windows.
  8. Start of changeSQL_TYPE_TIMESTAMP_WITH_TIMEZONE and SQL_C_TYPE_TIMESTAMP_EXT_TZ are used for TIMESTAMP values with time zone in which the fractional precision is 0 to 12 digits.End of change

Additional information:

  • The data types, DATE, DECIMAL, NUMERIC, TIME, and TIMESTAMP cannot be transferred to their default C buffer types without a conversion.
Table 2 shows the generic C type definitions for each symbolic C type. The table contains the following columns:
C symbolic data type
This column contains C symbolic names, defined as integer values. These values are used in various function arguments to identify the C data type shown in the last column.
C type
This column contains C-defined types, which are defined in sqlcli1.h using a C typedef statement. The values in this column should be used to declare all Db2 ODBC related variables and arguments, in order to make the application more portable.
Base C type
This column is shown for reference only. All variables and arguments should be defined using the symbolic types in the previous column. Some of the values are C structures that are described in Table 3.
Table 2. C data types
C symbolic data type C type Base C type
SQL_C_BIGINT SQLBIGINT long long int
SQL_C_CHAR SQLCHAR Unsigned char
SQL_C_BIT SQLCHAR Unsigned char or char (Value 1 or 0)
SQL_C_TINYINT SQLSCHAR Signed char (Range -128 to 127)
SQL_C_SHORT SQLSMALLINT Short int
SQL_C_LONG SQLINTEGER Long int (31-bit) or int (64-bit)1
SQL_C_DOUBLE SQLDOUBLE Double
SQL_C_FLOAT SQLREAL Float
SQL_C_DECIMAL64 SQLDECIMAL64 See Table 3
SQL_C_DECIMAL128 SQLDECIMAL128 See Table 3
SQL_C_TYPE_DATE2 DATE_STRUCT See Table 3
SQL_C_TYPE_TIMESTAMP2 TIMESTAMP_STRUCT See Table 3
SQL_C_TYPE_TIMESTAMP_EXT2 TIMESTAMP_STRUCT_EXT See Table 3
SQL_C_TYPE_TIMESTAMP_EXT_TZ
2		 TIMESTAMP_STRUCT_EXT_TZ See Table 3
SQL_C_CLOB_LOCATOR SQLINTEGER Long int (31-bit) or int (64-bit)1
SQL_C_BINARY SQLCHAR Unsigned char
SQL_C_BINARYXML SQLCHAR Unsigned char
SQL_C_BLOB_LOCATOR SQLINTEGER Long int (31-bit) or int (64-bit)1
SQL_C_DBCHAR SQLDBCHAR Unsigned short
SQL_C_DBCLOB_LOCATOR SQLINTEGER Long int (31-bit) or int (64-bit)1
SQL_C_WCHAR SQLWCHAR wchar_t (31-bit) or unsigned short (64-bit)1
Note:
  1. 31-bit is for 31-bit applications, and 64-bit is for 64 bit applications.

    In the 31-bit environment, long int is 32 bits. In the 64-bit environment, int is also 32 bits. Therefore, the C type SQLINTEGER is mapped to a 32-bit field regardless of the environment.

    In the 31-bit environment, wchar_t is 16 bits. In the 64-bit environment, unsigned short is also 16 bits. Therefore, the C type SQLWCHAR is mapped to a 16-bit field regardless of the environment.

  2. Changes to datetime data types have been made since previous releases.

The following table lists the C data types with their associated structures for date, time, timestamp, and decimal floating point.

Table 3. C date, time, timestamp, and decimal floating point structures
C type Generic structure
DATE_STRUCT 
typedef struct DATE_STRUCT
  {
    SQLSMALLINT    year;
    SQLUSMALLINT   month;
    SQLUSMALLINT   day;
  } DATE_STRUCT;
TIME_STRUCT 
typedef struct TIME_STRUCT
  {
    SQLUSMALLINT   hour;
    SQLUSMALLINT   minute;
    SQLUSMALLINT   second;
  } TIME_STRUCT;
TIMESTAMP_STRUCT 
typedef struct TIMESTAMP_STRUCT
  {
    SQLUSMALLINT   year;
    SQLUSMALLINT   month;
    SQLUSMALLINT   day;
    SQLUSMALLINT   hour;
    SQLUSMALLINT   minute;
    SQLUSMALLINT   second;
    SQLINTEGER     fraction;
  } TIMESTAMP_STRUCT;
TIMESTAMP_STRUCT_EXT 
typedef struct TIMESTAMP_STRUCT_EXT
{
  SQLUSMALLINT year;
  SQLUSMALLINT month;
  SQLUSMALLINT day;
  SQLUSMALLINT hour;
  SQLUSMALLINT minute;
  SQLUSMALLINT second;
  SQLINTEGER fraction;
  SQLINTEGER fraction2;
} TIMESTAMP_STRUCT_EXT;
Start of changeTIMESTAMP_STRUCT_EXT_TZEnd of change Start of change
typedef struct TIMESTAMP_STRUCT_EXT_TZ
{
  SQLSMALLINT year; 
  SQLUSMALLINT month;
  SQLUSMALLINT day;
  SQLUSMALLINT hour; 
  SQLUSMALLINT minute;
  SQLUSMALLINT second;
  SQLUINTEGER fraction;  /* 1-9 digits fractional   */
                         /* seconds                 */
  SQLUINTEGER fraction2; /* 10-12 digits fractional */ 
                         /* seconds                 */
  SQLSMALLINT timezone_hour; /* -12 to 14 */
  SQLUSMALLINT timezone_minute; /* 0 to 59 */
} TIMESTAMP_STRUCT_EXT_TZ;
End of change
SQLDECIMAL64 
typedef struct SQLDECIMAL64
{
  union{
    SQLDOUBLE dummy;
    SQLCHAR dec64[8];
  }dec64;
} SQLDECIMAL64;
SQLDECIMAL128 
typedef struct SQLDECIMAL128
{
  union{
    SQLDOUBLE dummy;
    SQLCHAR dec128[16];
  }dec128;
} SQLDECIMAL128;