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: –(263) <= n <= +((263) – 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 10308).
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: –(231) <= n <= ((231) – 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 1038).
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.