C to SQL data conversion in CLI

C to SQL data conversion in CLI provides a way to convert character C data, numeric C data, binary C data, DBCHAR C data, date C data, time C data, timestamp C data, variable timestamp C data and timestamp(p) with timezone C data.

For a given C data type:

the first column of the table lists the legal input values of the fSqlType argument in SQLBindParameter() or SQLSetParam().
the second column lists the outcomes of a test, often using the length of the parameter data as specified in the pcbValue argument in SQLBindParameter() or SQLSetParam(), which the driver performs to determine if it can convert the data.
the third column lists the SQLSTATE returned for each outcome by SQLExecDirect() or SQLExecute().

The tables list the conversions defined by ODBC to be valid for a given SQL data type.

If the fSqlType argument in SQLBindParameter() or SQLSetParam() contains a value not shown in the table for a given C data type, SQLSTATE 07006 is returned (Restricted data type attribute violation).

If the fSqlType argument contains a value shown in the table but which specifies a conversion not supported by the driver, SQLBindParameter() or SQLSetParam() returns SQLSTATE HYC00 (Driver not capable).

If the rgbValue and pcbValue arguments specified in SQLBindParameter() or SQLSetParam() are both null pointers, that function returns SQLSTATE HY009 (Invalid argument value).

Note:

The SQL_XML data type is not supported for use with an Informix® data server.
The SQL_BINARY and SQL_VARBINARY data types are supported for Db2® for IBM® i Version 6 Release 1 servers or later releases.

Length of data: the total length of the data after it has been converted to the specified SQL data type (excluding the null termination byte if the data was converted to a string). This is true even if data is truncated before it is sent to the data source.
Column length: the maximum number of bytes returned to the application when data is transferred to its default C data type. For character data, the length does not include the null termination byte.
Display size: the maximum number of bytes needed to display data in character form.
Significant digits: the minus sign (if needed) and the digits to the left of the decimal point.

Converting character C data to SQL data

The character C data type is:

SQL_C_CHAR

Table 1. Converting character C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data <= Column length	N/A
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data > Column length	22001
SQL_DECIMAL SQL_NUMERIC SQL_SMALLINT SQL_INTEGER SQL_BIGINT SQL_REAL SQL_FLOAT SQL_DOUBLE	Data converted without truncation	N/A
	Data converted with truncation, but without loss of significant digits	22001
	Conversion of data would result in loss of significant digits	22003
	Data value is not a numeric value	22005
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	(Length of data) < Column length	N/A
	(Length of data) >= Column length	22001
	Data value is not a hexadecimal value	22005
SQL_TYPE_DATE	Data value is a valid date	N/A
	Data value is not a valid date	22007
	Data value is a valid timestamp	22008
	Data value is a valid timestamp and the connection attribute SQL_ATTR_REPORT_TIMESTAMP_TRUNC_AS_WARN is set to 1	01S07 (Fractional truncation warning)
SQL_TYPE_TIME	Data value is a valid time	N/A
	Data value is not a valid time	22007
	Data value is a valid timestamp	22008
	Data value is a valid timestamp and the connection attribute SQL_ATTR_REPORT_TIMESTAMP_TRUNC_AS_WARN is set to 1	01S07 (Fractional truncation warning)
SQL_TYPE_TIMESTAMP	Data value is a valid timestamp	N/A
	Data value is not a valid timestamp	22007
	Data value is a valid date	N/A
SQL_GRAPHIC SQL_VARGRAPHIC SQL_LONGVARGRAPHIC SQL_DBCLOB	Length of data / 2 <= Column length	N/A
SQL_GRAPHIC SQL_VARGRAPHIC SQL_LONGVARGRAPHIC SQL_DBCLOB	Length of data / 2 < Column length	22001
SQL_XML	Data can be implicitly parsed	(several SQLSTATES can be returned)

Converting numeric C data to SQL data

The numeric C data types are:

SQL_C_SHORT
SQL_C_LONG
SQL_C_FLOAT
SQL_C_DOUBLE
SQL_C_TINYINT
SQL_C_SBIGINT
SQL_C_BIT

Table 2. Converting numeric C data to SQL data
fSQLType	Test	SQLSTATE
SQL_DECIMAL SQL_NUMERIC SQL_SMALLINT SQL_INTEGER SQL_BIGINT SQL_REAL SQL_FLOAT SQL_DOUBLE	Data converted without truncation	N/A
	Data converted with truncation, but without loss of significant digits	22001
	Conversion of data would result in loss of significant digits	22003
SQL_CHAR SQL_VARCHAR	Data converted without truncation.	N/A
SQL_CHAR SQL_VARCHAR	Conversion of data would result in loss of significant digits.	22003
Note: When converting to floating point values, SQLSTATE 22003 will not be returned if non-significant digits of the resulting value are lost.

Converting binary C data to SQL data

The binary C data type is:

SQL_C_BINARY

Table 3. Converting binary C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data <= Column length	N/A
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data > Column length	22001
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data <= Column length	N/A
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data > Column length	22001
SQL_XML	Data can be implicitly parsed	(several SQLSTATES can be returned)

Converting DBCHAR C data to SQL data

The double byte C data type is:

SQL_C_DBCHAR

Table 4. Converting DBCHAR C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data <= Column length x 2	N/A
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data > Column length x 2	22001
SQL_DECIMAL SQL_NUMERIC SQL_SMALLINT SQL_INTEGER SQL_BIGINT SQL_REAL SQL_FLOAT SQL_DECFLOAT SQL_DOUBLE	Length of data <= Column length x 2	N/A
	Length of data > Column length x 2	22001
	Data is non-Unicode	07006
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data <= Column length x 2	N/A
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data > Column length x 2	22001
SQL_XML	Data can be implicitly parsed	(several SQLSTATES can be returned)

Converting date C data to SQL data

The date C data type is:

SQL_C_DATE

Table 5. Converting date C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= 10	N/A
SQL_CHAR SQL_VARCHAR	Column length < 10	22001
SQL_TYPE_DATE	Data value is a valid date	N/A
SQL_TYPE_DATE	Data value is not a valid date	22007
SQL_TYPE_TIMESTAMP^a	Data value is a valid date	N/A
SQL_TYPE_TIMESTAMP^a	Data value is not a valid date	22007
Note: SQLSTATE 00000 is not returned by `SQLGetDiagRec()`, rather it is indicated when the function returns SQL_SUCCESS. Note: a, the time component of TIMESTAMP is set to zero.

Converting time C data to SQL data

The time C data type is:

SQL_C_TIME

Table 6. Converting time C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= 8	N/A
SQL_CHAR SQL_VARCHAR	Column length < 8	22001
SQL_TYPE_TIME	Data value is a valid time	N/A
SQL_TYPE_TIME	Data value is not a valid time	22007
SQL_TYPE_TIMESTAMP ^a	Data value is a valid time	N/A
SQL_TYPE_TIMESTAMP ^a	Data value is not a valid time	22007
Note: SQLSTATE 00000 is not returned by `SQLGetDiagRec()`, rather it is indicated when the function returns SQL_SUCCESS. Note: a The date component of TIMESTAMP is set to the system date of the machine at which the application is running.

Converting timestamp C data to SQL data

The timestamp C data type is:

SQL_C_TIMESTAMP

Table 7. Converting timestamp C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= Display size	N/A
	26 <= Column length < Display size ^a	N/A
	Column length < 26	22001
SQL_TYPE_DATE	Time fields are zero	N/A
	Time fields are non-zero	22008
	Data value does not contain a valid date ^b	22007
	Time fields are non-zero and the connection attribute SQL_ATTR_REPORT_TIMESTAMP_TRUNC_AS_WARN is set to 1	01S07 (Fractional truncation warning)
SQL_TYPE_TIME	Fractional seconds fields are zero	N/A
	Fractional seconds fields are non-zero	22008
	Data value does not contain a valid time	22007
	Time fields are non-zero and the connection attribute SQL_ATTR_REPORT_TIMESTAMP_TRUNC_AS_WARN is set to 1	01S07 (Fractional truncation warning)
SQL_TYPE_TIMESTAMP	Data value is a valid timestamp	N/A
SQL_TYPE_TIMESTAMP	Data value is not a valid timestamp	22007
Note: ^a The fractional seconds of the timestamp are truncated. ^b The timestamp_struct must reset the hour, minute, second, and fraction to 0, otherwise SQLSTATE 22008 will be returned. SQLSTATE 00000 is not returned by `SQLGetDiagRec()`, rather it is indicated when the function returns SQL_SUCCESS.

Converting variable timestamp C data to SQL data

The timestamp C data type is:

SQL_C_TIMESTAMP_EXT

Table 8. Converting variable timestamp C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= Display size	N/A
	26 <= Column length < Display size ^a	N/A
	Column length < 26	22001
	Fractional seconds fields > 12	22007
SQL_TYPE_DATE	Time fields are zero	N/A
	Time fields are non-zero	22008
	Data value does not contain a valid date ^b	22007
SQL_TYPE_TIME	Fractional seconds fields are zero	N/A
	Fractional seconds fields are non-zero	22008
	Data value does not contain a valid time	22007
SQL_TYPE_TIMESTAMP	Data value is a valid timestamp	N/A
	Data value is not a valid timestamp	22007
	Precision specified by TIMESTAMP(p) <= fractional seconds fields <= 12 ^a	N/A
Note: ^a The fractional seconds of the timestamp are truncated. ^b The timestamp_struct must reset the hour, minute, second, and fraction to 0, otherwise SQLSTATE 22008 will be returned. SQLSTATE 00000 is not returned by `SQLGetDiagRec()`, rather it is indicated when the function returns SQL_SUCCESS.

Converting timestamp(p) with timezone C data to SQL data

The timestamp with timezone C data type is:

SQL_C_TIMESTAMP_EXT_TZ

Table 9. Converting timestamp with timezone C data to SQL data
fSQLType	Test	SQLSTATE
SQL_CHAR /SQL_VARCHAR SQL_WCHAR/SQL_WVARCHAR SQL_LONGVARCHAR	Column length >= Display size	N/A
	Column length < 27+p	22001
SQL_TYPE_DATE	Time fields are zero	N/A
	Time fields are non-zero	22008
	Data value does not contain a valid date	22007
	Time zone fields are non-zero	22008
SQL_TYPE_TIME	Fractional seconds fields are zero	N/A
	Fractional seconds fields are non-zero	22008
	Data value does not contain a valid time	22007
	Time zone fields are non-zero	22008
SQL_TYPE_TIMESTAMP	Data value is a valid timestamp	N/A
	Data value is not a valid timestamp	22007
	Time zone fields are non-zero	22008
SQL_TYPE_TIMESTAMP_WITH_TIMEZONE	Data value is a valid timestamp	N/A
SQL_TYPE_TIMESTAMP_WITH_TIMEZONE	Data value is a valid timestamp with time zone	22007

C to SQL data conversion examples

Table 10. C to SQL data conversion examples
C data type	C data value	SQL data type	Column length	SQL data value	SQL STATE
SQL_C_CHAR	abcdef\0	SQL_CHAR	6	abcdef	N/A
SQL_C_CHAR	abcdef\0	SQL_CHAR	5	abcde	22001
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	6	1234.56	N/A
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	5	1234.5	22001
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	3	---	22003
SQL_C_CHAR	4.46.32	SQL_TYPE_TIME	6	4.46.32	N/A
SQL_C_CHAR	4-46-32	SQL_TYPE_TIME	6	not applicable	22007
SQL_C_DOUBLE	123.45	SQL_CHAR	22	1.23450000 000000e+02	N/A
SQL_C_FLOAT	1234.56	SQL_FLOAT	not applicable	1234.56	N/A
SQL_C_FLOAT	1234.56	SQL_INTEGER	not applicable	1234	22001
SQL_C_ TIMESTAMP	1992-12-31 23:45:55. 123456	SQL_TYPE_DATE	6	1992-12-31	01004
SQL_C_ TIMESTAMP_EXT	2009-06-06 23:45:55. 123456789876	SQL_TYPE_DATE	6	2009-06-06	01004
Note: SQLSTATE 00000 is not returned by `SQLGetDiagRec()`, rather it is indicated when the function returns SQL_SUCCESS.