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.