Call Level Interface Guide and Reference

Converting Data from C to SQL Data Types

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().

  Note:

  Data is sent to the data source only if the SQLSTATE is 00000 (Success).

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).

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 206. Converting Character C Data to SQL Data

fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_CLOB	Length of data <= Column length	00000
	Length of data > Column length	22001
SQL_DECIMAL SQL_NUMERIC SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE	Data converted without truncation	00000
	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_DATE	Data value is a valid date	00000
	Data value is not a valid date	22007
SQL_TIME	Data value is a valid time	00000
	Data value is not a valid time	22007
SQL_TIMESTAMP	Data value is a valid timestamp	00000
	Data value is not a valid timestamp	22007
SQL_GRAPHIC SQL_VARGRAPHIC SQL_LONGVARGRAPHIC SQL_DBCLOB	Length of data / 2 <= Column length	00000
	Length of data / 2 < Column length	22001
Note: SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

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_BIT

Table 207. Converting Numeric C Data to SQL Data

fSQLType	Test	SQLSTATE
SQL_DECIMAL SQL_NUMERIC SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE	Data converted without truncation	00000
	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.	00000
	Conversion of data would result in loss of significant digits.	22003
Note: SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS. 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 208. 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
	Length of data > Column length	22001
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data <= Column length	N/A
	Length of data > Column length	22001

Converting DBCHAR C Data to SQL Data

The Double Byte C data type is:

SQL_C_DBCHAR

Table 209. 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
	Length of data > Column length x 2	22001
SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_BLOB	Length of data <= Column length x 2	N/A
	Length of data > Column length x 2	22001

Converting Date C Data to SQL Data

The date C data type is:

SQL_C_DATE

Table 210. Converting Date C Data to SQL Data

fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= 10	00000
	Column length < 10	22003
SQL_DATE	Data value is a valid date	00000
	Data value is not a valid date	22007
SQL_TIMESTAMP a	Data value is a valid date	00000
	Data value is not a valid date	22007
Note: SQLSTATE 00000 is not returned by SQLError(), 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 211. Converting Time C Data to SQL Data

fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= 8	00000
	Column length < 8	22003
SQL_TIME	Data value is a valid time	00000
	Data value is not a valid time	22007
SQL_TIMESTAMP a	Data value is a valid time	00000
	Data value is not a valid time	22007
Note: SQLSTATE 00000 is not returned by SQLError(), 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 212. Converting Timestamp C Data to SQL Data

fSQLType	Test	SQLSTATE
SQL_CHAR SQL_VARCHAR	Column length >= Display size	00000
	19 <= Column length < Display size a	22001
	Column length < 19	22003
SQL_DATE	Data value is a valid date b	22001
	Data value is not a valid date	22007
SQL_TIME	Data value is a valid time c	22001
	Data value is not a valid time	22007
SQL_TIMESTAMP	Data value is a valid timestamp	00000
	Data value is not a valid timestamp	22007
Note: a The fractional seconds of the timestamp are truncated. b The time portion of the timestamp is deleted. c The date portion of the timestamp is deleted. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

C to SQL Data Conversion Examples

Table 213. 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	00000
SQL_C_CHAR	abcdef\0	SQL_CHAR	5	abcde	22001
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	6	1234.56	00000
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	5	1234.5	22001
SQL_C_CHAR	1234.56\0	SQL_DECIMAL	3	---	22003
SQL_C_FLOAT	1234.56	SQL_FLOAT	not applicable	1234.56	00000
SQL_C_FLOAT	1234.56	SQL_INTEGER	not applicable	1234	22001
Note: SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.