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).
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 |
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_TYPE_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_TYPE_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_TYPE_TIMESTAMP | Data value is a valid timestamp a | Data | 16 b | 00000 |
Data value is not a valid timestamp a | Untouched | 16 b | 22007 | |
SQL_C_TIMESTAMP_EXT | Data value is a valid timestamp a | Data | 20 | 00000 |
Data value is not a valid timestamp a | Untouched | 20 | 22007 | |
Note:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS. |
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 SQLGetDiagRec(),
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. |
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_DBCHAR |
Display size * 2 < cbValueMax | Data | Length of data | 00000 |
Number of significant digits * 2 < cbValueMax | Truncated Data | Length of Data | 01004 | |
Number of significant digits * 2 >= cbValueMax | Untouched | Length of Data | 22003 | |
SQL_C_SHORT |
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:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS. |
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 |
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_BINARYXML | Length of data <= cbValueMax | Data | Length of data | 00000 |
Length of data > cbValueMax | Truncated data | Length of data | 01004 | |
SQL_C_DBCHAR | Number of double-byte characters * 2 < cbValueMax | Data | Length of data | 00000 |
Number of double-byte characters * 2 >= cbValueMax | Truncated data, to the nearest even byte that is less than cbValueMax | Length of data | 01004 | |
SQL_C_WCHAR | Number of double-byte characters * 2 < cbValueMax | Data | Length of data | 00000 |
Number of double-byte characters * 2 >= cbValueMax | Truncated data, to the nearest even byte that is less than cbValueMax | Length of data | 01004 | |
Note:
|
fCType | Test | rgbValue | pcbValue | SQLSTATE |
---|---|---|---|---|
SQL_C_CHAR | cbValueMax >= 11 | Data | 10 | 00000 |
cbValueMax < 11 | Untouched | 10 | 22003 | |
SQL_C_TYPE_DATE | None a | Data | 6 b | 00000 |
SQL_C_TYPE_TIMESTAMP | None a | Data c | 16 b | 00000 |
SQL_C_TIMESTAMP_EXT | None a | Data c | 20 | 00000 |
Note:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), 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.
fCType | Test | rgbValue | pcbValue | SQLSTATE |
---|---|---|---|---|
SQL_C_CHAR | cbValueMax >= 9 | Data | 8 | 00000 |
cbValueMax < 9 | Untouched | 8 | 22003 | |
SQL_C_TYPE_TIME | None a | Data | 6 b | 00000 |
SQL_C_TYPE_TIMESTAMP | None a | Data c | 16 b | 00000 |
SQL_C_TIMESTAMP_EXT | None a | Data c | 20 | 00000 |
Note:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), 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.
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_TYPE_DATE | None a | Truncated data c | 6 e | 01S07 |
SQL_C_TYPE_TIME | None a | Truncated data d | 6 e | 01S07 |
SQL_C_TYPE_TIMESTAMP | None a | Data | 16 e | 00000 |
SQL_C_TIMESTAMP_EXT | None a | Data | 20 | 00000 |
Note:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), 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.ffffffffffff" format where fractional second digits range from 0 to 12 (regardless of the precision of the timestamp SQL data type). If an application requires the ISO format, set the CLI/ODBC configuration keyword PATCH2=33.
fCType | SQL Type | Test/Result | SQLSTATE |
---|---|---|---|
SQL_C_TYPE_TIMESTAMP_ EXT_TZ | SQL_CHAR | Data value is a valid timestamp with timezone | N/A |
Data value is not a valid timestamp with timezone | 22007 | ||
SQL_C_TYPE_DATE | SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE | Truncated data | 01S07 |
SQL_C_TYPE_TIME | SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE | Truncated data | 01S07 |
SQL_C_TYPE_TIMESTAMP | SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE | Truncated data | 01S07 |
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_TYPE_DATE | 1992-12-31 | SQL_C_CHAR | 11 | 1992-12-31\0 a | 00000 |
SQL_TYPE_DATE | 1992-12-31 | SQL_C_CHAR | 10 | --- | 22003 |
SQL_TYPE_DATE | 1992-12-31 | SQL_C_TYPE_ TIMESTAMP | ignored | 1992,12,31, 0,0,0,0 b | 00000 |
SQL_TYPE_ TIMESTAMP | 1992-12-31 23:45:55.12 | SQL_C_CHAR | 23 | 1992-12-31 23:45:55.12\0 a | 00000 |
SQL_TYPE_ TIMESTAMP | 1992-12-31 23:45:55.12 | SQL_C_CHAR | 22 | 1992-12-31 23:45:55.1\0 a | 01004 |
SQL_TYPE_ TIMESTAMP | 1992-12-31 23:45:55.12 | SQL_C_CHAR | 18 | --- | 22003 |
Note:
SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS. |