SQL & C Data Types
来源:互联网 发布:域名购买后备案 编辑:程序博客网 时间:2024/06/05 11:22
From: http://msdn.microsoft.com/en-us/library/windows/desktop/ms710150%28v=vs.85%29.aspx
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 toSQLColAttribute, SQLColumns, SQLDescribeCol,SQLDescribeParam, SQLProcedureColumns, and SQLSpecialColumns.
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, seeData 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.
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 scales. (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. (SeeConstraints 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. Precisionp 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 andq 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 andq 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 andq 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 andq 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 toSQLGetTypeInfo. 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 thanp, whereas the precision of a NUMERIC(p,s) is exactly equal top.
[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.
ODBC C data types indicate the data type of C buffers used to store data in the application.
All drivers must support all C data types. This is required because all drivers must support all C types to which SQL types that they support can be converted, and all drivers support at least one character SQL type. Because the character SQL type can be converted to and from all C types, all drivers must support all C types.
The C data type is specified in the SQLBindCol and SQLGetData functions with theTargetType argument and in the SQLBindParameter function with theValueType argument. It can also be specified by calling SQLSetDescField to set the SQL_DESC_CONCISE_TYPE field of an ARD or APD, or by callingSQLSetDescRec with the Type argument (and the SubType argument if needed) and theDescriptorHandle argument set to the handle of an ARD or APD.
The following table lists valid type identifiers for the C data types. The table also lists the ODBC C data type that corresponds to each identifier and the definition of this data type.
C type identifier
ODBC C typedef
C type
SQL_C_CHAR
SQLCHAR *
unsigned char *
SQL_C_WCHAR
SQLWCHAR *
wchar_t *
SQL_C_SSHORT[j]
SQLSMALLINT
short int
SQL_C_USHORT[j]
SQLUSMALLINT
unsigned short int
SQL_C_SLONG[j]
SQLINTEGER
long int
SQL_C_ULONG[j]
SQLUINTEGER
unsigned long int
SQL_C_FLOAT
SQLREAL
float
SQL_C_DOUBLE
SQLDOUBLE, SQLFLOAT
double
SQL_C_BIT
SQLCHAR
unsigned char
SQL_C_STINYINT[j]
SQLSCHAR
signed char
SQL_C_UTINYINT[j]
SQLCHAR
unsigned char
SQL_C_SBIGINT
SQLBIGINT
_int64[h]
SQL_C_UBIGINT
SQLUBIGINT
unsigned _int64[h]
SQL_C_BINARY
SQLCHAR *
unsigned char *
SQL_C_BOOKMARK[i]
BOOKMARK
unsigned long int[d]
SQL_C_VARBOOKMARK
SQLCHAR *
unsigned char *
SQL_C_TYPE_DATE[c]
SQL_DATE_STRUCT
struct tagDATE_STRUCT { SQLSMALLINT year; SQLUSMALLINT month; SQLUSMALLINT day; } DATE_STRUCT;[a]
SQL_C_TYPE_TIME[c]
SQL_TIME_STRUCT
struct tagTIME_STRUCT { SQLUSMALLINT hour; SQLUSMALLINT minute; SQLUSMALLINT second;} TIME_STRUCT;[a]
SQL_C_TYPE_TIMESTAMP[c]
SQL_TIMESTAMP_STRUCT
struct tagTIMESTAMP_STRUCT { SQLSMALLINT year; SQLUSMALLINT month; SQLUSMALLINT day; SQLUSMALLINT hour; SQLUSMALLINT minute; SQLUSMALLINT second; SQLUINTEGER fraction;[b] } TIMESTAMP_STRUCT;[a]
SQL_C_NUMERIC
SQL_NUMERIC_STRUCT
struct tagSQL_NUMERIC_STRUCT { SQLCHAR precision; SQLSCHAR scale; SQLCHAR sign[g]; SQLCHAR val[SQL_MAX_NUMERIC_LEN];[e], [f] } SQL_NUMERIC_STRUCT;
SQL_C_GUID
SQLGUID
struct tagSQLGUID { DWORD Data1; WORD Data2; WORD Data3; BYTE Data4[8];} SQLGUID;[k]
All C interval data types
SQL_INTERVAL_STRUCT
See the C Interval Structure section, later in this appendix.
[a] The values of the year, month, day, hour, minute, and second fields in the datetime C data types must conform to the constraints of the Gregorian calendar. (SeeConstraints of the Gregorian Calendar later in this appendix.)
[b] The value of the fraction field is the number of billionths of a second and ranges from 0 through 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.
[c] In ODBC 2.x, the C date, time, and timestamp data types are SQL_C_DATE, SQL_C_TIME, and SQL_C_TIMESTAMP.
[d] ODBC 3.x applications should use SQL_C_VARBOOKMARK, not SQL_C_BOOKMARK. When an ODBC 3.x application works with an ODBC 2.x driver, the ODBC 3.x Driver Manager will map SQL_C_VARBOOKMARK to SQL_C_BOOKMARK.
[e] A number is stored in the val field of the SQL_NUMERIC_STRUCT structure as a scaled integer, in little endian mode (the leftmost byte being the least-significant byte). For example, the number 10.001 base 10, with a scale of 4, is scaled to an integer of 100010. Because this is 186AA in hexadecimal format, the value in SQL_NUMERIC_STRUCT would be "AA 86 01 00 00 ... 00", with the number of bytes defined by the SQL_MAX_NUMERIC_LEN#define.
For more information about SQL_NUMERIC_STRUCT, seeHOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT andINF: How to Use SQL_C_NUMERIC Data Type with Numeric Data.
[f] The precision and scale fields of the SQL_C_NUMERIC data type areused for input from an application and for output from the driver to the application. When the driver writes a numeric value into the SQL_NUMERIC_STRUCT, it will use its own driver-specific default as the value for the precision field, and it will use the value in the SQL_DESC_SCALE field of the application descriptor (which defaults to 0) for thescale field. An application can provide its own values for precision and scale by setting the SQL_DESC_PRECISION and SQL_DESC_SCALE fields of the application descriptor.
[g] The sign field is 1 if positive, 0 if negative.
[h] _int64 might not be supplied by some compilers.
[i] _SQL_C_BOOKMARK has been deprecated in ODBC 3.x.
[j] _SQL_C_SHORT, SQL_C_LONG, and SQL_C_TINYINT have been replaced in ODBC by signed and unsigned types: SQL_C_SSHORT and SQL_C_USHORT, SQL_C_SLONG and SQL_C_ULONG, and SQL_C_STINYINT and SQL_C_UTINYINT. An ODBC 3.x driver that should work with ODBC 2.x applications should support SQL_C_SHORT, SQL_C_LONG, and SQL_C_TINYINT, because when they are called, the Driver Manager passes them through to the driver.
[k] SQL_C_GUID can be converted only to SQL_CHAR or SQL_WCHAR.
- SQL & C Data Types
- SQL Server Data Types
- Oracle data types and Microsoft SQL Server data types
- Hibernate Data Types VS JAVA Data Types VS SQL Data Types
- Mapping SQL and Java data types
- Ternary Data Types for C Programmers
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- Data Types
- DATA TYPES
- Ry’s Objective-C Tutorial---Objective-C Data Types
- Android应用层对设备的访问权限的实现
- Python 去除代码前行号 除去左侧一个字符
- 【杨中科解惑】我该怎么选择?选择就是放弃
- 自定义chekBox 样式
- Ubuntu 环境变量
- SQL & C Data Types
- 舒迅:产品经理必读的九步法
- 数论初步之扩展欧几里德
- Android WIFI模块分析
- 增加百度权重的方法
- 冷笑话
- select 禁用,但可以传值,很好用的!
- 图片类应用如何盈利?
- Function Pointers and Callbacks in C