Each DBMS defines its own SQL types. Each ODBC driver exposes only those SQL data types that the associated DBMS defines. Information about how a driver maps DBMS SQL types to the ODBC-defined SQL type identifiers and how a driver maps DBMS SQL types to its own driver-specific SQL type identifiers is returned through a call to SQLGetTypeInfo. A driver also returns the SQL data types when describing the data types of columns and parameters through calls to SQLColAttribute, SQLColumns, SQLDescribeCol, SQLDescribeParam, SQLProcedureColumns, and SQLSpecialColumns.
.gif) Note |
|---|
|
The SQL data types are contained in the SQL_DESC_ CONCISE_TYPE, SQL_DESC_TYPE, and SQL_DESC_DATETIME_INTERVAL_CODE fields of the implementation descriptors. Characteristics of the SQL data types are contained in the SQL_DESC_PRECISION, SQL_DESC_SCALE, SQL_DESC_LENGTH, and SQL_DESC_OCTET_LENGTH fields of the implementation descriptors. For more information, see Data Type Identifiers and Descriptors later in this appendix.
|
A given driver and data source do not necessarily support all the SQL data types that are defined in this appendix. A driver's support for SQL data types depends on the level of SQL-92 that the driver complies with. To determine the level of SQL-92 grammar supported by the driver, an application calls SQLGetInfo with the SQL_SQL_CONFORMANCE information type. Additionally, a given driver and data source 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. For information about the data types in a specific data source, see the documentation for that data source.
| Important |
|---|
|
The tables throughout this appendix are only guidelines and show typically used names, ranges, and limits of SQL data types. A given data source might support only some of the listed data types, and the characteristics of the supported data types can differ from those listed.
|
The following table lists valid SQL type identifiers for all SQL data types. The table also lists the name and description of the corresponding data type from SQL-92 (if one exists).
|
SQL type identifier[1]
|
Typical SQL data
type[2]
|
Typical type description
|
|---|
|
SQL_CHAR
|
CHAR(n)
|
Character string of fixed string length n.
|
|
SQL_VARCHAR
|
VARCHAR(n)
|
Variable-length character string with a maximum string length n.
|
|
SQL_LONGVARCHAR
|
LONG VARCHAR
|
Variable length character data. Maximum length is data source–dependent.[9]
|
|
SQL_WCHAR
|
WCHAR(n)
|
Unicode character string of fixed string length n
|
|
SQL_WVARCHAR
|
VARWCHAR(n)
|
Unicode variable-length character string with a maximum string length n
|
|
SQL_WLONGVARCHAR
|
LONGWVARCHAR
|
Unicode variable-length character data. Maximum length is data source–dependent
|
|
SQL_DECIMAL
|
DECIMAL(p,s)
|
Signed, exact, numeric value with a precision of at least p and scale s. (The maximum precision is driver-defined.) (1 <= p <= 15; s <= p).[4]
|
|
SQL_NUMERIC
|
NUMERIC(p,s)
|
Signed, exact, numeric value with a precision p and scale s (1 <= p <= 15; s <= p).[4]
|
|
SQL_SMALLINT
|
SMALLINT
|
Exact numeric value with precision 5 and scale 0 (signed: –32,768 <= n <= 32,767, unsigned: 0 <= n <= 65,535)[3].
|
|
SQL_INTEGER
|
INTEGER
|
Exact numeric value with precision 10 and scale 0 (signed: –2[31] <= n <= 2[31] – 1, unsigned: 0 <= n <= 2[32] – 1)[3].
|
|
SQL_REAL
|
REAL
|
Signed, approximate, numeric value with a binary precision 24 (zero or absolute value 10[–38] to 10[38]).
|
|
SQL_FLOAT
|
FLOAT(p)
|
Signed, approximate, numeric value with a binary precision of at least p. (The maximum precision is driver-defined.)[5]
|
|
SQL_DOUBLE
|
DOUBLE PRECISION
|
Signed, approximate, numeric value with a binary precision 53 (zero or absolute value 10[–308] to 10[308]).
|
|
SQL_BIT
|
BIT
|
Single bit binary data.[8]
|
|
SQL_TINYINT
|
TINYINT
|
Exact numeric value with precision 3 and scale 0 (signed: –128 <= n <= 127, unsigned: 0 <= n <= 255)[3].
|
|
SQL_BIGINT
|
BIGINT
|
Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: –2[63] <= n <= 2[63] – 1, unsigned: 0 <= n <= 2[64] – 1)[3],[9].
|
|
SQL_BINARY
|
BINARY(n)
|
Binary data of fixed length n.[9]
|
|
SQL_VARBINARY
|
VARBINARY(n)
|
Variable length binary data of maximum length n. The maximum is set by the user.[9]
|
|
SQL_LONGVARBINARY
|
LONG VARBINARY
|
Variable length binary data. Maximum length is data source–dependent.[9]
|
|
SQL_TYPE_DATE[6]
|
DATE
|
Year, month, and day fields, conforming to the rules of the Gregorian calendar. (See Constraints of the Gregorian Calendar, later in this appendix.)
|
|
SQL_TYPE_TIME[6]
|
TIME(p)
|
Hour, minute, and second fields, with valid values for hours of 00 to 23, valid values for minutes of 00 to 59, and valid values for seconds of 00 to 61. Precision p indicates the seconds precision.
|
|
SQL_TYPE_TIMESTAMP[6]
|
TIMESTAMP(p)
|
Year, month, day, hour, minute, and second fields, with valid values as defined for the DATE and TIME data types.
|
|
SQL_TYPE_UTCDATETIME
|
UTCDATETIME
|
Year, month, day, hour, minute, second, utchour, and utcminute fields. The utchour and utcminute fields have 1/10 microsecond precision.
|
|
SQL_TYPE_UTCTIME
|
UTCTIME
|
Hour, minute, second, utchour, and utcminute fields. The utchour and utcminute fields have 1/10 microsecond precision..
|
|
SQL_INTERVAL_MONTH[7]
|
INTERVAL MONTH(p)
|
Number of months between two dates; p is the interval leading precision.
|
|
SQL_INTERVAL_YEAR[7]
|
INTERVAL YEAR(p)
|
Number of years between two dates; p is the interval leading precision.
|
|
SQL_INTERVAL_YEAR_TO_MONTH[7]
|
INTERVAL YEAR(p) TO MONTH
|
Number of years and months between two dates; p is the interval leading precision.
|
|
SQL_INTERVAL_DAY[7]
|
INTERVAL DAY(p)
|
Number of days between two dates; p is the interval leading precision.
|
|
SQL_INTERVAL_HOUR[7]
|
INTERVAL HOUR(p)
|
Number of hours between two date/times; p is the interval leading precision.
|
|
SQL_INTERVAL_MINUTE[7]
|
INTERVAL MINUTE(p)
|
Number of minutes between two date/times; p is the interval leading precision.
|
|
SQL_INTERVAL_SECOND[7]
|
INTERVAL SECOND(p,q)
|
Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
|
|
SQL_INTERVAL_DAY_TO_HOUR[7]
|
INTERVAL DAY(p) TO HOUR
|
Number of days/hours between two date/times; p is the interval leading precision.
|
|
SQL_INTERVAL_DAY_TO_MINUTE[7]
|
INTERVAL DAY(p) TO MINUTE
|
Number of days/hours/minutes between two date/times; p is the interval leading precision.
|
|
SQL_INTERVAL_DAY_TO_SECOND[7]
|
INTERVAL DAY(p) TO SECOND(q)
|
Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
|
|
SQL_INTERVAL_HOUR_TO_MINUTE[7]
|
INTERVAL HOUR(p) TO MINUTE
|
Number of hours/minutes between two date/times; p is the interval leading precision.
|
|
SQL_INTERVAL_HOUR_TO_SECOND[7]
|
INTERVAL HOUR(p) TO SECOND(q)
|
Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
|
|
SQL_INTERVAL_MINUTE_TO_SECOND[7]
|
INTERVAL MINUTE(p) TO SECOND(q)
|
Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
|
|
SQL_GUID
|
GUID
|
Fixed length GUID.
|
[1] This is the value returned in the DATA_TYPE column by a call to SQLGetTypeInfo.
[2] This is the value returned in the NAME and CREATE PARAMS column by a call to SQLGetTypeInfo. The NAME column returns the designation—for example, CHAR—whereas the CREATE PARAMS column returns a comma-separated list of creation parameters such as precision, scale, and length.
[3] An application uses SQLGetTypeInfo or SQLColAttribute to determine whether a particular data type or a particular column in a result set is unsigned.
[4] SQL_DECIMAL and SQL_NUMERIC data types differ only in their precision. The precision of a DECIMAL(p,s) is an implementation-defined decimal precision that is no less than p, whereas the precision of a NUMERIC(p,s) is exactly equal to p.
[5] Depending on the implementation, the precision of SQL_FLOAT can be either 24 or 53: if it is 24, the SQL_FLOAT data type is the same as SQL_REAL; if it is 53, the SQL_FLOAT data type is the same as SQL_DOUBLE.
[6] In ODBC 3.x, the SQL date, time, and timestamp data types are SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP, respectively; in ODBC 2.x, the data types are SQL_DATE, SQL_TIME, and SQL_TIMESTAMP.
[7] For more information about the interval SQL data types, see the Interval Data Types section, later in this appendix.
[8] The SQL_BIT data type has different characteristics than the BIT type in SQL-92.
[9] This data type has no corresponding data type in SQL-92.
This section provides the following example.