Call Level Interface Guide and Reference

Converting Data from SQL to C Data Types

For a given SQL data type:

• the first column of the table lists the legal input values of the fCType argument in SQLBindCol() and SQLGetData().
• the second column lists the outcomes of a test, often using the cbValueMax argument specified in SQLBindCol() or SQLGetData(), which the driver performs to determine if it can convert the data.
• the third and fourth columns list the values (for each outcome) of the rgbValue and pcbValue arguments specified in the SQLBindCol() or SQLGetData() after the driver has attempted to convert the data.
• the last column lists the SQLSTATE returned for each outcome by SQLFetch(), SQLExtendedFetch(), SQLGetData() or SQLGetSubString().

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

If the fCType argument in SQLBindCol() or SQLGetData() contains a value not shown in the table for a given SQL data type, SQLFetch(), or SQLGetData() returns the SQLSTATE 07006 (Restricted data type attribute violation).

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

Though it is not shown in the tables, the pcbValue argument contains SQL_NULL_DATA when the SQL data value is NULL. For an explanation of the use of pcbValue when multiple calls are made to retrieve data, see SQLGetData().

When SQL data is converted to character C data, the character count returned in pcbValue does not include the null termination byte. If rgbValue is a null pointer, SQLBindCol() or SQLGetData() returns SQLSTATE HY009 (Invalid argument value).

In the following tables:

Length of data

the total length of the data after it has been converted to the specified C 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 returned to the application.

Significant digits

the minus sign (if needed) and the digits to the left of the decimal point.

Display size

the total number of bytes needed to display data in the character format.

Converting Character SQL Data to C Data

The character SQL data types are:

SQL_CHAR

SQL_VARCHAR

SQL_LONGVARCHAR

SQL_CLOB

Table 198. Converting Character SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	Length of data < cbValueMax	Data	Length of data	00000
	Length of data >= cbValueMax	Truncated data	Length of data	01004
SQL_C_BINARY	Length of data <= cbValueMax	Data	Length of data	00000
	Length of data > cbValueMax	Truncated data	Length of data	01004
SQL_C_SHORT SQL_C_LONG SQL_C_FLOAT SQL_C_FLOAT SQL_C_TINYINT SQL_C_BIT SQL_C_UBIGINT SQL_C_SBIGINT SQL_C_NUMERIC c	Data converted without truncation a	Data	Size of the C data type	00000
	Data converted with truncation, but without loss of significant digits a	Data	Size of the C data type	01004
	Conversion of data would result in loss of significant digitsa	Untouched	Size of the C data type	22003
	Data is not a number a	Untouched	Size of the C data type	22005
SQL_C_DATE	Data value is a valid date a	Data	6 b	00000
	Data value is not a valid date a	Untouched	6 b	22007
SQL_C_TIME	Data value is a valid time a	Data	6 b	00000
	Data value is not a valid time a	Untouched	6 b	22007
SQL_C_TIMESTAMP	Data value is a valid timestamp a	Data	16 b	00000
	Data value is not a valid timestamp a	Untouched	16 b	22007
Note: a The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type. b This is the size of the corresponding C data type. c SQL_C_NUMERIC is only supported on 32-bit Windows platforms. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

Converting Graphic SQL Data to C Data

The graphic SQL data types are:

SQL_GRAPHIC

SQL_VARGRAPHIC

SQL_LONGVARGRAPHIC

SQL_DBCLOB

Table 199. Converting Graphic SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	Number of double byte characters * 2 <= cbValueMax	Data	Length of data(octects)	00000
	Number of double byte characters * 2 > cbValueMax	Truncated data, to the nearest even byte that is less than cbValueMax.	Length of data(octects)	01004
SQL_C_DBCHAR	Number of double byte characters * 2 < cbValueMax	Data	Length of data(octects)	00000
	Number of double byte characters * 2 >= cbValueMax	Truncated data, to the nearest even byte that is less than cbValueMax.	Length of data(octects)	01004
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 Numeric SQL Data to C Data

The numeric SQL data types are:

SQL_DECIMAL

SQL_NUMERIC

SQL_SMALLINT

SQL_INTEGER

SQL_BIGINT

SQL_REAL

SQL_FLOAT

SQL_DOUBLE

Table 200. Converting Numeric SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	Display size < cbValueMax	Data	Length of data	00000
	Number of significant digits < cbValueMax	Truncated data	Length of data	01004
	Number of significant digits >= cbValueMax	Untouched	Length of data	22003
SQL_C_SHORT SQL_C_LONG SQL_C_FLOAT SQL_C_DOUBLE SQL_C_TINYINT SQL_C_BIT SQL_C_UBIGINT SQL_C_SBIGINT SQL_C_NUMERIC b	Data converted without truncation a	Data	Size of the C data type	00000
	Data converted with truncation, but without loss of significant digits a	Truncated data	Size of the C data type	01004
	Conversion of data would result in loss of significant digits a	Untouched	Size of the C data type	22003
Note: a The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type. b SQL_C_NUMERIC is only supported on 32-bit Windows platforms. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

Converting Binary SQL Data to C Data

The binary SQL data types are:

SQL_BINARY

SQL_VARBINARY

SQL_LONGVARBINARY

SQL_BLOB

Table 201. Converting Binary SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	(Length of data) < cbValueMax	Data	Length of data	N/A
	(Length of data) >= cbValueMax	Truncated data	Length of data	01004
SQL_C_BINARY	Length of data <= cbValueMax	Data	Length of data	N/A
	Length of data > cbValueMax	Truncated data	Length of data	01004

Converting Date SQL Data to C Data

The date SQL data type is:

SQL_DATE

Table 202. Converting Date SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	cbValueMax >= 11	Data	10	00000
	cbValueMax < 11	Untouched	10	22003
SQL_C_DATE	None a	Data	6 b	00000
SQL_C_TIMESTAMP	None a	Data c	16 b	00000
Note: a The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type. b This is the size of the corresponding C data type. c The time fields of the TIMESTAMP_STRUCT structure are set to zero. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

When the date SQL data type is converted to the character C data type, the resulting string is in the "yyyy-mm-dd" format.

Converting Time SQL Data to C Data

The time SQL data type is:

SQL_TIME

Table 203. Converting Time SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	cbValueMax >= 9	Data	8	00000
	cbValueMax < 9	Untouched	8	22003
SQL_C_TIME	None a	Data	6 b	00000
SQL_C_TIMESTAMP	None a	Data c	16 b	00000
Note: a The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type. b This is the size of the corresponding C data type. c The date fields of the TIMESTAMP_STRUCT structure are set to the current system date of the machine that the application is running, and the time fraction is set to zero. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

When the time SQL data type is converted to the character C data type, the resulting string is in the "hh:mm:ss" format.

Converting Timestamp SQL Data to C Data

The timestamp SQL data type is:

SQL_TIMESTAMP

Table 204. Converting Timestamp SQL Data to C Data

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_CHAR	Display size < cbValueMax	Data	Length of data	00000
	19 <= cbValueMax <= Display size	Truncated Data b	Length of data	01004
	cbValueMax < 19	Untouched	Length of data	22003
SQL_C_DATE	None a	Truncated data c	6 e	01004
SQL_C_TIME	None a	Truncated data d	6 e	01004
SQL_C_TIMESTAMP	None a	Data	16 e	00000
Note: a The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type. b The fractional seconds of the timestamp are truncated. c The time portion of the timestamp is deleted. d The date portion of the timestamp is deleted. e This is the size of the corresponding C data type. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.

When the timestamp SQL data type is converted to the character C data type, the resulting string is in the "yyyy-mm-dd hh:mm:ss[.fff[fff]]" format (regardless of the precision of the timestamp SQL data type).

SQL to C Data Conversion Examples

Table 205. SQL to C Data Conversion Examples

SQL Data Type	SQL Data Value	C Data Type	cbValue Max	rgbValue	SQL STATE
SQL_CHAR	abcdef	SQL_C_CHAR	7	abcdef\0 a	00000
SQL_CHAR	abcdef	SQL_C_CHAR	6	abcde\0 a	01004
SQL_DECIMAL	1234.56	SQL_C_CHAR	8	1234.56\0 a	00000
SQL_DECIMAL	1234.56	SQL_C_CHAR	5	1234\0 a	01004
SQL_DECIMAL	1234.56	SQL_C_CHAR	4	---	22003
SQL_DECIMAL	1234.56	SQL_C_FLOAT	ignored	1234.56	00000
SQL_DECIMAL	1234.56	SQL_C_SHORT	ignored	1234	01004
SQL_DATE	1992-12-31	SQL_C_CHAR	11	1992-12-31\0 a	00000
SQL_DATE	1992-12-31	SQL_C_CHAR	10	---	22003
SQL_DATE	1992-12-31	SQL_C_ TIMESTAMP	ignored	1992,12,31, 0,0,0,0 b	00000
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	23	1992-12-31 23:45:55.12\0 a	00000
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	22	1992-12-31 23:45:55.1\0 a	01004
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	18	---	22003
Note: a "\0" represents a null termination character. b The numbers in this list are the numbers stored in the fields of the TIMESTAMP_STRUCT structure. SQLSTATE 00000 is not returned by SQLError(), rather it is indicated when the function returns SQL_SUCCESS.