Data conversion

DB2® ODBC manages the transfer and any required conversion of data between the application and the database server. However, not all data conversions are supported.

Before the data transfer actually takes place, the source, target, or both data types are indicated when calling SQLBindParameter(), SQLBindCol(), or SQLGetData(). These functions use symbolic type names shown to identify the data types involved in the data transfer.

Example: The following SQLBindParameter() call binds a parameter marker that corresponds to an SQL data type of DECIMAL(5,3) to an application's C buffer type of double:

SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
                  SQL_DECIMAL, 5, 3, double_ptr, NULL);

The functions mentioned in the previous paragraph can be used to convert data to other types, but not all data conversions are supported or make sense. Table 1 shows all the conversions that DB2 ODBC supports.

Table 1 and Table 2 list the data conversions DB2 ODBC supports.

Table 1 lists the conversions by SQL type. The first column of this table contains the SQL types. The second column of this table contains the default C type that the SQL type is converted to when you specify SQL_C_DEFAULT as the target type. The last column lists all other C types that you can specify as a target in a conversion from SQL data types to C data types.

Table 1. Supported data conversions by SQL data type
SQL symbolic data type	Default C symbolic data type	Additional C symbolic data types
SQL_BIGINT	SQL_C_BIGINT	SQL_C_CHAR SQL_C_WCHAR SQL_C_SHORT SQL_C_LONG SQL_C_TINYINT SQL_C_BIT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128
SQL_BINARY	SQL_C_BINARY	SQL_C_CHAR SQL_C_WCHAR
SQL_BLOB	SQL_C_BINARY	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_BLOB_LOCATOR³
SQL_CHAR	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_LONG SQL_C_SHORT SQL_C_TINYINT SQL_C_BIGINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_TYPE_DATE SQL_C_TYPE_TIME SQL_C_TYPE_TIMESTAMP SQL_C_BINARY SQL_C_BIT
SQL_CLOB	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_BINARY SQL_C_CLOB_LOCATOR³
SQL_DBCLOB	SQL_C_DBCHAR	SQL_C_WCHAR² SQL_C_BINARY SQL_C_DBCLOB_LOCATOR³
SQL_DECFLOAT	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_LONG SQL_C_SHORT SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_BIT SQL_C_BIGINT SQL_C_DECIMAL64 SQL_C_DECIMAL128
SQL_DECIMAL	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_LONG SQL_C_SHORT SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_DOUBLE	SQL_C_DOUBLE	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_LONG SQL_C_SHORT SQL_C_TINYINT SQL_C_FLOAT SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_FLOAT	SQL_C_DOUBLE	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_LONG SQL_C_SHORT SQL_C_TINYINT SQL_C_FLOAT SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_GRAPHIC	SQL_C_DBCHAR or SQL_C_WCHAR⁴	SQL_C_CHAR¹
SQL_INTEGER	SQL_C_LONG	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_SHORT SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_LONGVARBINARY	SQL_C_BINARY	SQL_C_CHAR SQL_C_WCHAR
SQL_LONGVARCHAR	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_BINARY SQL_C_DECIMAL64 SQL_C_DECIMAL128
SQL_LONGVARGRAPHIC	SQL_C_DBCHAR or SQL_C_WCHAR⁴	SQL_C_CHAR¹
SQL_NUMERIC⁵	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_SHORT SQL_C_LONG SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_REAL	SQL_C_FLOAT	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_SHORT SQL_C_LONG SQL_C_TINYINT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BIT
SQL_ROWID	SQL_C_CHAR	SQL_C_WCHAR
SQL_SMALLINT	SQL_C_SHORT	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_LONG SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BINARY SQL_C_BIT
SQL_TYPE_DATE	SQL_C_TYPE_DATE	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_TYPE_TIMESTAMP
SQL_TYPE_TIME	SQL_C_TYPE_TIME	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_TYPE_TIMESTAMP
SQL_TYPE_TIMESTAMP	SQL_C_TYPE_TIMESTAMP	SQL_C_CHAR¹ SQL_C_WCHAR² SQL_C_TYPE_DATE SQL_C_TYPE_TIME
SQL_VARBINARY	SQL_C_BINARY	SQL_C_CHAR SQL_C_WCHAR
SQL_VARCHAR	SQL_C_CHAR¹	SQL_C_WCHAR² SQL_C_SHORT SQL_C_LONG SQL_C_TINYINT SQL_C_FLOAT SQL_C_DOUBLE SQL_C_DECIMAL64 SQL_C_DECIMAL128 SQL_C_BINARY SQL_C_BIT SQL_C_TYPE_DATE SQL_C_TYPE_TIME SQL_C_TYPE_TIMESTAMP
SQL_VARGRAPHIC	SQL_C_DBCHAR or SQL_C_WCHAR⁴	SQL_C_CHAR¹
SQL_XML	SQL_C_BINARY	SQL_C_BINARYXML SQL_C_CHAR SQL_C_DBCHAR SQL_C_WCHAR
Notes: You must bind data to the SQL_C_CHAR data type for Unicode UTF-8 data You must bind data with the SQL_C_WCHAR data type for Unicode UCS-2 data. Data is not converted to LOB locator types; locators represent a data value. 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. NUMERIC is a synonym for DECIMAL on DB2 for z/OS®, DB2 for VSE & VM, and DB2 for Linux, UNIX, and Windows.

Table 2 lists the conversions by C type. The first column of this table contains these C types. The second column of this table contains the SQL types that use the C type in the first column for default conversions. The last column are all other SQL types you can specify in a conversion from C data types to SQL data types.

Table 2. Supported data conversions by C data type
Symbolic C data type	Symbolic SQL data types that use this C data type as a default	Additional symbolic SQL data types
SQL_C_BIGINT	SQL_BIGINT	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_DECFLOAT SQL_SMALLINT SQL_VARCHAR
SQL_C_CHAR¹	SQL_CHAR SQL_CLOB SQL_DECFLOAT SQL_DECIMAL SQL_LONGVARCHAR SQL_NUMERIC² SQL_VARCHAR	SQL_BLOB SQL_DOUBLE SQL_FLOAT SQL_GRAPHIC SQL_INTEGER SQL_LONGVARGRAPHIC SQL_REAL SQL_ROWID SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARGRAPHIC SQL_XML
SQL_C_WCHAR³	SQL_GRAPHIC⁵ SQL_LONGVARGRAPHIC⁵ SQL_VARGRAPHIC⁵	SQL_BLOB SQL_CHAR SQL_CLOB SQL_DECIMAL SQL_DECFLOAT SQL_DOUBLE SQL_FLOAT SQL_INTEGER SQL_LONGVARCHAR SQL_NUMERIC² SQL_REAL SQL_ROWID SQL_SMALLINT SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_VARCHAR SQL_XML
SQL_C_LONG	SQL_INTEGER	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_NUMERIC² SQL_REAL SQL_DECFLOAT SQL_SMALLINT SQL_VARCHAR
SQL_C_SHORT	SQL_SMALLINT	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_NUMERIC² SQL_REAL SQL_DECFLOAT SQL_VARCHAR
SQL_C_TINYINT	No SQL data types use SQL_C_TINYINT in a default conversion.	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_SMALLINT SQL_INTEGER SQL_NUMERIC² SQL_REAL SQL_DECFLOAT SQL_VARCHAR
SQL_C_FLOAT	SQL_REAL	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_SMALLINT SQL_INTEGER SQL_NUMERIC² SQL_DECFLOAT SQL_VARCHAR
SQL_C_DOUBLE	SQL_DOUBLE SQL_FLOAT	SQL_CHAR SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_NUMERIC² SQL_REAL SQL_DECFLOAT SQL_VARCHAR
SQL_C_DECIMAL64	No SQL data types use SQL_C_DECIMAL64 in a default conversion.	SQL_CHAR SQL_DECIMAL SQL_FLOAT SQL_DECFLOAT SQL_DOUBLE SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_BIGINT SQL_VARCHAR SQL_LONGVARCHAR
SQL_C_DECIMAL128	No SQL data types use SQL_C_DECIMAL128 in a default conversion.	SQL_CHAR SQL_DECIMAL SQL_FLOAT SQL_DECFLOAT SQL_DOUBLE SQL_INTEGER SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_BIGINT SQL_VARCHAR SQL_LONGVARCHAR
SQL_C_TYPE_DATE	SQL_TYPE_DATE	SQL_CHAR SQL_TYPE_TIMESTAMP SQL_VARCHAR
SQL_C_TYPE_TIME	SQL_TYPE_TIME	SQL_CHAR SQL_TYPE_TIMESTAMP SQL_VARCHAR
SQL_C_TYPE_TIMESTAMP	SQL_TYPE_TIMESTAMP	SQL_CHAR SQL_TYPE_DATE SQL_TYPE_TIME SQL_VARCHAR
SQL_C_BINARY	SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB SQL_XML	SQL_CHAR SQL_CLOB SQL_LONGVARCHAR SQL_LONGVARGRAPHIC SQL_VARCHAR
SQL_C_BINARYXML	No SQL types use SQL_C_BINARYXML in a default conversion	SQL_XML
SQL_C_BIT	No SQL types use SQL_C_BIT in a default conversion.	SQL_CHAR SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_SMALLINT SQL_INTEGER SQL_NUMERIC² SQL_REAL SQL_DECFLOAT SQL_VARCHAR
SQL_C_DBCHAR	SQL_DBCLOB SQL_GRAPHIC⁵ SQL_LONGVARGRAPHIC⁵ SQL_VARGRAPHIC⁵	SQL_XML
SQL_C_CLOB_LOCATOR	No SQL data types use SQL_C_CLOB_LOCATOR in a default conversion.	SQL_CLOB
SQL_C_BLOB_LOCATOR	No SQL data types use SQL_C_BLOB_LOCATOR in a default conversion.	SQL_BLOB
SQL_C_DBCLOB_LOCATOR	No SQL data types use SQL_C_DBCLOB_LOCATOR in a default conversion.	SQL_DBCLOB
Notes: You must bind data to the SQL_C_CHAR data type for Unicode UTF-8 data NUMERIC is a synonym for DECIMAL on DB2 for z/OS, DB2 for VSE & VM, and DB2 for Linux, UNIX, and Windows. You must bind data with the SQL_C_WCHAR data type for Unicode UCS-2 data. Data is not converted to LOB locator types; locators represent a data value. 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.

Limits on precision, and scale, as well as truncation and rounding rules are the same as those for DB2 for z/OS, with the following exception; truncation of values to the right of the decimal point for numeric values returns a truncation warning, whereas truncation to the left of the decimal point returns an error. In cases of error, the application should call SQLGetDiagRec() to obtain the SQLSTATE and additional information about the failure. When moving and converting floating point data values between the application and DB2 ODBC, no correspondence is guaranteed to be exact as the values can change in precision and scale.