ODBC Programmer's Reference
****** SQL Data Types ******
Each DBMS defines its own SQL types. Each ODBC driver exposes only those SQL
data types that the associated DBMS defines. 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 are 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.
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 of the SQL data
types defined in this appendix. A driver's support for SQL data types depends
on the level of SQL-92 that the driver conforms to. To determine the level of
SQL-92 grammar supported by the driver, an application calls SQLGetInfo with
the SQL_SQL_CONFORMANCE information type. Furthermore, 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
commonly 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        Typical type description
                              type[2]
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 sourcedependent.[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
                                                      sourcedependent
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 sourcedependent.[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_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     Number of years and
                              MONTH                   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      Number of days/hours/
                              MINUTE                  minutes between two date/
                                                      times; p is the interval
                                                      leading precision.
SQL_INTERVAL_DAY_TO_SECOND[7] INTERVAL DAY(p) TO      Number of days/hours/
                              SECOND(q)               minutes/seconds between
                                                      two date/times; p is the
                                                      interval leading
                                                      precision and q is the
                                                      interval seconds
                                                      precision.
SQL_INTERVAL_HOUR_TO_MINUTE   INTERVAL HOUR(p) TO     Number of hours/minutes
[7]                           MINUTE                  between two date/times; p
                                                      is the interval leading
                                                      precision.
SQL_INTERVAL_HOUR_TO_SECOND   INTERVAL HOUR(p) TO     Number of hours/minutes/
[7]                           SECOND(q)               seconds between two date/
                                                      times; p is the interval
                                                      leading precision and q
                                                      is the interval seconds
                                                      precision.
SQL_INTERVAL_MINUTE_TO_SECOND INTERVAL MINUTE(p) TO   Number of minutes/seconds
[7]                           SECOND(q)               between two date/times; p
                                                      is the interval leading
                                                      precision and q is the
                                                      interval seconds
                                                      precision.
SQL_GUID                      GUID                    Fixed length Globally
                                                      Unique Identifier.
[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 designationfor example,
CHARwhile 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 if 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, while 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 on 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 Programmer's Reference
************ CC DDaattaa TTyyppeess ************
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 SSQQLLBBiinnddCCoolland SSQQLLGGeettDDaattaa functions with the
TargetType argument and in the SSQQLLBBiinnddPPaarraammeetteerr function with the ValueType
argument. It can also be specified by calling SSQQLLSSeettDDeessccFFiieelldd to set the
SQL_DESC_CONCISE_TYPE field of an ARD or APD, or by calling SSQQLLSSeettDDeessccRReecc with
the Type argument (and the SubType argument if needed) and the DescriptorHandle
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C ttyyppee iiddeennttiiffiieerr       OODDBBCC CC ttyyppeeddeeff       CC ttyyppee
SQL_C_CHAR              SQLCHAR *            unsigned char *
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_L           EN];
                                             [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     SQL_INTERVAL_STRUCT  See the "_C_ _I_n_t_e_r_v_a_l_ _S_t_r_u_c_t_u_r_e"
types                                        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. (See "_C_o_n_s_t_r_a_i_n_t_s_ _o_f_ _t_h_e_ _G_r_e_g_o_r_i_a_n_ _C_a_l_e_n_d_a_r" 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 ##ddeeffiinnee.
[f]   The precision and scale fields of the SQL_C_NUMERIC data type are never
used for input from an application, only 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 the scale 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.
