Programmer Guide : Data types : SQL data types
  
SQL data types
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.
A driver’s support is determined by the level of SQL-92 conformance. To determine which data types a driver supports, an application calls SQLGetTypeInfo. See SQLGetTypeInfo Result Set Example. For information about driver-specific SQL data types, see the driver’s documentation.
A driver also returns the SQL data types when it describes the data types of columns and parameters using the following functions:
▪SQLColAttribute
▪SQLColumns
▪SQLDescribeCol
▪SQLDescribeParam
▪SQLProcedureColumns
▪SQLSpecialColumns
For details on fields that store SQL data type values and characteristics, see Data type identifiers and descriptors.
The following table is not a comprehensive list of SQL data types, but offers commonly used names, ranges, and limits. A data source may only support some of the data types that are listed in the table and depending on your driver, the characteristics of the data types can differ form this table’s description. The table includes the description of the associated data type from SQL-92 (if applicable)
Common SQL data type names, ranges, and limits
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. [3]
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 p and scale s. (The maximum precision is driver-defined.)
(1 <= p <= 16; s <= p). [4]
SQL_NUMERIC
NUMERIC(p,s)
Signed, exact, numeric value with a precision p and scale s.
(1 <= p <= 16; 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)
solidDB® supports only signed, not unsigned, SMALLINT. [5]
SQL_INTEGER
INTEGER
Exact numeric value with precision 10 and scale 0. (signed: -231 <= n <= 231 -1, unsigned: 0 <= n <= 232 -1) solidDB® supports only signed, not unsigned, INTEGER. [5]
SQL_REAL
REAL
Signed, approximate, numeric value with a binary precision 24 (zero or absolute value 10-38 to 1038).
SQL_FLOAT
FLOAT(p)
Signed, approximate, numeric value with a binary precision of at least p. (The maximum precision is driver defined.) [6]
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.
solidDB® does not support BIT/SQL_BIT. [7]
SQL_TINYINT
TINYINT
Exact numeric value with precision 3 and scale 0 (signed:-128 <= n <= 127 unsigned: 0 <= n <= 255) solidDB® supports only signed, not unsigned, TINYINT. [5]
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)
solidDB® supports only signed, not unsigned, BIGINT. [3] [5]
SQL_BINARY
BINARY(n)
Binary data of fixed length n. [3]
SQL_VARBINARY
VARBINARY(n)
Variable length binary data of maximum length n. The maximum is set by the user. [3]
SQL_LONGVARBINARY
LONG VARBINARY
Variable length binary data. Maximum length is data source-dependent. [3]
SQL_TYPE_DATE [8]
DATE
Year, month, and day fields, conforming to the rules of the Gregorian calendar. (See Constraints of the Gregorian calendar.)
SQL_TYPE_TIME [8]
TIME(p)
Hour, minute, and second fields. Valid values for hours are 00 to 23. Valid values for minutes are 00 to 59. Valid values for seconds are 00 to 61 (60 and 61 are to handle “leap seconds” (see http://tycho.usno.navy.mil/leapsec.html). Precision p indicates the precision of the seconds field.
SQL_TYPE_TIMESTAMP [8]
TIMESTAMP(p)
Year, month, day, hour, minute, and send fields, with valid values as defined for the DATE and Time data types.
[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 - while the CREATE PARAMS column returns a comma-separated list of creation parameters such as precision, scale, and length.
[3] This data type has no corresponding data type in SQL-92.
[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, while the precision of a NUMERIC(p,s) is exactly equal to p.
[5] An application uses SQLGetTypeInfo or SQLColAttribute to determine if a particular data type or a particular column in a result set is unsigned.
[6] 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.
[7] The SQL_BIT data type has different characteristics than the BIT type in SQL-92.
[8] This data type has no corresponding data type in SQL-92.
SQLGetTypeInfo Result Set Example
Applications call SQLGetTypeInfo result set for a list of supported data types and their characteristics for a given data source.
The example below shows the data types that SQLGetTypeInfo returns for a data source; all data types under “DATA_TYPE” are supported in this data source.
Data types SQLGetTypeInfo returns (1)
TYPE_
DATA_TYPE
COLUMN_ SIZE
LITERAL_ PREFIX
LITERAL_ SUFFIX
CREATE_ PARAMS
NULLABLE
"char"
SQL_CHAR
255
„„
„„
"length"
SQL_TRUE
"text"
SQL_LONG VARCHAR
2147483647
„„
„„
<Null>
SQL_TRUE
"decimal"
SQL_DECIMAL
18 [1]
<Null>
<Null>
"precision, scale"
SQL_TRUE
"real"
SQL_REAL
7
<Null>
<Null>
<Null>
SQL_TRUE
"datetime"
SQL_TYPE_ TIMESTAMP
29 [2]
„„
„„
<Null>
SQL_TRUE
SQL_CHAR
SQL_FALSE
SQL_ SEARCHABLE
<Null>
SQL_FALSE
<Null>
"char"
SQL_LONG VARCHAR
SQL_FALSE
SQL_PRED_ CHAR
<Null>
SQL_FALSE
<Null>
"text"
SQL_DECIMAL
SQL_FALSE
SQL_PRED_ BASIC
SQL_FALSE
SQL_FALSE
SQL_FALSE
"decimal"
SQL_REAL
SQL_FALSE
SQL_PRED_ BASIC
SQL_FALSE
SQL_FALSE
SQL_FALSE
"real"
SQL_TYPE_ TIMESTAMP
SQL_FALSE
SQL_ SEARCHABLE
<Null>
SQL_FALSE
<Null>
"datetime"
SQL_CHAR
<Null>
<Null>
SQL_CHAR
<Null>
<Null>
<Null>
SQL_LONG VARCHAR
<Null>
<Null>
SQL_LONG VARCHAR
<Null>
<Null>
<Null>
SQL_DECIMAL
0
16
SQL_ DECIMAL
<Null>
10
<Null>
SQL_REAL
<Null>
<Null>
SQL_REAL
<Null>
10
<Null>
SQL_TYPE_ TIMESTAMP
3
3
SQL_ DATETIME
SQL_CODE_ TIMESTAMP
<Null>
12
[1] 16 digits, 1 decimal point, and an optional sign character for negative numbers
[2] 29 characters to display yyyy-mm-dd hh:MM:ss.nnnnnnnnn
See also
Data types