SQL Data Types

From Lianjapedia
Jump to: navigation, search

See Also

ALTER TABLE, CONSTRAINTS, CREATE TABLE, INSERT, SELECT, UPDATE

Description

The Lianja database engine recognizes the following data types.

Data Types Description
BIGINT[(p[,s])][UNSIGNED] Same as NUMERIC.
BIT Same as LOGICAL.
BOOLEAN Same as LOGICAL.
CHAR[ACTER](size)[BINARY] Fixed length character data of length size bytes. The maximum size is 254 bytes and the minimum size is 1 byte. If BINARY is specified then column values are sorted and compared in case-sensitive fashion according to their ASCII order. If a BINARY flagged column is used in an expression, the whole expression is evaluated as a BINARY value.
CURRENCY Stores double precision floating numbers corresponding to the double data type in C. The precision is fixed at 25. The scale is fixed at 4. Internal storage is 8 bytes.
DATE Internal storage size of 4 bytes. Valid date range from January 1, 1900 to January 1, 3000.
DATETIME Internal storage size of 8 bytes. Valid date range from January 1, 1900 to January 1, 3000. Valid time range from 12:00:00 AM to 11:59:59 PM.
DEC[IMAL][(p[,s])][UNSIGNED] Same as NUMERIC.
DOUBLE [PRECISION][(p[,s])][UNSIGNED] Same as NUMERIC.
FLOAT[(p[,s])][UNSIGNED] Stores double precision floating numbers corresponding to the double data type in C. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, a FLOAT is always stored internally as 8 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 25 and the scale defaults to 6. If UNSIGNED is specified, only positive values are allowed.
GENERAL Same as OBJECT.
INT[EGER][(p[,s])][UNSIGNED] Stores whole numbers from -2147483647 to +2147483647. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, an INTEGER is always stored as 4 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 11 and the scale defaults to 0. If UNSIGNED is specified, only positive values are allowed.
JSON Same as MEMO, but containing JSON data accessible by member name. (Lianja v2.0)
LOGICAL Fixed length of 1 byte. This is a Boolean value that takes .T. for True or .F. for False.
LONG VARCHAR Same as MEMO.
LONG VARBINARY Same as OBJECT.
MEDIUMINT[(p[,s])][UNSIGNED] Stores whole numbers from -2147483647 to +2147483647. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, an INTEGER is always stored as 4 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 11 and the scale defaults to 0. If UNSIGNED is specified, only positive values are allowed.
MEMO Variable length character data. Length up to 2 gigabytes, or 2 to the power 31. An 8 byte pointer is stored in the record, the data is stored in an associated file.
NUM[ERIC][(p[,s])][UNSIGNED] Stores whole numbers from -2147483647 to +2147483647 depending on p. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale affect the internal storage size. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 25 and the scale defaults to 6. If UNSIGNED is specified, only positive values are allowed.
OBJECT Variable length binary data. Length up to 2 gigabytes, or 2 to the power 31. An 8 byte pointer is stored in the record, the data is stored in an associated file.
REAL[(p[,s])][UNSIGNED] Stores single precision floating numbers corresponding to the float data type in C. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, a REAL is always stored internally as 4 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 25 and the scale defaults to 6. If UNSIGNED is specified, only positive values are allowed..
SHORT[(p[,s])][UNSIGNED] Stores whole numbers from -32768 to +32767. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, a SHORT is always stored as 2 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 6 and the scale defaults to 0. If UNSIGNED is specified, only positive values are allowed.
SMALLINT[(p[,s])][UNSIGNED] Stores whole numbers from -32768 to +32767. The precision p can range from 1 to 25. The scale s can range from 0 to 9. The precision and scale only affect display, a SMALLINT is always stored as 2 bytes. If the scale is omitted, it defaults to 0. If the precision is omitted, it defaults to 6 and the scale defaults to 0. If UNSIGNED is specified, only positive values are allowed.
TEXT Same as MEMO.
TIME An 8 character field for use to store time data in the format "HH:MM:SS".
TIMESTAMP A 19 character field for use to store timestamp data in the format

"YYYY-MM-DD HH:MM:SS".

TINYINT[(length)][UNSIGNED] Fixed length of 1 byte. Stores whole numbers from 0 to 255. Display width is 3. The (length) and UNSIGNED are included for compatibility reasons only.
VARCHAR(size) Same as MEMO, size is ignored.

Data Type Abbreviations:

Abbreviations Data Type Numeric DATA_TYPE
B TINYINT/DOUBLE -6
C CHAR/CHARACTER 1
D DATE 91
F FLOAT 8
G LONG VARBINARY/GENERAL -4
I INTEGER 2
L LOGICAL/BIT/BOOLEAN -7
M JSON/LONG VARCHAR/MEMO -4
N NUMERIC 2
P PACKED DECIMAL 0
Q QUAD 0
R REAL 7
S SHORT 5
T DATETIME 93
U UNKNOWN 0
V VAXDATE 93
Y CURRENCY 0
Z ZONED 0