APPENDIX D DATA TYPES
Data stored on a data source has an SQL data type, which may be specific to that data source. A driver maps data source–specific SQL data types to ODBC SQL data types and driver-specific SQL data types. (A driver returns these mappings through SQLGetTypeInfo. It also returns the SQL data types when describing the data types of columns and parameters in SQLColAttributes, SQLColumns, SQLDescribeCol, SQLDescribeParam, SQLProcedureColumns, and SQLSpecialColumns.)
Each SQL data type corresponds to an ODBC C data type. By default, the driver assumes that the C data type of a storage location corresponds to the SQL data type of the column or parameter to which the location is bound. If the C data type of a storage location is not the default C data type, the application can specify the correct C data type with the fCType argument in SQLBindCol, SQLGetData, or SQLBindParameter. Before returning data from the data source, the driver converts it to the specified C data type. Before sending data to the data source, the driver converts it from the specified C data type.
This appendix discusses the following:
- ODBC SQL data types
- ODBC C data types
- Default ODBC C data types
- Transferring data in its binary form
- Precision, scale, length, and display size of SQL data types
- Converting data from SQL to C data types
- Converting data from C to SQL data types
For information about driver-specific SQL data types, see the driver’s documentation.
SQL Data Types
The ODBC SQL grammar defines three sets of SQL data types, each of which is a superset of the previous set.
- Minimum SQL data types provide a basic level of ODBC conformance.
- Core SQL data types are the data types in the X/Open and SQL Access Group SQL CAE specification (1992) and are supported by most SQL data sources.
- Extended SQL data types are additional data types supported by some SQL data sources.
A given driver and data source do not necessarily support all of the SQL data types defined in the ODBC grammar. Furthermore, they may support additional, driver-specific SQL data types. To determine which data types a driver supports, an application calls SQLGetTypeInfo. For information about driver-specific SQL data types, see the driver’s documentation.
Minimum SQL Data Types
The following table lists valid values of fSqlType for the minimum SQL data types. These values are defined in SQL.H. The table also lists the name and description of the corresponding data type from the X/Open and SQL Access Group SQL CAE specification (1992).
Note The minimum SQL grammar requires that a data source support at least one character SQL data type. This table is only a guideline and shows commonly used names and limits of these data types. For a given data source, the characteristics of these data types may differ from those listed below. For information about the data types in a specific data source, see the documentation for that data source.
To determine which data types are supported by a data source and the characteristics of those data types, an application calls SQLGetTypeInfo.
fSqlType
|
SQL Data Type
|
Description |
SQL_CHAR
|
CHAR(n)
|
Character string of fixed string length n (1 £ n £ 254). |
SQL_VARCHAR
|
VARCHAR(n)
|
Variable-length character string with a maximum string length n (1 £ n £ 254). |
SQL_LONGVARCHAR
|
LONG VARCHAR
|
Variable length character data. Maximum length is data source–dependent. |
Core SQL Data Types
The following table lists valid values of fSqlType for the core SQL data types. These values are defined in SQL.H. The table also lists the name and description of the corresponding data type from the X/Open and SQL Access Group SQL CAE specification (1992). In the table, precision refers to the total number of digits and scale refers to the number of digits to the right of the decimal point.
Note This table is only a guideline and shows commonly used names, ranges, and limits of core SQL data types. A given data source may support only some of the listed data types and the characteristics of the supported data types may differ from those listed below. For example, some data sources support unsigned numeric data types. For information about the data types in a specific data source, see the documentation for that data source. To determine which data types are supported by a data source and the characteristics of those data types, an application calls SQLGetTypeInfo.
fSqlType
|
SQL Data Type
|
Description |
SQL_DECIMAL
|
DECIMAL(p,s)
|
Signed, exact, numeric value with a precision p and scale s (1 £ p £ 15; 0 £ s £ p). |
SQL_NUMERIC
|
NUMERIC(p,s)
|
Signed, exact, numeric value with a precision p and scale s (1 £ p £ 15; 0 £ s £ p). |
SQL_SMALLINT
|
SMALLINT
|
Exact numeric value with precision 5 and scale 0 (signed: –32,768 £ n £ 32,767, unsigned: 0£ n £ 65,535) a. |
SQL_INTEGER
|
INTEGER
|
Exact numeric value with precision 10 and scale 0 (signed: –231 £ n £ 231 – 1,
unsigned: 0 £ n £ 232 – 1) a. |
SQL_REAL
|
REAL
|
Signed, approximate, numeric value with a mantissa precision 7 (zero or absolute value 10–38 to 1038). |
SQL_FLOAT
|
FLOAT
|
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10–308 to 10308). |
SQL_DOUBLE
|
DOUBLE
PRECISION
|
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10–308 to 10308). |
a An application uses SQLGetTypeInfo or SQLColAttributes to determine if a particular data type or a particular column in a result set is unsigned.
|
Extended SQL Data Types
The following table lists valid values of fSqlType for the extended SQL data types. These values are defined in SQLEXT.H. The table also lists the name and description of the corresponding data type. In the table, precision refers to the total number of digits and scale refers to the number of digits to the right of the decimal point.
Note This table is only a guideline and shows commonly used names, ranges, and limits of extended SQL data types. A given data source may support only some of the listed data types and the characteristics of the supported data types may differ from those listed below. For example, some data sources support unsigned numeric data types. For information about the data types in a specific data source, see the documentation for that data source. To determine which data types are supported by a data source and the characteristics of those data types, an application calls SQLGetTypeInfo.
fSqlType
|
Typical SQL Data Type
|
Description |
SQL_BIT
|
BIT
|
Single bit binary data. |
SQL_TINYINT
|
TINYINT
|
Exact numeric value with precision 3 and scale 0 (signed: –128 £ n £ 127, unsigned: 0£ n £ 255) a. |
SQL_BIGINT
|
BIGINT
|
Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: –263 £ n £ 263 – 1, unsigned: 0 £ n £ 264 – 1) a. |
SQL_BINARY
|
BINARY(n)
|
Binary data of fixed length n (1 £ n £ 255). |
SQL_VARBINARY
|
VARBINARY(n)
|
Variable length binary data of maximum length n
(1 £ n £ 255). |
SQL_LONGVARBINARY
|
LONG VARBINARY
|
Variable length binary data. Maximum length is data source–dependent. |
SQL_DATE
|
DATE
|
Date data. |
SQL_TIME
|
TIME
|
Time data. |
SQL_TIMESTAMP
|
TIMESTAMP
|
Date/time data. |
a An application uses SQLGetTypeInfo or SQLColAttributes to determine if a particular data type or a particular column in a result set is unsigned.
|
C Data Types
Data is stored in the application in ODBC C data types. The core C data types are those that support the minimum and core SQL data types. They also support some extended SQL data types. The extended C data types are those that only support extended SQL data types. The bookmark C data type is used only to retrieve bookmark values and should not be converted to other data types.
Note Unsigned C data types for integers were added to ODBC 2.0. Drivers must support the integer C data types specified in both ODBC 1.0 and ODBC 2.0; ODBC 2.0 or later applications must use the ODBC 1.0 integer C data types with ODBC 1.0 drivers and the ODBC 2.0 integer C data types with ODBC 2.0 drivers.
The C data type is specified in the SQLBindCol, SQLGetData, and SQLBindParameter functions with the fCType argument.
Core C Data Types
The following table lists valid values of fCType for the core C data types. These values are defined in SQL.H. The table also lists the ODBC C data type that implements each value of fCType and the definition of this data type from SQL.H.
fCType
|
ODBC C Typedef
|
C Type |
SQL_C_CHAR
|
UCHAR FAR *
|
unsigned char FAR * |
SQL_C_SSHORT
|
SWORD
|
short int |
SQL_C_USHORT
|
UWORD
|
unsigned short int |
SQL_C_SLONG
|
SDWORD
|
long int |
SQL_C_ULONG
|
UDWORD
|
unsigned long int |
SQL_C_FLOAT
|
SFLOAT
|
float |
SQL_C_DOUBLE
|
SDOUBLE
|
double |
Note Because objects of the CString class in Microsoft C++ are signed and string arguments in ODBC functions are unsigned, applications that pass CString objects to ODBC functions without casting them will receive compiler warnings.
Extended C Data Types
The following table lists valid values of fCType for the extended C data types. These values are defined in SQLEXT.H. The table also lists the ODBC C data type that implements each value of fCType and the definition of this data type from SQLEXT.H or SQL.H.
fCType
|
ODBC C Typedef
|
C Type |
SQL_C_BIT
|
UCHAR
|
unsigned char |
SQL_C_STINYINT
|
SCHAR
|
signed char |
SQL_C_UTINYINT
|
UCHAR
|
unsigned char |
SQL_C_BINARY
|
UCHAR FAR *
|
unsigned char FAR * |
SQL_C_DATE
|
DATE_STRUCT
|
struct tagDATE_STRUCT {
SWORD year; a
UWORD month; b
UWORD day; c
} |
SQL_C_TIME
|
TIME_STRUCT
|
struct tagTIME_STRUCT {
UWORD hour; d
UWORD minute; e
UWORD second; f
} |
SQL_C_TIMESTAMP
|
TIMESTAMP_STRUCT
|
struct tagTIMESTAMP_STRUCT {
SWORD year; a
UWORD month; b
UWORD day; c
UWORD hour; d
UWORD minute; e
UWORD second; f
UDWORD fraction; g
} |
a The value of the year field must be in the range from 0 to 9,999. Years are measured from 0 A.D. Some data sources do not support the entire range of years.
b The value of the month field must be in the range from 1 to 12.
c The value of day field must be in the range from 1 to the number of days in the month. The number of days in the month is determined from the values of the year and month fields and is 28, 29, 30, or 31.
d The value of the hour field must be in the range from 0 to 23.
e The value of the minute field must be in the range from 0 to 59.
f The value of the second field must be in the range from 0 to 59.
g The value of the fraction field is the number of billionths of a second and ranges from 0 to 999,999,999 (1 less than 1 billion). For example, the value of the fraction field for a half-second is 500,000,000, for a thousandth of a second (one millisecond) is 1,000,000, for a millionth of a second (one microsecond) is 1,000, and for a billionth of a second (one nanosecond) is 1.
|
Bookmark C Data Type
Bookmarks are 32-bit values used by an application to return to a specific row; an application retrieves a bookmark either from column 0 of the result set with SQLExtendedFetch or SQLGetData or by calling SQLGetStmtOption. For more information, see "Using Bookmarks" in Chapter 7, "Retrieving Results."
The following table lists the value of fCType for the bookmark C data type, the ODBC C data type that implements the bookmark C data type, and the definition of this data type from SQL.H.
fCType
|
ODBC C Typedef
|
C Type |
SQL_C_BOOKMARK
|
BOOKMARK
|
unsigned long int |
ODBC 1.0 C Data Types
In ODBC 1.0, all integer C data types were signed. The following table lists values of fCType for the integer C data types that were valid in ODBC 1.0. To remain compatible with applications that use ODBC 1.0, all drivers must support these values of fCType. To remain compatible with drivers that use ODBC 1.0, ODBC 2.0 or later applications must pass these values of fCType to ODBC 1.0 drivers. However, ODBC 2.0 or later applications must not pass these values to ODBC 2.0 or later drivers.
fCType
|
ODBC C Typedef
|
C Type |
SQL_C_TINYINT
|
SCHAR
|
signed char |
SQL_C_SHORT
|
SWORD
|
short int |
SQL_C_LONG
|
SDWORD
|
long int |
Because the ODBC 1.0 integer C data types (SQL_C_TINYINT, SQL_C_SHORT, and SQL_C_LONG) are signed, and because the ODBC integer SQL data types can be signed or unsigned, ODBC 1.0 applications and drivers had to interpret signed integer C data as signed or unsigned.
ODBC 2.0 applications and drivers treat the ODBC 1.0 integer C data types as unsigned only when:
- The column from which data will be retrieved is unsigned, and
- The C data type of the storage location in which the data will be placed is the default C data type for that column. (For a list of default C data types, see "Default C Data Types" later in this chapter.)
In all other cases, these applications and drivers treat the ODBC 1.0 integer C data types as signed.
In other words, for any conversion except the default conversion, ODBC 2.0 drivers check the validity of the conversion based on the numeric data value. For the default conversion, the drivers simply pass the data value without attempting to validate it numerically and applications interpret the data value according to whether the column is signed. (Applications call SQLGetTypeInfo to determine whether a column is signed or unsigned.)
For example, the following table shows how an ODBC 2.0 driver interprets ODBC 1.0 integer C data sent to both signed and unsigned SQL_SMALLINT columns.
From C Data Type
|
To SQL Data Type
|
C Data Values
|
SQL Data Values |
SQL_C_TINYINT
|
SQL_SMALLINT (signed)
|
–128 to 127
|
–128 to 127 |
|
SQL_SMALLINT (unsigned)
|
< 0
0 to 127
|
--- a
0 to 127 |
SQL_C_SHORT
(default conversion)
|
SQL_SMALLINT (signed)
|
–32,768 to 32,767
|
–32,768 to 32,767 |
|
SQL_SMALLINT (unsigned)
|
–32,768 to –1
0 to 32,767
|
32,768 to 65,535
0 to 32,767 |
SQL_C_LONG
|
SQL_SMALLINT (signed)
|
< –32,768
–32,768 to 32,767
> 32,767
|
--- a
–32,768 to 32,767
--- a |
|
SQL_SMALLINT (unsigned)
|
< 0
0 to 32,767
> 32,767
|
--- a
0 to 32,767
--- a |
a The driver returns SQLSTATE 22003 (Numeric value out of range).
|
Default C Data Types
If an application specifies SQL_C_DEFAULT for the fCType argument in SQLBindCol, SQLGetData, or SQLBindParameter, the driver assumes that the C data type of the output or input buffer corresponds to the SQL data type of the column or parameter to which the buffer is bound. For each ODBC SQL data type, the following table shows the corresponding, or default, C data type. For information about driver-specific SQL data types, see the driver’s documentation.
SQL Data Type
|
Default C Data Type |
SQL_CHAR
|
SQL_C_CHAR |
SQL_VARCHAR
|
SQL_C_CHAR |
SQL_LONGVARCHAR
|
SQL_C_CHAR |
SQL_DECIMAL
|
SQL_C_CHAR |
SQL_NUMERIC
|
SQL_C_CHAR |
SQL_BIT
|
SQL_C_BIT |
SQL_TINYINT
|
SQL_C_STINYINT or SQL_C_UTINYINT a |
SQL_SMALLINT
|
SQL_C_SSHORT or SQL_C_USHORT a |
SQL_INTEGER
|
SQL_C_SLONG or SQL_C_ULONG a |
SQL_BIGINT
|
SQL_C_CHAR |
SQL_REAL
|
SQL_C_FLOAT |
SQL_FLOAT
|
SQL_C_DOUBLE |
SQL_DOUBLE
|
SQL_C_DOUBLE |
SQL_BINARY
|
SQL_C_BINARY |
SQL_VARBINARY
|
SQL_C_BINARY |
SQL_LONGVARBINARY
|
SQL_C_BINARY |
SQL_DATE
|
SQL_C_DATE |
SQL_TIME
|
SQL_C_TIME |
SQL_TIMESTAMP
|
SQL_C_TIMESTAMP |
a If the driver can determine whether the column is signed or unsigned, such as when the driver is fetching data from the data source or when the data source supports only a signed type or only an unsigned type, but not both, the driver uses the corresponding signed or unsigned C data type. If the driver cannot determine whether the column is signed or unsigned, it passes the data value without attempting to validate it numerically.
|
Note For maximum interoperability, applications should specify a C data type other than SQL_C_DEFAULT. This allows drivers that promote SQL data types (and therefore cannot always determine default C data types) to return data. It also allows drivers that cannot determine whether an integer column is signed or unsigned to correctly return data.
Note ODBC 2.0 drivers use the ODBC 2.0 default C data types for both ODBC 1.0 and ODBC 2.0 integer C data.
Transferring Data in its Binary Form
Among data sources that use the same DBMS, an application can safely transfer data in the internal form used by that DBMS. For a given piece of data, the SQL data types must be the same in the source and target data sources. The C data type is SQL_C_BINARY.
When the application calls SQLFetch, SQLExtendedFetch, or SQLGetData to retrieve the data from the source data source, the driver retrieves the data from the data source and transfers it, without conversion, to a storage location of type SQL_C_BINARY. When the application calls SQLExecute, SQLExecDirect, or SQLPutData to send the data to the target data source, the driver retrieves the data from the storage location and transfers it, without conversion, to the target data source.
Note Applications that transfer any data (except binary data) in this manner are not interoperable among DBMS’s.
Precision, Scale, Length, and Display Size
SQLColAttributes, SQLColumns, and SQLDescribeCol return the precision, scale, length, and display size of a column in a table. SQLProcedureColumns returns the precision, scale, and length of a column in a procedure. SQLDescribeParam returns the precision or scale of a parameter in an SQL statement; SQLBindParameter sets the precision or scale of a parameter in an SQL statement. SQLGetTypeInfo returns the maximum precision and the minimum and maximum scales of an SQL data type on a data source.
Due to limitations in the size of the arguments these functions use, precision, length, and display size are limited to the size of an SDWORD, or 2,147,483,647.
Precision
The precision of a numeric column or parameter refers to the maximum number of digits used by the data type of the column or parameter. The precision of a nonnumeric column or parameter generally refers to either the maximum length or the defined length of the column or parameter. To determine the maximum precision allowed for a data type, an application calls SQLGetTypeInfo. The following table defines the precision for each ODBC SQL data type.
fSqlType
|
Precision |
SQL_CHAR
SQL_VARCHAR
|
The defined length of the column or parameter. For example, the precision of a column defined as CHAR(10) is 10. |
SQL_LONGVARCHAR a, b
|
The maximum length of the column or parameter. |
SQL_DECIMAL
SQL_NUMERIC
|
The defined number of digits. For example, the precision of a column defined as NUMERIC(10,3) is 10. |
SQL_BIT c
|
1 |
SQL_TINYINT c
|
3 |
SQL_SMALLINT c
|
5 |
SQL_INTEGER c
|
10 |
SQL_BIGINT c
|
19 (if signed) or 20 (if unsigned) |
SQL_REAL c
|
7 |
SQL_FLOAT c
|
15 |
SQL_DOUBLE c
|
15 |
SQL_BINARY
SQL_VARBINARY
|
The defined length of the column or parameter. For example, the precision of a column defined as BINARY(10) is 10. |
SQL_LONGVARBINARY a, b
|
The maximum length of the column or parameter. |
SQL_DATE c
|
10 (the number of characters in the yyyy-mm-dd format). |
SQL_TIME c
|
8 (the number of characters in the hh:mm:ss format). |
SQL_TIMESTAMP
|
The number of characters in the "yyyy-mm-dd hh:mm:ss[.f...]" format used by the TIMESTAMP data type. For example, if a timestamp does not use seconds or fractional seconds, the precision is 16 (the number of characters in the "yyyy-mm-dd hh:mm" format). If a timestamp uses thousandths of a second, the precision is 23 (the number of characters in the "yyyy-mm-dd hh:mm:ss.fff" format). |
a For an ODBC 1.0 application calling SQLSetParam in an ODBC 2.0 driver, and for an ODBC 2.0 application calling SQLBindParameter in an ODBC 1.0 driver, when pcbValue is SQL_DATA_AT_EXEC, cbColDef must be set to the total length of the data to be sent, not the precision as defined in this table.
b If the driver cannot determine the column or parameter length, it returns SQL_NO_TOTAL.
c The cbColDef argument of SQLBindParameter is ignored for this data type.
|
Scale
The scale of a numeric column or parameter refers to the maximum number of digits to the right of the decimal point. For approximate floating point number columns or parameters, the scale is undefined, since the number of digits to the right of the decimal point is not fixed. (For the SQL_DECIMAL and SQL_NUMERIC data types, the maximum scale is generally the same as the maximum precision. However, some data sources impose a separate limit on the maximum scale. To determine the minimum and maximum scales allowed for a data type, an application calls SQLGetTypeInfo.) The following table defines the scale for each ODBC SQL data type.
fSqlType
|
Scale |
SQL_CHAR a
SQL_VARCHAR a
SQL_LONGVARCHAR a
|
Not applicable. |
SQL_DECIMAL
SQL_NUMERIC
|
The defined number of digits to the right of the decimal point. For example, the scale of a column defined as NUMERIC(10,3) is 3. |
SQL_BIT a
SQL_TINYINT a
SQL_SMALLINT a
SQL_INTEGER a
SQL_BIGINT a
|
0 |
SQL_REAL a
SQL_FLOAT a
SQL_DOUBLE a
|
Not applicable. |
SQL_BINARY a
SQL_VARBINARY a
SQL_LONGVARBINARY a
|
Not applicable. |
SQL_DATE a
SQL_TIME a
|
Not applicable. |
SQL_TIMESTAMP
|
The number of digits to the right of the decimal point in the "yyyy-mm-dd hh:mm:ss[.f...]" format. For example, if the TIMESTAMP data type uses the "yyyy-mm-dd hh:mm:ss.fff" format, the scale is 3. |
a The ibScale argument of SQLBindParameter is ignored for this data type.
|
Length
The length of a column is 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. Note that the length of a column may be different than the number of bytes required to store the data on the data source. For a list of default C data types, see the "Default C Data Types" earlier in this appendix.
The following table defines the length for each ODBC SQL data type.
fSqlType
|
Length |
SQL_CHAR
SQL_VARCHAR
|
The defined length of the column. For example, the length of a column defined as CHAR(10) is 10. |
SQL_LONGVARCHAR a
|
The maximum length of the column. |
SQL_DECIMAL
SQL_NUMERIC
|
The maximum number of digits plus 2. Since these data types are returned as character strings, characters are needed for the digits, a sign, and a decimal point. For example, the length of a column defined as NUMERIC(10,3) is 12. |
SQL_BIT
SQL_TINYINT
|
1 (one byte). |
SQL_SMALLINT
|
2 (two bytes). |
SQL_INTEGER
|
4 (four bytes). |
SQL_BIGINT
|
20 (since this data type is returned as a character string, characters are needed for 19 digits and a sign, if signed, or 20 digits, if unsigned). |
SQL_REAL
|
4 (four bytes). |
SQL_FLOAT
|
8 (eight bytes). |
SQL_DOUBLE
|
8 (eight bytes). |
SQL_BINARY
SQL_VARBINARY
|
The defined length of the column. For example, the length of a column defined as BINARY(10) is 10. |
SQL_LONGVARBINARY a
|
The maximum length of the column. |
SQL_DATE
SQL_TIME
|
6 (the size of the DATE_STRUCT or TIME_STRUCT structure). |
SQL_TIMESTAMP
|
16 (the size of the TIMESTAMP_STRUCT structure). |
a If the driver cannot determine the column or parameter length, it returns SQL_NO_TOTAL.
|
Display Size
The display size of a column is the maximum number of bytes needed to display data in character form. The following table defines the display size for each ODBC SQL data type.
fSqlType
|
Display Size |
SQL_CHAR
SQL_VARCHAR
|
The defined length of the column. For example, the display size of a column defined as CHAR(10) is 10. |
SQL_LONGVARCHAR a
|
The maximum length of the column. |
SQL_DECIMAL
SQL_NUMERIC
|
The precision of the column plus 2 (a sign, precision digits, and a decimal point). For example, the display size of a column defined as NUMERIC(10,3) is 12. |
SQL_BIT
|
1 (1 digit). |
SQL_TINYINT
|
4 if signed (a sign and 3 digits) or 3 if unsigned (3 digits). |
SQL_SMALLINT
|
6 if signed (a sign and 5 digits) or 5 if unsigned (5 digits). |
SQL_INTEGER
|
11 if signed (a sign and 10 digits) or 10 if unsigned (10 digits). |
SQL_BIGINT
|
20 (a sign and 19 digits if signed or 20 digits if unsigned). |
SQL_REAL
|
13 (a sign, 7 digits, a decimal point, the letter E, a sign, and 2 digits). |
SQL_FLOAT
SQL_DOUBLE
|
22 (a sign, 15 digits, a decimal point, the letter E, a sign, and 3 digits). |
SQL_BINARY
SQL_VARBINARY
|
The defined length of the column times 2 (each binary byte is represented by a 2 digit hexadecimal number). For example, the display size of a column defined as BINARY(10) is 20. |
SQL_LONGVARBINARY a
|
The maximum length of the column times 2. |
SQL_DATE
|
10 (a date in the format yyyy-mm-dd). |
SQL_TIME
|
8 (a time in the format hh:mm:ss). |
SQL_TIMESTAMP
|
19 (if the scale of the timestamp is 0) or 20 plus the scale of the timestamp (if the scale is greater than 0). This is the number of characters in the "yyyy-mm-dd hh:mm:ss[.f...]" format. For example, the display size of a column storing thousandths of a second is 23 (the number of characters in "yyyy-mm-dd hh:mm:ss.fff"). |
a If the driver cannot determine the column or parameter length, it returns SQL_NO_TOTAL.
|
Converting Data from SQL to C Data Types
When an application calls SQLExtendedFetch, SQLFetch, or SQLGetData, the driver retrieves the data from the data source. If necessary, it converts the data from the data type in which the driver retrieved it to the data type specified by the fCType argument in SQLBindCol or SQLGetData. Finally, it stores the data in the location pointed to by the rgbValue argument in SQLBindCol or SQLGetData.
Note The word convert is used in this section in a broad sense, and includes the transfer of data, without a conversion in data type, from one storage location to another.
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. For a given ODBC 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. For each outcome, the third and fourth columns list the values of the rgbValue and pcbValue arguments specified in SQLBindCol or SQLGetData after the driver has attempted to convert the data. The last column lists the SQLSTATE returned for each outcome by SQLExtendedFetch, SQLFetch, or SQLGetData.
If the fCType 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, SQLExtendedFetch, SQLFetch, or SQLGetData returns SQLSTATE 07006 (Restricted data type attribute violation). If the fCType 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, SQLExtendedFetch, SQLFetch, or SQLGetData returns SQLSTATE S1C00 (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 S1009 (Invalid argument value).
The following terms and conventions are used in the tables:
- Length of data is the number of bytes of C data available to return in rgbValue, regardless of whether the data will be truncated before it is returned to the application. For string data, this does not include the null termination byte.
- Display size is the total number of bytes needed to display the data in character format.
- Words in italics represent function arguments or elements of the ODBC SQL grammar.
SQL to C: Character
The character ODBC SQL data types are:
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
The following table shows the ODBC C data types to which character SQL data may be converted. For an explanation of the columns and terms in the table, see the list above.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
Length of data < cbValueMax
Length of data ³ cbValueMax
|
Data
Truncated data
|
Length of data
Length of data
|
N/A
01004 |
SQL_C_STINYINT
SQL_C_UTINYINT
SQL_C_TINYINT a
SQL_C_SSHORT
SQL_C_USHORT
SQL_C_SHORT a
SQL_C_SLONG
SQL_C_ULONG
SQL_C_LONG a
|
Data converted without truncation b
Data converted with truncation of fractional digits b
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
Untouched
Untouched
|
Size of the C data type
Size of the C data type
Untouched
Untouched
|
N/A
01004
22003
22005 |
SQL_C_FLOAT
SQL_C_DOUBLE
|
Data is within the range of the data type to which the number is being converted b
Data is outside the range of the data type to which the number is being converted b
Data is not a numeric-literal b
|
Data
Untouched
Untouched
|
Size of the C data type
Untouched
Untouched
|
N/A
22003
22005 |
SQL_C_BIT
|
Data is 0 or 1 a
Data is greater than 0, less than 2, and not equal to 1 a
Data is less than 0 or greater than or equal to 2 a
Data is not a numeric-literal a
|
Data
Truncated data
Untouched
Untouched
|
1 c
1 c
Untouched
Untouched
|
N/A
01004
22003
22005 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Truncated data
|
Length of data
Length of data
|
N/A
01004 |
SQL_C_DATE
|
Data value is a valid date-value b
Data value is a valid timestamp-value; time portion is zero b
Data value is a valid timestamp-value; time portion is non-zero b, d
Data value is not a valid date-value or timestamp-value b
|
Data
Data
Truncated data
Untouched
|
6 c
6 c
6 c
Untouched
|
N/A
N/A
01004
22008 |
SQL_C_TIME
|
Data value is a valid time-value b
Data value is a valid timestamp-value; fractional seconds portion is zero b, e
Data value is a valid timestamp-value; fractional seconds portion is non-zero b, e, f
Data value is not a valid time-value or timestamp-value b
|
Data
Data
Truncated data
Untouched
|
6 c
6 c
6 c
Untouched
|
N/A
N/A
01004
22008 |
SQL_C_
TIMESTAMP
|
Data value is a valid timestamp-value; fractional seconds portion not truncated b
Data value is a valid timestamp-value; fractional seconds portion truncated b
Data value is a valid date-value b
Data value is a valid time-value b
Data value is not a valid date-value, time-value, or timestamp-value b
|
Data
Truncated data
Data g
Data h
Untouched
|
16 c
16 c
16 c
16 c
Untouched
|
N/A
N/A
N/A
N/A
22008 |
a For more information, see "ODBC 1.0 C Data Types," earlier in this appendix.
b 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.
c This is the size of the corresponding C data type.
d The time portion of the timestamp-value is truncated.
e The date portion of the timestamp-value is ignored.
f The fractional seconds portion of the timestamp is truncated.
g The time fields of the timestamp structure are set to zero.
h The date fields of the timestamp structure are set to the current date.
|
When character SQL data is converted to numeric, date, time, or timestamp C data, leading and trailing spaces are ignored.
All drivers that support date, time, and timestamp data can convert character SQL data to date, time, or timestamp C data as specified in the previous table. Drivers may be able to convert character SQL data from other, driver-specific formats to date, time, or timestamp C data. Such conversions are not interoperable among data sources.
SQL to C: Numeric
The numeric ODBC SQL data types are:
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 page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
Display size < cbValueMax
Number of whole (as opposed to fractional) digits < cbValueMax
Number of whole (as opposed to fractional) digits ³ cbValueMax
|
Data
Truncated data
Untouched
|
Length of data
Length of data
Untouched
|
N/A
01004
22003 |
SQL_C_STINYINT
SQL_C_UTINYINT
SQL_C_TINYINT a
SQL_C_SSHORT
SQL_C_USHORT
SQL_C_SHORT a
SQL_C_SLONG
SQL_C_ULONG
SQL_C_LONG a
|
Data converted without truncation b
Data converted with truncation of fractional digits b
Conversion of data would result in loss of whole (as opposed to fractional) digits b
|
Data
Truncated data
Untouched
|
Size of the C data type
Size of the C data type
Untouched
|
N/A
01004
22003 |
SQL_C_FLOAT
SQL_C_DOUBLE
|
Data is within the range of the data type to which the number is being converted b
Data is outside the range of the data type to which the number is being converted b
|
Data
Untouched
|
Size of the C data type
Untouched
|
N/A
22003 |
SQL_C_BIT
|
Data is 0 or 1 b
Data is greater than 0, less than 2, and not equal to 1 b
Data is less than 0 or greater than or equal to 2 b
|
Data
Truncated data
Untouched
|
1 c
1 c
Untouched
|
N/A
01004
22003 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Untouched
|
Length of data
Untouched
|
N/A
22003 |
a For more information, see "ODBC 1.0 C Data Types," earlier in this appendix.
b 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.
c This is the size of the corresponding C data type.
|
SQL to C: Bit
The bit ODBC SQL data type is:
SQL_BIT
The following table shows the ODBC C data types to which bit SQL data may be converted. For an explanation of the columns and terms in the table, see page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
cbValueMax > 1
cbValueMax £ 1
|
Data
Untouched
|
1
Untouched
|
N/A
22003 |
SQL_C_STINYINT
SQL_C_UTINYINT
SQL_C_TINYINT a
SQL_C_SSHORT
SQL_C_USHORT
SQL_C_SHORT a
SQL_C_SLONG
SQL_C_ULONG
SQL_C_LONG a
SQL_C_FLOAT
SQL_C_DOUBLE
|
None b
|
Data
|
Size of the C data type
|
N/A |
SQL_C_BIT
|
None b
|
Data
|
1 c
|
N/A |
SQL_C_BINARY
|
cbValueMax ³ 1
cbValueMax < 1
|
Data
Untouched
|
1
Untouched
|
N/A
22003 |
a For more information, see "ODBC 1.0 C Data Types," earlier in this appendix.
b 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.
c This is the size of the corresponding C data type.
|
When bit SQL data is converted to character C data, the possible values are "0" and "1".
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 page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
(Length of data) * 2 < cbValueMax
(Length of data) * 2 ³ cbValueMax
|
Data
Truncated data
|
Length of data
Length of data
|
N/A
01004 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Truncated data
|
Length of data
Length of data
|
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".
The driver always converts individual bytes to pairs of hexadecimal digits and terminates the character string with a null byte. Because of this, if cbValueMax is even and is less than the length of the converted data, the last byte of the rgbValue 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.)
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 page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
cbValueMax ³ 11
cbValueMax < 11
|
Data
Untouched
|
10
Untouched
|
N/A
22003 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Untouched
|
Length of data
Untouched
|
N/A
22003 |
SQL_C_DATE
|
None a
|
Data
|
6 c
|
N/A |
SQL_C_TIMESTAMP
|
None a
|
Data b
|
16 c
|
N/A |
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 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.
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 page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
cbValueMax ³ 9
cbValueMax < 9
|
Data
Untouched
|
8
Untouched
|
N/A
22003 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Untouched
|
Length of data
Untouched
|
N/A
22003 |
SQL_C_TIME
|
None a
|
Data
|
6 c
|
N/A |
SQL_C_TIMESTAMP
|
None a
|
Data b
|
16 c
|
N/A |
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 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.
c 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 page 574.
fCType
|
Test
|
rgb-
Value
|
pcb-
Value
|
SQL-
STATE |
SQL_C_CHAR
|
cbValueMax > Display size
20 £ cbValueMax £ Display size
cbValueMax < 20
|
Data
Truncated data b
Untouched
|
Length of data
Length of data
Untouched
|
N/A
01004
22003 |
SQL_C_BINARY
|
Length of data £ cbValueMax
Length of data > cbValueMax
|
Data
Untouched
|
Length of data
Untouched
|
N/A
22003 |
SQL_C_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
01004 |
SQL_C_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
01004 |
SQL_C_TIMESTAMP
|
Fractional seconds portion of timestamp is not truncated a
Fractional seconds portion of timestamp is truncated a
|
Data e
Truncated data e
|
16 f
16 f
|
N/A
01004 |
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 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. (Except for the decimal point and fractional seconds, the entire format must be used, regardless of the precision of the timestamp SQL data type.)
SQL to C Data Conversion Examples
The following examples illustrate how the driver converts SQL data to C data:
SQL Data Type
|
SQL Data
Value
|
C Data Type
|
cbValue-
Max
|
rgbValue
|
SQL-
STATE |
SQL_CHAR
|
abcdef
|
SQL_C_CHAR
|
7
|
abcdef\0 a
|
N/A |
SQL_CHAR
|
abcdef
|
SQL_C_CHAR
|
6
|
abcde\0 a
|
01004 |
SQL_
DECIMAL
|
1234.56
|
SQL_C_CHAR
|
8
|
1234.56\0 a
|
N/A |
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
|
N/A |
SQL_
DECIMAL
|
1234.56
|
SQL_C_
SSHORT
|
ignored
|
1234
|
01004 |
SQL_
DECIMAL
|
1234.56
|
SQL_C_
STINYINT
|
ignored
|
----
|
22003 |
SQL_
DOUBLE
|
1.2345678
|
SQL_C_
DOUBLE
|
ignored
|
1.2345678
|
N/A |
SQL_
DOUBLE
|
1.2345678
|
SQL_C_
FLOAT
|
ignored
|
1.234567
|
N/A |
SQL_
DOUBLE
|
1.2345678
|
SQL_C_
STINYINT
|
ignored
|
1
|
N/A |
SQL_DATE
|
1992-12-31
|
SQL_C_CHAR
|
11
|
1992-12-31\0 a
|
N/A |
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
|
N/A |
SQL_
TIMESTAMP
|
1992-12-31
23:45:55.12
|
SQL_C_CHAR
|
23
|
1992-12-31
23:45:55.12\0 a
|
N/A |
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 |
a "\0" represents a null-termination byte. The driver always null-terminates SQL_C_CHAR data.
b The numbers in this list are the numbers stored in the fields of the TIMESTAMP_STRUCT structure.
|
Converting Data from C to SQL Data Types
When an application calls SQLExecute or SQLExecDirect, the driver retrieves the data for any parameters bound with SQLBindParameter from storage locations in the application. For data-at-execution parameters, the application sends the parameter data with SQLPutData. If necessary, the driver converts the data from the data type specified by the fCType argument in SQLBindParameter to the data type specified by the fSqlType argument in SQLBindParameter. Finally, the driver sends the data to the data source.
Note The word convert is used in this section in a broad sense, and includes the transfer of data, without a conversion in data type, from one storage location to another.
The tables in the following sections describe how the driver or data source converts data sent to the data source; drivers are required to support conversions from all ODBC C data types to the ODBC SQL data types that they support. For a given ODBC C data type, the first column of the table lists the legal input values of the fSqlType argument in SQLBindParameter. The second column lists the outcomes of a test that the driver performs to determine if it can convert the data. The third column lists the SQLSTATE returned for each outcome by SQLExecDirect, SQLExecute, or SQLPutData. Data is sent to the data source only if SQL_SUCCESS is returned.
If the fSqlType argument in SQLBindParameter contains a value for an ODBC SQL data type that is not shown in the table for a given C data type, SQLBindParameter returns SQLSTATE 07006 (Restricted data type attribute violation). If the fSqlType argument contains a driver-specific value and the driver does not support the conversion from the specific ODBC C data type to that driver-specific SQL data type, SQLBindParameter returns SQLSTATE S1C00 (Driver not capable).
If the rgbValue and pcbValue arguments specified in SQLBindParameter are both null pointers, that function returns SQLSTATE S1009 (Invalid argument value). Though it is not shown in the tables, an application sets the value pointed to by the pcbValue argument of SQLBindParameter or the value of the cbValue argument to SQL_NULL_DATA to specify a NULL SQL data value. The application sets these values to SQL_NTS to specify that the value in rgbValue is a null-terminated string.
The following terms are used in the tables:
- Length of data is the number of bytes of SQL data available to send to the data source, regardless of whether the data will be truncated before it is sent to the data source. For string data, this does not include the null termination byte.
- Column length and display size are defined for each SQL data type in the section "Precision, Scale, Length, and Display Size" earlier in this chapter.
- Number of digits is the number of characters used to represent a number, including the minus sign, decimal point, and exponent (if needed).
- Words in italics represent elements of the ODBC SQL grammar.
C to SQL: Character
The character ODBC C data type is:
SQL_C_CHAR
The following table shows the ODBC SQL data types to which C character data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQL-
STATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Length of data £ Column length
Length of data > Column length
|
N/A
01004 |
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
|
Data converted without truncation
Data converted with truncation of fractional digits
Conversion of data would result in loss of whole (as opposed to fractional) digits
Data value is not a numeric-literal
|
N/A
01004
22003
22005 |
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
|
Data is within the range of the data type to which the number is being converted
Data is outside the range of the data type to which the number is being converted
Data value is not a numeric-literal
|
N/A
22003
22005 |
SQL_BIT
|
Data is 0 or 1
Data is greater than 0, less than 2, and not equal to 1
Data is less than 0 or greater than or equal to 2
Data is not a numeric-literal
|
N/A
01004
22003
22005 |
SQL_BINARY
SQL_VARBINARY
SQL_LONG-VARBINARY
|
(Length of data) / 2 £ Column length
(Length of data) / 2 > Column length
Data value is not a hexadecimal value
|
N/A
01004
22005 |
SQL_DATE
|
Data value is a valid ODBC-date-literal
Data value is a valid ODBC-timestamp-literal; time portion is zero
Data value is a valid ODBC-timestamp-literal; time portion is non-zero a
Data value is not a valid ODBC-date-literal or ODBC-timestamp-literal
|
N/A
N/A
01004
22008 |
SQL_TIME
|
Data value is a valid ODBC-time-literal
Data value is a valid ODBC-timestamp-literal; fractional seconds portion is zero b
Data value is a valid ODBC-timestamp-literal; fractional seconds portion is non-zero b, c
Data value is not a valid ODBC-time-literal or ODBC-timestamp-literal
|
N/A
N/A
01004
22008 |
SQL_TIMESTAMP
|
Data value is a valid ODBC-timestamp-literal; fractional seconds portion not truncated
Data value is a valid ODBC-timestamp-literal; fractional seconds portion
truncated
Data value is a valid ODBC-date-literal d
Data value is a valid ODBC-time-literal e
Data value is not a valid ODBC-date-literal, ODBC-time-literal,or ODBC-timestamp-literal
|
N/A
01004
N/A
N/A
22008 |
a The time portion of the timestamp is truncated.
b The date portion of the timestamp is ignored.
c The fractional seconds portion of the timestamp is truncated.
d The time portion of the timestamp is set to zero.
e The date portion of the timestamp is set to the current date.
|
When character C data is converted to numeric, date, time, or timestamp SQL data, leading and trailing blanks are ignored.
When character C data is converted to binary SQL data, each two bytes of character data are converted to a single byte (8 bits) of binary data. Each two bytes of character data represent a number in hexadecimal form. For example, "01" is converted to a binary 00000001 and "FF" is converted to a binary 11111111.
The driver always converts pairs of hexadecimal digits to individual bytes and ignores the null termination byte. Because of this, if the length of the character string is odd, the last byte of the string (excluding the null termination byte, if any) is not converted.
All drivers that support date, time, and timestamp data can convert character C data to date, time, or timestamp SQL data as specified in the previous table. Drivers may be able to convert character C data from other, driver-specific formats to date, time, or timestamp SQL data. Such conversions are not interoperable among data sources.
C to SQL: Numeric
The numeric ODBC C data types are:
SQL_C_STINYINT SQL_C_SLONG
SQL_C_UTINYINT SQL_C_ULONG
SQL_C_TINYINT SQL_C_LONG
SQL_C_SSHORT SQL_C_FLOAT
SQL_C_USHORT SQL_C_DOUBLE
SQL_C_SHORT
For more information about the SQL_C_TINYINT, SQL_C_SHORT, and SQL_C_LONG data types, see "ODBC 1.0 C Data Types," earlier in this appendix. The following table shows the ODBC SQL data types to which numeric C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQL-
STATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Number of digits £ Column length
Number of whole (as opposed to fractional) digits £ Column length
Number of whole (as opposed to fractional) digits > Column length
|
N/A
01004
22003 |
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
|
Data converted without truncation
Data converted with truncation of fractional digits
Conversion of data would result in loss of whole (as opposed to fractional) digits
|
N/A
01004
22003 |
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
|
Data is within the range of the data type to which the number is being converted
Data is outside the range of the data type to which the number is being converted
|
N/A
22003 |
SQL_BIT
|
Data is 0 or 1
Data is greater than 0, less than 2, and not equal to 1
Data is less than 0 or greater than or equal to 2
|
N/A
01004
22003 |
The value pointed to by the pcbValue argument of SQLBindParameter and the value of the cbValue argument of SQLPutData are ignored when data is converted from the numeric C data types. The driver assumes that the size of rgbValue is the size of the numeric C data type.
C to SQL: Bit
The bit ODBC C data type is:
SQL_C_BIT
The following table shows the ODBC SQL data types to which bit C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQLSTATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
None
|
N/A |
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
|
None
|
N/A |
SQL_BIT
|
None
|
N/A |
The value pointed to by the pcbValue argument of SQLBindParameter and the value of the cbValue argument of SQLPutData are ignored when data is converted from the bit C data type. The driver assumes that the size of rgbValue is the size of the bit C data type.
C to SQL: Binary
The binary ODBC C data type is:
SQL_C_BINARY
The following table shows the ODBC SQL data types to which binary C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQL-
STATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Length of data £ Column length
Length of data > Column length
|
N/A
01004 |
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
|
Length of data = SQL data length a
Length of data ¹ SQL data length a
|
N/A
22003 |
SQL_BIT
|
Length of data = SQL data length a
Length of data ¹ SQL data length a
|
N/A
22003 |
SQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY
|
Length of data £ Column length
Length of data > Column length
|
N/A
01004 |
SQL_DATE
SQL_TIME
SQL_TIMESTAMP
|
Length of data = SQL data length a
Length of data ¹ SQL data length a
|
N/A
22003 |
a The SQL data length is the number of bytes needed to store the data on the data source. (This may be different than the column length, as defined earlier in this appendix.)
|
C to SQL: Date
The date ODBC C data type is:
SQL_C_DATE
The following table shows the ODBC SQL data types to which date C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQLSTATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Column length ³ 10
Column length < 10
Data value is not a valid date
|
N/A
22003
22008 |
SQL_DATE
|
Data value is a valid date
Data value is not a valid date
|
N/A
22008 |
SQL_TIMESTAMP
|
Data value is a valid date a
Data value is not a valid date
|
N/A
22008 |
a The time portion of the timestamp is set to zero.
|
For information about what values are valid in a SQL_C_DATE structure, see "Extended C Data Types," earlier in this appendix.
When date C data is converted to character SQL data, the resulting character data is in the "yyyy-mm-dd" format.
The value pointed to by the pcbValue argument of SQLBindParameter and the value of the cbValue argument of SQLPutData are ignored when data is converted from the date C data type. The driver assumes that the size of rgbValue is the size of the date C data type.
C to SQL: Time
The time ODBC C data type is:
SQL_C_TIME
The following table shows the ODBC SQL data types to which time C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQLSTATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Column length ³ 8
Column length < 8
Data value is not a valid time
|
N/A
22003
22008 |
SQL_TIME
|
Data value is a valid time
Data value is not a valid time
|
N/A
22008 |
SQL_TIMESTAMP
|
Data value is a valid time a
Data value is not a valid time
|
N/A
22008 |
a The date portion of the timestamp is set to the current date and the fractional seconds portion of the timestamp is set to zero.
|
For information about what values are valid in a SQL_C_TIME structure, see "Extended C Data Types," earlier in this appendix.
When time C data is converted to character SQL data, the resulting character data is in the "hh:mm:ss" format.
The value pointed to by the pcbValue argument of SQLBindParameter and the value of the cbValue argument of SQLPutData are ignored when data is converted from the time C data type. The driver assumes that the size of rgbValue is the size of the time C data type.
C to SQL: Timestamp
The timestamp ODBC C data type is:
SQL_C_TIMESTAMP
The following table shows the ODBC SQL data types to which timestamp C data may be converted. For an explanation of the columns and terms in the table, see page 587.
fSqlType
|
Test
|
SQL-
STATE |
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
|
Column length ³ Display size
19 £ Column length < Display size a
Column length < 19
Data value is not a valid date
|
N/A
01004
22003
22008 |
SQL_DATE
|
Time fields are zero
Time fields are non-zero b
Data value does not contain a valid date
|
N/A
01004
22008 |
SQL_TIME
|
Fractional seconds fields are zero c
Fractional seconds fields are non-zero c, d
Data value does not contain a valid time
|
N/A
01004
22008 |
SQL_TIMESTAMP
|
Fractional seconds fields are not truncated
Fractional seconds fields are truncated d
Data value is not a valid timestamp
|
N/A
01004
22008 |
a The fractional seconds of the timestamp are truncated.
b The time fields of the timestamp structure are truncated.
c The date fields of the timestamp structure are ignored.
d The fractional seconds fields of the timestamp structure are truncated.
|
For information about what values are valid in a SQL_C_TIMESTAMP structure, see "Extended C Data Types," earlier in this appendix.
When timestamp C data is converted to character SQL data, the resulting character data is in the "yyyy-mm-dd hh:mm:ss[.f...]" format.
The value pointed to by the pcbValue argument of SQLBindParameter and the value of the cbValue argument of SQLPutData are ignored when data is converted from the timestamp C data type. The driver assumes that the size of rgbValue is the size of the timestamp C data type.
C to SQL Data Conversion Examples
The following examples illustrate how the driver converts C data to SQL data:
C DataType
|
C Data Value
|
SQL Data Type
|
Column
length
|
SQL Data
Value
|
SQL-
STATE |
SQL_C_CHAR
|
abcdef\0 a
|
SQL_CHAR
|
6
|
abcdef
|
N/A |
SQL_C_CHAR
|
abcdef\0 a
|
SQL_CHAR
|
5
|
abcde
|
01004 |
SQL_C_CHAR
|
1234.56\0 a
|
SQL_DECIMAL
|
8 b
|
1234.56
|
N/A |
SQL_C_CHAR
|
1234.56\0 a
|
SQL_DECIMAL
|
7 b
|
1234.5
|
01004 |
SQL_C_CHAR
|
1234.56\0 a
|
SQL_DECIMAL
|
4
|
----
|
22003 |
SQL_C_
FLOAT
|
1234.56
|
SQL_FLOAT
|
not
applicable
|
1234.56
|
N/A |
SQL_C_
FLOAT
|
1234.56
|
SQL_INTEGER
|
not
applicable
|
1234
|
01004 |
SQL_C_
FLOAT
|
1234.56
|
SQL_TINYINT
|
not
applicable
|
----
|
22003 |
SQL_C_DATE
|
1992,12,31 c
|
SQL_CHAR
|
10
|
1992-12-31
|
N/A |
SQL_C_DATE
|
1992,12,31 c
|
SQL_CHAR
|
9
|
----
|
22003 |
SQL_C_DATE
|
1992,12,31 c
|
SQL_
TIMESTAMP
|
not
applicable
|
1992-12-31 00:00:00.0
|
N/A |
SQL_C_
TIMESTAMP
|
1992,12,31,
23,45,55,
120000000 d
|
SQL_CHAR
|
22
|
1992-12-31 23:45:55.12
|
N/A |
SQL_C_
TIMESTAMP
|
1992,12,31,
23,45,55,
120000000 d
|
SQL_CHAR
|
21
|
1992-12-31 23:45:55.1
|
01004 |
SQL_C_
TIMESTAMP
|
1992,12,31,
23,45,55,
120000000 d
|
SQL_CHAR
|
18
|
----
|
22003 |
a "\0" represents a null-termination byte. The null-termination byte is required only if the length of the data is SQL_NTS.
b In addition to bytes for numbers, one byte is required for a sign and another byte is required for the decimal point.
c The numbers in this list are the numbers stored in the fields of the DATE_STRUCT structure.
d The numbers in this list are the numbers stored in the fields of the TIMESTAMP_STRUCT structure.
|
Copyright © 1992-1997 Solid Information Technology Ltd All rights reserved.