Equivalent SQL and C data types

When you declare host variables in your C programs, the precompiler uses equivalent SQL data types. When you retrieve data of a particular SQL data type into a host variable, you need to ensure that the host variable is of an equivalent data type.

The following table describes the SQL data type and the base SQLTYPE and SQLLEN values that the precompiler uses for host variables in SQL statements.

Table 1. SQL data types, SQLLEN values, and SQLTYPE values that the precompiler uses for host variables in C programs
C host variable data type	SQLTYPE of host variable¹	SQLLEN of host variable	SQL data type
short int	500	2	SMALLINT
long int	496	4	INTEGER
long long long long int sqlint64	492	8	BIGINT⁵
decimal(p,s)²	484	p in byte 1, s in byte 2	DECIMAL(p,s)²
_Decimal32	996/997	4	DECFLOAT(16)^{7, 8}
_Decimal64	996/997	8	DECFLOAT(16)⁸
_Decimal128	996/997	16	DECFLOAT(34)⁸
float	480	4	FLOAT (single precision)
double	480	8	FLOAT (double precision)
SQL TYPE IS BINARY(n), 1<=n<=255	912	n	BINARY(n)
SQL TYPE IS VARBINARY(n), 1<=n<=32704	908	n	VARBINARY(n)
Single-character form	452	1	CHAR(1)
NUL-terminated character form	460	n	VARCHAR (n-1)
VARCHAR structured form 1<=n<=255	448	n	VARCHAR(n)
VARCHAR structured form n>255	456	n	VARCHAR(n)
Single-graphic form	468	1	GRAPHIC(1)
NUL-terminated graphic form	400	n	VARGRAPHIC (n-1)
VARGRAPHIC structured form 1<=n<128	464	n	VARGRAPHIC(n)
VARGRAPHIC structured form n>127	472	n	VARGRAPHIC(n)
SQL TYPE IS RESULT_SET _LOCATOR	972	4	Result set locator³
SQL TYPE IS TABLE LIKE table-name AS LOCATOR	976	4	Table locator³
SQL TYPE IS BLOB_LOCATOR	960	4	BLOB locator³
SQL TYPE IS CLOB_LOCATOR	964	4	CLOB locator³
SQL TYPE IS DBCLOB_LOCATOR	968	4	DBCLOB locator³
SQL TYPE IS BLOB(n) 1≤n≤2147483647	404	n	BLOB(n)
SQL TYPE IS CLOB(n) 1≤n≤2147483647	408	n	CLOB(n)
SQL TYPE IS DBCLOB(n) 1≤n≤1073741823	412	n	DBCLOB(n)⁴
SQL TYPE IS XML AS BLOB(n)	404	0	XML
SQL TYPE IS XML AS CLOB(n)	408	0	XML
SQL TYPE IS XML AS DBCLOB(n)	412	0	XML
SQL TYPE IS BLOB_FILE	916/917	267	BLOB file reference ³
SQL TYPE IS CLOB_FILE	920/921	267	CLOB file reference ³
SQL TYPE IS DBCLOB_FILE	924/925	267	DBCLOB file reference ³
SQL TYPE IS XML AS BLOB_FILE	916/917	267	XML BLOB file reference ³
SQL TYPE IS XML AS CLOB_FILE	920/921	267	XML CLOB file reference ³
SQL TYPE IS XML AS DBCLOB_FILE	924/925	267	XML DBCLOB file reference ³
SQL TYPE IS ROWID	904	40	ROWID
Notes: If a host variable includes an indicator variable, the SQLTYPE value is the base SQLTYPE value plus 1. p is the precision; in SQL terminology, this the total number of digits. In C, this is called the size. s is the scale; in SQL terminology, this is the number of digits to the right of the decimal point. In C, this is called the precision. C++ does not support the decimal data type. Do not use this data type as a column type. `n` is the number of double-byte characters. No exact equivalent. Use DECIMAL(19,0). The C data type long maps to the SQL data type BIGINT. DFP host variable with a length of 4 is supported while DFP column can be defined only with length 8(DECFLOAT(16)) or 16(DECFLOAT(34)). To use the decimal floating-point host data type, you must do the following: Use z/OS® 1.10 or above (z/OS V1R10 XL C/C++ ). Compile with the C/C++ compiler option, DFP. Specify the SQL compiler option to enable the DB2® coprocessor. Specify C/C++ compiler option, ARCH(7). It is required by the DFP compiler option if the DFP type is used in the source. Specify 'DEFINE(__STDC_WANT_DEC_FP__)' compiler option because DFP is not officially part of the C/C++ Language Standard.

The following table shows equivalent C host variables for each SQL data type. Use this table to determine the C data type for host variables that you define to receive output from the database. For example, if you retrieve TIMESTAMP data, you can define a variable of NUL-terminated character form or VARCHAR structured form

This table shows direct conversions between SQL data types and C data types. However, a number of SQL data types are compatible. When you do assignments or comparisons of data that have compatible data types, DB2 converts those compatible data types.

Table 2. C host variable equivalents that you can use when retrieving data of a particular SQL data type
SQL data type	C host variable equivalent	Notes
SMALLINT	short int
INTEGER	long int
DECIMAL(p,s) or NUMERIC(p,s)	decimal	You can use the double data type if your C compiler does not have a decimal data type; however, double is not an exact equivalent.
REAL or FLOAT(n)	float	1<=n<=21
DOUBLE PRECISION or FLOAT(n)	double	22<=n<=53
DECFLOAT(16)	_Decminal32
DECFLOAT(34)	_Decimal128
BIGINT	long long, long long int, and sqlint64
BINARY(n)	SQL TYPE IS BINARY(n)	1<=n<=255 If data can contain character NULs (\0), certain C and C++ library functions might not handle the data correctly. Ensure that your application handles the data properly.
VARBINARY(n)	SQL TYPE IS VARBINARY(n)	1<=n<=32 704
CHAR(1)	single-character form
CHAR(n)	no exact equivalent	If n>1, use NUL-terminated character form
VARCHAR(n)	NUL-terminated character form	If data can contain character NULs (\0), use VARCHAR structured form. Allow at least n+1 to accommodate the NUL-terminator.
VARCHAR(n)	VARCHAR structured form
GRAPHIC(1)	single-graphic form
GRAPHIC(n)	no exact equivalent	If n>1, use NUL-terminated graphic form. n is the number of double-byte characters.
VARGRAPHIC(n)	NUL-terminated graphic form	If data can contain graphic NUL values (\0\0), use VARGRAPHIC structured form. Allow at least n+1 to accommodate the NUL-terminator. n is the number of double-byte characters.
VARGRAPHIC(n)	VARGRAPHIC structured form	n is the number of double-byte characters.
DATE	NUL-terminated character form	If you are using a date exit routine, that routine determines the length. Otherwise, allow at least 11 characters to accommodate the NUL-terminator.
DATE	VARCHAR structured form	If you are using a date exit routine, that routine determines the length. Otherwise, allow at least 10 characters.
TIME	NUL-terminated character form	If you are using a time exit routine, the length is determined by that routine. Otherwise, the length must be at least 7; to include seconds, the length must be at least 9 to accommodate the NUL-terminator.
TIME	VARCHAR structured form	If you are using a time exit routine, the length is determined by that routine. Otherwise, the length must be at least 6; to include seconds, the length must be at least 8.
TIMESTAMP	NUL-terminated character form	The length must be at least 20. To include microseconds, the length must be 27. If the length is less than 27, truncation occurs on the microseconds part.
TIMESTAMP	VARCHAR structured form	The length must be at least 19. To include microseconds, the length must be 26. If the length is less than 26, truncation occurs on the microseconds part.
TIMESTAMP(0)	NUL-terminated character form	The length must be at least 20.
TIMESTAMP(0)	VARCHAR structured form	The length must be at least 19.
TIMESTAMP(`p`) `p` > 0	NUL-terminated character form	The length must be at least 20. To include fractional seconds, the length must be 21+`x` where `x` is the number of fractional seconds to include; if `x` is less than `p`, truncation occurs on the fraction seconds part.
TIMESTAMP(`p`) `p` > 0	VARCHAR structured form	The length must be at least 19. To include fractional seconds, the length must be 20+`x` where `x` is the number of fractional seconds to include; if `x` is less than `p`, truncation occurs on the fractional seconds part.
TIMESTAMP(0) WITH TIME ZONE	NUL-terminated character form	The length must be at least 26.
TIMESTAMP(0) WITH TIME ZONE	VARCHAR structured form	The length must be at least 25.
TIMESTAMP(`p`) WITH TIME ZONE	NUL-terminated character form	The length must be at least 27+`p`.
TIMESTAMP(`p`) WITH TIME ZONE	VARCHAR structured form	The length must be at least 26+`p`.
Result set locator	SQL TYPE IS RESULT_SET_LOCATOR	Use this data type only for receiving result sets. Do not use this data type as a column type.
Table locator	SQL TYPE IS TABLE LIKE table-name AS LOCATOR	Use this data type only in a user-defined function or stored procedure to receive rows of a transition table. Do not use this data type as a column type.
BLOB locator	SQL TYPE IS BLOB_LOCATOR	Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB locator	SQL TYPE IS CLOB_LOCATOR	Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB locator	SQL TYPE IS DBCLOB_LOCATOR	Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
BLOB(n)	SQL TYPE IS BLOB(n)	1≤n≤2147483647
CLOB(n)	SQL TYPE IS CLOB(n)	1≤n≤2147483647
DBCLOB(n)	SQL TYPE IS DBCLOB(n)	n is the number of double-byte characters. 1≤n≤1073741823
XML	SQL TYPE IS XML AS BLOB(n)	1≤n≤2147483647
XML	SQL TYPE IS XML AS CLOB(n)	1≤n≤2147483647
XML	SQL TYPE IS XML AS DBCLOB(n)	n is the number of double-byte characters. 1≤n≤1073741823
BLOB file reference	SQL TYPE IS BLOB_FILE	Use this data type only to manipulate data in BLOB columns. Do not use this data type as a column type.
CLOB file reference	SQL TYPE IS CLOB_FILE	Use this data type only to manipulate data in CLOB columns. Do not use this data type as a column type.
DBCLOB file reference	SQL TYPE IS DBCLOB_FILE	Use this data type only to manipulate data in DBCLOB columns. Do not use this data type as a column type.
XML BLOB file reference	SQL TYPE IS XML AS BLOB_FILE	Use this data type only to manipulate XML data as BLOB files. Do not use this data type as a column type.
XML CLOB file reference	SQL TYPE IS XML AS CLOB_FILE	Use this data type only to manipulate XML data as CLOB files. Do not use this data type as a column type.
XML DBCLOB file reference	SQL TYPE IS XML AS DBCLOB_FILE	Use this data type only to manipulate XML data as DBCLOB files. Do not use this data type as a column type.
ROWID	SQL TYPE IS ROWID