Data conversion tables from SQL to C

Programmer Guide : Data types : Data conversion tables from SQL to C

The tables in the following sections describe how the driver or data source converts data retrieved from the data source; drivers are required to support conversions to all ODBC C data types from the ODBC SQL data types that they support.

Conversion table description (SQL to C)

The following columns are included in the tables:

▪For a given ODBC SQL data type, the first column of the table lists the legal input values of the TargetType argument in SQLBindCol and SQLGetData.

▪The second column lists the outcomes of a test, often using the BufferLength argument specified in SQLBindCol or SQLGetData, which the driver performs to determine if it can convert the data.

▪For each outcome, the third and fourth columns list the values placed in the buffers specified by the TargetValuePtr and StrLen_or_IndPtr arguments specified in SQLBindCol or SQLGetData after the driver has attempted to convert the data. (The StrLen_or_IndPtr argument corresponds to the SQL_DESC_OCTET_LENGTH_PTR field of the ARD.)

▪The last column lists the SQLSTATE returned for each outcome by SQLFetch, SQLFetchScroll, or SQLGetData.

If the TargetType argument in SQLBindCol or SQLGetData contains a value for an ODBC C data type not shown in the table for a given ODBC SQL data type, SQLFetch, SQLFetchScroll, or SQLGetData returns SQLSTATE 07006 (Restricted data type attribute violation). If the TargetType argument contains a value that specifies a conversion from a driver-specific SQL data type to an ODBC C data type and this conversion is not supported by the driver, SQLFetch, SQLFetchScroll, or SQLGetData returns SQLSTATE HYC00 (Optional feature not implemented).

Although it is not shown in the tables, the driver returns SQL_NULL_DATA in the buffer specified by the StrLen_or_IndPtr argument when the SQL data value is NULL. The length specified by StrLen_or_IndPtr does not include the null-termination byte. If TargetValuePtr is a null pointer, SQLGetData returns SQLSTATE HY009 (Invalid use of null pointer); in SQLBindCol, this unbinds the columns.

The following terms and conventions are used in the tables:

▪Byte length of data is the number of bytes of C data available to return in * TargetValuePtr, whether or not the data will be truncated before it is returned to the application. For string data, this does not include the space for the null-termination character.

▪Character byte length is the total number of bytes needed to display the data in character format.

▪Words in italics represent function arguments or elements of the SQL grammar. See Minimum SQL grammar requirements for ODBC for the syntax of grammar elements.

SQL to C: Character

The character ODBC SQL data types are:

SQL_CHAR

SQL_VARCHAR

SQL_LONGVARCHAR

SQL_WCHAR

SQL_WVARCHAR

SQL_WLONGVARCHAR

The following table shows the ODBC C data types to which character SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	Byte length of data < BufferLength Byte length of data >= BufferLength	Data Truncated data	Length of data in bytes Length of data in bytes	N/A 01004
SQL_C_WCHAR	Character length of data < BufferLength (Character length of data) >= BufferLength	Data Truncated data	Length of data in characters Length of data in characters	N/A 01004
EXACT NUMERIC TYPES [7] SQL_C_STINYINT SQL_C_UTINYINT SQL_C_TINYINT SQL_C_SSHORT SQL_C_USHORT SQL_C_SHORT SQL_C_SLONG SQL_C_ULONG SQL_C_LONG SQL_C_SBIGINT SQL_C_UBIGINT SQL_C_NUMERIC	Data converted without truncation [2] Data converted with truncation of fractional digits [1] Conversion of data would result in loss of whole (as opposed to fractional) digits [2] Data is not a numeric-literal [2]	Data Truncated data Undefined Undefined	Number of bytes of the C data type Number of bytes of the C data type Undefined Undefined	N/A 01S07 22003 22018
APPROXIMATE NUMERIC TYPES [7] SQL_C_FLOAT SQL_C_DOUBLE	Data is within the range of the data type to which the number is being converted [1] Data is outside the range of the data type to which the number is being converted [1] Data is not a numeric-literal [2]	Data Undefined Undefined	Size of the C data type Undefined Undefined	N/A 2003 22018
SQL_C_BINARY	Byte length of data <= BufferLength Byte length of data > BufferLength	Data Truncated data	Length of data Length of data	N/A 01004
SQL_C_TYPE_DATE	Data value is a valid date-value [1] Data value is a valid timestamp-value; time portion is zero [1] Data value is a valid timestamp-value; time portion is nonzero [1] [3] Data value is not a valid date-value or timestamp_value [1]	Data Data Truncated data Undefined	6 [2] 6 [2] 6 [2] Undefined	N/A N/A 01S07 22018
SQL_C_TYPE_TIME	Data value is a valid time-value and the fractional seconds value is 0 [1] Data value is a valid timestamp-value or a valid time_value; fractional seconds portion is zero [1] [4] Data value is a valid timestamp-value; fractional seconds portion is nonzero [1] [4] [5] Data value is not a valid timestamp-value or time_value [1]	Data Data Truncated data Undefined	6 [2] 6 [2] 6 [2] Undefined	N/A N/A 01S07 22018
SQL_C_TYPE_TIMESTAMP	Data value is a valid timestamp-value or a valid time_value; fractional seconds portion not truncated [1] [4] Data value is a valid timestamp-value or a valid time_value; fractional seconds portion truncated [1] Data value is a valid date-value [1] Data value is a valid time_value [1] Data value is not a valid date_value, time_value, or timestamp_value [1]	Data Truncated data Data [6] Data [7] Undefined	16 [2] 16 [2] 16 [2] 16 [2] Undefined	N/A 01S07 N/A N/A 22018

[1] The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.

[2] This is the size of the corresponding C data type.

[3] The time portion of the timestamp-value is truncated.

[4] The date portion of the timestamp-value is ignored.

[5] The fractional seconds portion of the timestamp is truncated.

[6] The time fields of the timestamp structure are set to zero.

[7] The date fields of the timestamp structure are set to the current date.

[8] The exact numeric types include NUMERIC/DECIMAL as well as integer. These data types store the exact value that you specify, as long as it is within the precision of the data type. The approximate data types include FLOAT/REAL, which store only approximately the value that you specify (in some cases, the least significant digit may be slightly different from what you specified).

When character SQL data is converted to numeric, date, time, or timestamp C data, leading and trailing spaces are ignored.

SQL to C: Numeric

SQL_DECIMAL

SQL_NUMERIC

SQL_TINYINT

SQL_SMALLINT

SQL_INTEGER

SQL_BIGINT

SQL_REAL

SQL_FLOAT

SQL_DOUBLE

The following table shows the ODBC C data types to which numeric SQL data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	Character byte length <	Data	Length of data in	N/A
	BufferLength	Truncated data	bytes	01004
	Number of whole (as opposed to fractional) digits <	Undefined	Length of data in bytes	22003
	BufferLength		Undefined
	Number of whole (as opposed to fractional) digits ?
	BufferLength
SQL_C_WCHAR	Character length < BufferLength Number of whole (as opposed to fractional) digits < BufferLength Number of whole (as opposed to fractional) digits ? BufferLength	Data Truncated data Undefined	Length of data in bytes Length of data in bytes Undefined	N/A 01004 22003
EXACT NUMERIC TYPES [3] SQL_C_STINYINT SQL_C_UTINYINT SQL_C_TINYINT SQL_C_SBIGINT SQL_C_UBIGINT SQL_C_SSHORT SQL_C_USHORT SQL_C_SHORT a SQL_C_SLONG SQL_C_ULONG SQL_C_LONG SQL_C_NUMERIC	Data converted without truncation [1] Data converted with truncation of fractional digits [1] Conversion of data would result in loss of whole (as opposed to fractional) digits [1]	Data Truncated data Undefined	Size of the C data type Size of the C data type Undefined	N/A 01S07 22003
APPROXIMATE NUMERIC TYPES [3] SQL_C_FLOAT SQL_C_DOUBLE	Data is within the range of the data type to which the number is being converted [1] Data is outside the range of the data type to which the number is being converted [1]	Data Undefined	Size of the C data type Undefined	N/A 22003
SQL_C_BINARY	Length of data ? BufferLength Length of data > BufferLength	Data Undefined	Length of data Undefined	N/A 22003

[1] The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.

[2] This is the size of the corresponding C data type.

[3] The exact numeric types include NUMERIC/DECIMAL as well as integer. These data types store the exact value that you specify, as long as it is within the precision of the data type. The approximate data types include FLOAT/REAL, which store only approximately the value that you specify (in some cases, the least significant digit may be slightly different from what you specified).

SQL to C: Binary

The binary ODBC SQL data types are:

SQL_BINARY

SQL_VARBINARY

SQL_LONGVARBINARY

The following table shows the ODBC C data types to which binary SQL data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	(Byte length of data) * 2 < BufferLength (Byte length of data) * 2 >= BufferLength	Data Truncated data	Length of data in bytes Length of data in bytes	N/A 01004
SQL_C_WCHAR	(Character length of data) * 2 < BufferLength (Character length of data) * 2 >= BufferLength	Data Truncated data	Length of data in bytes Length of data in bytes	N/A 01004
SQL_C_BINARY	Byte length of data <= BufferLength Byte Length of data > BufferLength	Data Truncated data	Length of data in bytes Length of data in bytes	N/A 01004

When binary SQL data is converted to character C data, each byte (8 bits) of source data is represented as two ASCII characters. These characters are the ASCII character representation of the number in its hexadecimal form. For example, a binary 00000001 is converted to "01"and a binary 11111111 is converted to "FF".

T he driver always converts individual bytes to pairs of hexadecimal digits and terminates the character string with a null byte. Because of this, if BufferLength is even and is less than the length of the converted data, the last byte of the

*TargetValuePtr buffer is not used. (The converted data requires an even number of bytes, the next-to-last byte is a null byte, and the last byte cannot be used.)

Application developers are discouraged from binding binary SQL data to a character C data type. This conversion is usually inefficient and slow.

SQL to C: Date

The date ODBC SQL data type is:

SQL_DATE

The following table shows the ODBC C data types to which date SQL data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	BufferLength > Character byte length 11<= BufferLength <= Character byte length BufferLength < 11	Data Truncated data Undefined	10 Length of data in bytes Undefined	N/A 01004 22003
SQL_C_WCHAR	BufferLength > Character length 11<= BufferLength <= Character length BufferLength < 11	Data Truncated data Undefined	10 Length of data in bytes Undefined	N/A 01004 22003
SQL_C_BINARY	Byte length of data <= BufferLength Byte length of data > BufferLength	Data Undefined	Length of data in bytes Undefined	N/A 22003
SQL_C_DATE	None [1]	Data	6 [3]	N/A
SQL_C_TIMESTAMP	None [1]	Data [2]	16 [3]	N/A

[1] The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.

[2] The time fields of the timestamp structure are set to zero.

[3] This is the size of the corresponding C data type.

When date SQL data is converted to character C data, the resulting string is in the "yyyy-mm-dd"format. This format is not affected by the Windows country setting.

SQL to C: Time

The time ODBC SQL data type is:

SQL_TIME

The following table shows the ODBC C data types to which time SQL data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	BufferLength > Character byte length 9 <= BufferLength <= Character byte length BufferLength < 9	Data Truncated data [1] Undefined	Length of data in bytes Length of data in bytes Undefined	N/A 01004 22003
SQL_C_WCHAR	BufferLength > Character byte length 9 <= BufferLength <= Character byte length BufferLength < 9	Data Truncated data [1] Undefined	Length of data in characters Length of data in characters Undefined	N/A 01004 22003
SQL_C_BINARY	Byte length of data <= BufferLength Byte length of data > BufferLength	Data Undefined	Length of data in bytes Undefined	N/A 22003
SQL_C_DATE	None [1]	Data	6 [3]	N/A
SQL_C_TIMESTAMP	None [1]	Data [2]	16 [3]	N/A

[1] The fractional seconds of the time are truncated.

[2] The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.

[3] The date fields of the timestamp structure are set to the current date and the fractional seconds field of the timestamp structure is set to zero.

[4] This is the size of the corresponding C data type.

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

SQL to C: Timestamp

The timestamp ODBC SQL data type is:

SQL_TIMESTAMP

The following table shows the ODBC C data types to which timestamp SQL data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).

C type identifier	Test	*TargetValuePtr	*StrLen_or_IndPtr	SQLSTATE
SQL_C_CHAR	BufferLength > Character byte length 20 <= BufferLength <= Character byte length BufferLength < 20	Data Truncated data [2] Undefined	Length of data in bytes Length of data in bytes Undefined	N/A 01004 22003
SQL_C_WCHAR	BufferLength > Character byte length 20 <= BufferLength <= Character byte length BufferLength < 20	Data Truncated data [2] Undefined	Length of data in characters Length of data in characters Undefined	N/A 01004 22003
SQL_C_BINARY	Byte length of data <= BufferLength Byte length of data > BufferLength	Data Undefined	Length of data in bytes Undefined	N/A 22003
SQL_C_TYPE_DATE	Time portion of timestamp is zero [1] Time portion of timestamp is non-zero [1]	Data Truncated data [3]	6 [6] 6 [6]	N/A 01S07
SQL_C_TYPE_TIME	Fractional seconds portion of timestamp is zero [1] Fractional seconds portion of timestamp is non-zero [1]	Data [4] Truncated data [4] [5]	6 [6] 6 [6]	N/A 01S07
SQL_C_TYPE_TIMESTAMP	Fractional seconds portion of timestamp is not truncated [1] Fractional seconds portion of timestamp is truncated [1]	Data [5] Truncated data [5]	6 [6] 6 [6]	N/A 01S07

[1] The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.

[2] The fractional seconds of the timestamp are truncated.

[3] The time portion of the timestamp is truncated.

[4] The date portion of the timestamp is ignored.

[5] The fractional seconds portion of the timestamp is truncated.

[6] This is the size of the corresponding C data type.

When timestamp SQL data is converted to character C data, the resulting string is in the "yyyy-mm-dd hh:mm:ss [ .f ...]"format, where up to nine digits may be used for fractional seconds. The format is not affected by the Windows country setting. (Except for the decimal point and fractional seconds, the entire format must be used, regardless of the precision of the timestamp SQL data type.)