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 andSQLGetData.
- 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_WLONGVARCHARThe 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 [h] 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 [b] Data converted with truncation of fractional digits [a] Conversion of data would result in loss of whole (as opposed to fractional) digits [b] Data is not a numeric-literal [b] | 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 [h] SQL_C_FLOAT SQL_C_DOUBLE | Data is within the range of the data type to which the number is being converted [a] Data is outside the range of the data type to which the number is being converted [a] Data is not a numeric-literal [b] | 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 [a] Data value is a valid timestamp-value; time portion is zero [a] Data value is a valid timestamp-value; time portion is nonzero [a], [c], Data value is not a valid date-value or timestamp_value [a] | Data Data Truncated data Undefined | 6 [b] 6 [b] 6 [b] 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 [a] Data value is a valid timestamp-value or a valid time_value; fractional seconds portion is zero [a],[d] Data value is a valid timestamp-value ; fractional seconds portion is nonzero [a], [d], [e] Data value is not a valid timestamp-value or time_value [a] | Data Data Truncated data Undefined | 6 [b] 6 [b] 6 [b] 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 [a], [d] Data value is a valid timestamp-value or a valid time_value; fractional seconds portion truncated [a] Data value is a valid date-value [a] Data value is a valid time_value [a] Data value is not a valid date_value, time_value, or timestamp_value [a] | Data Truncated data Data [f] Data [g] Undefined | 16 [b] 16 [b] 16 [b] 16 [b] Undefined | N/A 01S07 N/A N/A 22018 |
|
Note:
[a] 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. [b] This is the size of the corresponding C data type. [c] The time portion of the timestamp-value is truncated. [d] The date portion of the timestamp-value is ignored. [e] The fractional seconds portion of the timestamp is truncated. [f] The time fields of the timestamp structure are set to zero. [g] The date fields of the timestamp structure are set to the current date. [h] 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_BIGINT SQL_NUMERIC SQL_REAL SQL_TINYINT SQL_FLOAT SQL_SMALLINT SQL_DOUBLE SQL_INTEGER
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 < 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 |
| 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 [c] 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 [a] Data converted with truncation of fractional digits [a] Conversion of data would result in loss of whole (as opposed to fractional) digits [a] | Data Truncated data Undefined | Size of the C data type Size of the C data type Undefined | N/A 01S07 22003 |
| APPROXIMATE NUMERIC TYPES [c] SQL_C_FLOAT SQL_C_DOUBLE | Data is within the range of the data type to which the number is being converted [a] Data is outside the range of the data type to which the number is being converted [a] | 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 |
|
Note:
[a] 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. [b] This is the size of the corresponding C data type. [c] 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, seeConversion 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, seeConversion 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 [a] | Data | 6 [c] | N/A |
| SQL_C_TIMESTAMP | None [a] | Data [b] | 16 [c] | N/A |
|
Note:
[a] 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. [b] The time fields of the timestamp structure are set to zero. [c] 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, seeConversion 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 [a] 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 [a] 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 [a] | Data | 6 [c] | N/A |
| SQL_C_TIMESTAMP | None [a] | Data [b] | 16 [c] | N/A |
|
Note:
[a]: The fractional seconds of the time are truncated. [b]: 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. [c]: 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. [d]: 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 [b] 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 [b] 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 [a] Time portion of timestamp is non-zero [a] | Data Truncated data [c] | 6 [f] 6 [f] | N/A 01S07 |
| SQL_C_TYPE_TIME | Fractional seconds portion of timestamp is zero [a] Fractional seconds portion of timestamp is non-zero [a] | Data [d] Truncated data [d], [e] | 6 [f] 6 [f] | N/A 01S07 |
| SQL_C_TYPE_TIMESTAMP | Fractional seconds portion of timestamp is not truncated [a] Fractional seconds portion of timestamp is truncated [a] | Data [e] Truncated data [e] | 6 [f] 6 [f] | N/A 01S07 |
|
Note:
[a] 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. [b] The fractional seconds of the timestamp are truncated. [c] The time portion of the timestamp is truncated. [d] The date portion of the timestamp is ignored. [e] The fractional seconds portion of the timestamp is truncated. [f] 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.)
扫一扫
3173
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
