SQL Data Types |

< > InstantSQL Components InstantSQL Data Conversion Table of Contents

InstantSQL directly supports the SQL data types commonly found in databases. These data types are denoted by constant-names defined in the copy file lisqldef.cpy. For any particular data source, it is common that the data source supports only a subset of these data types. The data type numbers supported by a particular data source can be obtained using the SQL QUERY TYPES statement, which also returns additional information about each supported data type.

In addition to the common data types that are directly supported, InstantSQL supports any driver-specific or other ODBC data type if the driver can convert the data type to character data. These data types are effectively treated by InstantSQL as if they were converted to LONGVARCHAR.

InstantSQL uses the ODBC extension level 1 function SQLGetTypeInfo to determine which data types are supported by a data source and whether the data type is unsigned. InstantSQL then uses this information to validate data types provided by the user. If the driver does not support the SQLGetTypeInfo function, InstantSQL assumes any data type provided by the user is valid and, if it is a numeric type, that it is signed. ODBC errors can occur if this assumption is incorrect.

The following table lists the SQL data types directly supported by InstantSQL.

SQL Type | Constant-Name | Description
BIGINT | sql-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).
BINARY(n) | sql-Binary | Binary data of fixed length n.
BIT | sql-Bit | An unsigned exact numeric type that may have the value 0 (commonly equivalent to false) or 1 (commonly equivalent to true).
CHAR(n) | sql-Char | Character string with a fixed length n.
DATE | sql-Date | Year, month, and day fields, conforming to the rules of the Gregorian calendar.
DECIMAL(p, s) | sql-Decimal | 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)
DOUBLE PRECISION | sql-Double | Signed, approximate numeric value with a binary precision 53 (zero or absolute value 10**–308 to 10**308).
FLOAT(p) | sql-Float | Signed, approximate, numeric value with a binary precision of at least p. (The maximum precision is driver-defined.)
INTEGER | sql-Integer | Exact numeric value with precision 10 and scale 0 (signed: –(2**31) <= n <= ((2**31) – 1), unsigned: 0 <= n <= ((2**32) – 1).
LONGVARBINARY | sql-LongVarBinary | Variable length binary data. Maximum length is data source – dependent.
LONGVARCHAR | sql-LongVarChar | Variable length character data. Maximum length is data source – dependent.
NUMERIC(p, s) | sql-Numeric | Signed, exact numeric value with a precision p and scale s. (1 <= p <= 15; s <= p)
REAL | sql-Real | Signed, approximate numeric value with a binary precision 24 (zero or absolute value 10**–38 to 10**38).
SMALLINT | sql-SmallInt | Exact numeric value with precision 5 and scale 0 (signed: –32,768 <= n <= 32,767, unsigned: 0 <= n <= 65,535).
TIME(p) | sql-Time | 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.
TIMESTAMP(p) | sql-Timestamp | Year, month, day, hour, minute, and second fields, with valid values as defined for the DATE and TIME data types.
TINYINT | sql-TinyInt | Exact numeric value with precision 3 and scale 0 (signed: –128 <= n <= 127, unsigned: 0 <= n <= 255).
VARBINARY(n) | sql-VarBinary | Variable-length binary data with a maximum length n.
VARCHAR(n) | sql-VarChar | Variable-length character string with a maximum string length n.

The SQL types listed are only typical names for the data type. The names that must be used in the SQL CREATE TABLE and ALTER TABLE statements are data source - dependent. The InstantSQL statement SQL QUERY TYPES can be used to obtain the names used by a particular data source, as well as the types supported by that data source.

Copyright ©2000 Liant Software Corp. All rights reserved.