DB2 Version 9.7 for Linux, UNIX, and Windows

Supported SQL data types in C and C++ embedded SQL applications

Certain predefined C and C++ data types correspond to DB2® database column types. Only these C and C++ data types can be declared as host variables.

The following tables show the C and C++ equivalent of each column type. When the precompiler finds a host variable declaration, it determines the appropriate SQL type value. The database manager uses this value to convert the data exchanged between the application and itself.

Table 1. SQL Data Types Mapped to C and C++ Declarations
SQL Column Type1 C and C++ Data Type SQL Column Type Description

SMALLINT
(500 or 501)

short
short int
sqlint16

 16-bit signed integer

INTEGER
(496 or 497)

int
long
long int
sqlint322

 32-bit signed integer

BIGINT
(492 or 493)

long long
long
__int64
sqlint643

 64-bit signed integer

REAL5
(480 or 481)

float Single-precision floating point

DOUBLE6
(480 or 481)

double Double-precision floating point

DECIMAL(p,s)
(484 or 485)

No exact equivalent; use double Packed decimal

(Consider using the CHAR and DECIMAL functions to manipulate packed decimal fields as character data.)

CHAR(1)
(452 or 453)

char Single character

CHAR(n)
(452 or 453)

No exact equivalent; use char[n+1] where n is large enough to hold the data

1<=n<=254

 Fixed-length character string

VARCHAR(n)
(448 or 449)

struct tag {
   short int;
   char[n]
   }
 
1<=n<=32 672

 Non null-terminated varying character string with 2-byte string length indicator.
Note: A host variable structure of the following form is always treated as a VARCHAR host variable and cannot be declared: 
struct tag { 
   short int; 
   char[n] 
   }
Alternatively, use char[n+1] where n is large enough to hold the data

1<=n<=32 672

 Null-terminated variable-length character string
Note: Assigned an SQL type of 460/461.

LONG VARCHAR8
(456 or 457)

struct tag {
   short int;
   char[n]
   }
 
32 673<=n<=32 700

 Non null-terminated varying character string with 2-byte string length indicator

CLOB(n)
(408 or 409)

sql type is
   clob(n)
 
1<=n<=2 147 483 647

 Non null-terminated varying character string with 4-byte string length indicator

CLOB locator variable7
(964 or 965)

sql type is
   clob_locator

 Identifies CLOB entities residing on the server

CLOB file reference variable7
(920 or 921)

sql type is
   clob_file

 Descriptor for file containing CLOB data

BLOB(n)
(404 or 405)

sql type is
   blob(n)
 
1<=n<=2 147 483 647

 Non null-terminated varying binary string with 4-byte string length indicator

BLOB locator variable7
(960 or 961)

sql type is
   blob_locator

 Identifies BLOB entities on the server

BLOB file reference variable7
(916 or 917)

sql type is
   blob_file

 Descriptor for the file containing BLOB data

DATE
(384 or 385)

 Null-terminated character form Allow at least 11 characters to accommodate the null-terminator
VARCHAR structured form Allow at least 10 characters

TIME
(388 or 389)

 Null-terminated character form Allow at least 9 characters to accommodate the null-terminator
VARCHAR structured form Allow at least 8 characters

TIMESTAMP(p)
4(392 or 393)

 Null-terminated character form Allow 20- 33 characters to accommodate for the null-terminator
VARCHAR structured form

Allow 19-32 characters.

XML8
(988 or 989)

struct {
  sqluint32 length;
  char      data[n];
}
 
1<=n<=2 147 483 647
 
SQLUDF_CLOB

 XML value

BINARY

unsigned char myBinField[4];

1<= n <=255

 Binary data

VARBINARY

struct
myVarBinField_t
{sqluint16 length;char data[12];}
myVarBinField;

1<= n <=32704

 Varbinary data

The following data types are only available in the DBCS or EUC environment when precompiled with the WCHARTYPE NOCONVERT option.

Table 2. SQL Data Types Mapped to C and C++ Declarations
SQL Column Type1 C and C++ Data Type SQL Column Type Description

GRAPHIC(1)
(468 or 469)

 sqldbchar Single double-byte character

GRAPHIC(n)
(468 or 469)

 No exact equivalent; use sqldbchar[n+1] where n is large enough to hold the data

1<=n<=127

 Fixed-length double-byte character string

VARGRAPHIC(n)
(464 or 465)

struct tag {
   short int;
   sqldbchar[n]
   }
 
1<=n<=16 336

 Non null-terminated varying double-byte character string with 2-byte string length indicator
Alternatively use sqldbchar[n+1] where n is large enough to hold the data

1<=n<=16 336

 Null-terminated variable-length double-byte character string
Note: Assigned an SQL type of 400/401.

LONG VARGRAPHIC8
(472 or 473)

struct tag {
   short int;
   sqldbchar[n]
   }
 
16 337<=n<=16 350

 Non null-terminated varying double-byte character string with 2-byte string length indicator

The following data types are only available in the DBCS or EUC environment when precompiled with the WCHARTYPE CONVERT option.

Table 3. SQL Data Types Mapped to C and C++ Declarations
SQL Column Type1 C and C++ Data Type SQL Column Type Description

GRAPHIC(1)
(468 or 469)

 wchar_t
  • Single wide character (for C-type)
  • Single double-byte character (for column type)

GRAPHIC(n)
(468 or 469)

 No exact equivalent; use wchar_t [n+1] where n is large enough to hold the data

1<=n<=127

 Fixed-length double-byte character string

VARGRAPHIC(n)
(464 or 465)

struct tag {
   short int;
   wchar_t [n]
   }
 
1<=n<=16 336

 Non null-terminated varying double-byte character string with 2-byte string length indicator
Alternately use char[n+1] where n is large enough to hold the data

1<=n<=16 336

 Null-terminated variable-length double-byte character string
Note: Assigned an SQL type of 400/401.

LONG VARGRAPHIC8
(472 or 473)

struct tag {
   short int;
   wchar_t [n]
   }
 
16 337<=n<=16 350

 Non null-terminated varying double-byte character string with 2-byte string length indicator

The following data types are only available in the DBCS or EUC environment.

Table 4. SQL Data Types Mapped to C and C++ Declarations
SQL Column Type1 C and C++ Data Type SQL Column Type Description

DBCLOB(n)
(412 or 413)

sql type is
   dbclob(n)
 
1<=n<=1 073 741 823

 Non null-terminated varying double-byte character string with 4-byte string length indicator

DBCLOB locator variable7
(968 or 969)

sql type is
   dbclob_locator

 Identifies DBCLOB entities residing on the server

DBCLOB file reference
variable7
(924 or 925)

sql type is
   dbclob_file

 Descriptor for file containing DBCLOB data
Note:
  1. The first number under SQL Column Type indicates that an indicator variable is not provided, and the second number indicates that an indicator variable is provided. An indicator variable is needed to indicate NULL values, or to hold the length of a truncated string. These are the values that will be displayed in the SQLTYPE field of the SQLDA for these data types.
  2. For platform compatibility, use sqlint32. On 64-bit UNIX and Linux operating systems, "long" is a 64 bit integer. On 64-bit Windows operating systems and 32-bit UNIX and Linux operating systems "long" is a 32 bit integer.
  3. For platform compatibility, use sqlint64. The DB2 database system sqlsystm.h header file has a type definition for sqlint64 as "__int64" on the supported Windows operating systems when using the Microsoft compiler, "long long" on 32-bit UNIX and Linux operating systems, and "long" on 64 bit UNIX and Linux operating systems.

  4. The character string can be from 19 - 32 bytes in length without a null terminator depending on the number of fractional seconds specified. The fractional seconds of the TIMESTAMP data type can be optionally specified with 0-12 digits of timestamp precision.

    When a timestamp value is assigned to a timestamp variable with a different number of fractional seconds, the value is either truncated or padded with 0's to match the format of the timestamp variable.

  5. FLOAT(n) where 0 < n < 25 is a synonym for REAL. The difference between REAL and DOUBLE in the SQLDA is the length value (4 or 8).
  6. The following SQL types are synonyms for DOUBLE:
    • FLOAT
    • FLOAT(n) where 24 < n < 54 is a synonym for DOUBLE
    • DOUBLE PRECISION
  7. This is not a column type but a host variable type.
  8. The SQL_TYP_XML/SQL_TYP_NXML value is returned by DESCRIBE requests only. It cannot be used directly by the application to bind application resources to XML values.
  9. The LONG VARCHAR and LONG VARGRAPHIC data types are deprecated and might be removed in a future release. Choose the CLOB or DBCLOB data type instead.
The following are additional rules for supported C and C++ data types:
  • The data type char can be declared as char or unsigned char.
  • The database manager processes null-terminated variable-length character string data type char[n] (data type 460), as VARCHAR(m).
    • If LANGLEVEL is SAA1, the host variable length m equals the character string length n in char[n] or the number of bytes preceding the first null-terminator (\0), whichever is smaller.
    • If LANGLEVEL is MIA, the host variable length m equals the number of bytes preceding the first null-terminator (\0).
  • The database manager processes null-terminated, variable-length graphic string data type, wchar_t[n] or sqldbchar[n] (data type 400®), as VARGRAPHIC(m).
    • If LANGLEVEL is SAA1, the host variable length m equals the character string length n in wchar_t[n] or sqldbchar[n], or the number of characters preceding the first graphic null-terminator, whichever is smaller.
    • If LANGLEVEL is MIA, the host variable length m equals the number of characters preceding the first graphic null-terminator.
  • Unsigned numeric data types are not supported.
  • The C and C++ data type int is not allowed because its internal representation is machine dependent.
Parent topic: Data types that map to SQL data types in embedded SQL applications
Related reference:
C++ samples
C samples