FrontBase Documentation

Backtrack:   Welcome!     5. SQL 92       5.3. FrontBase Datatypes

Updated: 20-Nov-2000 prev — next Table of Contents

5.3.1. SQL 92 Datatypes

FrontBase implements all datatypes defined by the SQL 92 standard:

SMALLINT, INTEGER, INT
DECIMAL, NUMERIC, FLOAT
REAL, DOUBLE PRECISION
CHARACTER, CHAR
NATIONAL CHARACTER, NATIONAL CHAR, NCHAR
CHARACTER VARYING, CHAR VARYING, VARCHAR
NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING
BIT, BIT VARYING
DATE, TIME, INTERVAL
TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIMEZONE

Each type is explained below with an example of defining a column of the type.

 

SMALLINT

Implemented as a 16-bit integer.

Example:

CREATE TABLE T0(C0 SMALLINT, ...);

INTEGER, INT

Implemented as a 32-bit integer. Apart from the obvious use, this datatype is often used for single column PRIMARY KEYs. If you are using EOF, you may want to look into using EOF's auto-generated primary keys and the BYTE type as EOF can then generate keys without a database access. The trade off is that the 12-byte primary keys thus generated are unintelligable, while a 32-bit integer is pretty simple.

Example:

CREATE TABLE T0(C0 INTEGER PRIMARY KEY, ...);

DECIMAL [ (<precision> [, <scale>] ) ]

Implemented as a 128-bit integer + 32 bits to hold sign and exponent. Default value for <precision> is 38 (the maximum) nd 0 for <scale>. This representation is identical to that of NSDecimalNumber. If you want fixed point numbers this is the datatype for it. A popular use of DECIMAL is to hold currency values.

Please note that FrontBase, by using a base 10 representation, does not lose precision. If you INSERT, for example, 1.23, this is the value that gets stored and returned, not 1.229994599 or similar. This also applies to the NUMERIC, FLOAT, REAL, and DOUBLE PRECISION datatypes.

Example:

CREATE TABLE T0(C0 DECIMAL, PROFITS DECIMAL(20,2), ...);

NUMERIC [ (<precision> [, <scale>] ) ]

Implemented as a 64-bit integer + 32 bits to hold sign and exponent. Default value for <precision> is 19 (the maximum) and 0 for <scale>. NUMERIC can be used instead of DECIMAL, reducing the storage requirement if you don't need the 38 digit precision,

Example:

CREATE TABLE T0(C0 NUMERIC, SALARY NUMERIC(10,2), ...);

FLOAT[ (<precision>) ]

Implemented as a 64-bit integer + 32 bits to hold sign and exponent. Default value for <precision> is 19 (the maximum).

Example:

CREATE TABLE T0(C0 FLOAT, C1 FLOAT(10), ...);

REAL

Implemented as a 64-bit integer + 32 bits to hold sign and exponent, with 19 digit precision. REAL and FLOAT are implemented identically, except that you can specify the maximum precision when using FLOAT.

Example:

CREATE TABLE T0(C0 REAL, ...);

DOUBLE PRECISION

Implemented as a 128-bit integer + 32 bits to hold sign and exponent, with 38 digit precision. When using EOF, this is often the best choice for mapping a NSDecimalNumber/java.math.BigDecimal.

Example:

CREATE TABLE T0(C0 DOUBLE PRECISION, ...);

CHARACTER, CHAR

Implemented as the traditional fixed length character string. Please note that FrontBase supports Unicode exclusively and stores all character strings in the UTF8 encoding. This means that character strings with values other than ASCII will occupy more bytes than the number of characters. Most non-ASCII characters occupy two bytes when encoded in UTF8 format. Encoding to and decoding from UTF8 occurs on the client side.

The maximum length of a CHARACTER value is 2GB.

Example:

CREATE TABLE T0(C0 CHAR(1), C1 CHARACTER(100000), ...);

NATIONAL CHARACTER, NATIONAL CHAR, NCHAR

As FrontBase supports Unicode exclusively, the NATIONAL CHARACTER datatype is mapped to CHARACTER.

Example:

CREATE TABLE T0(C0 NATIONAL CHAR(1), C1 NCHAR(100000), ...);

CHARACTER VARYING, CHAR VARYING, VARCHAR

Implemented as the traditional variable length character string. The implementation of variable length strings is very efficient, and there is no extra overhead associated with very long strings. Strings up to 16 bytes in length are stored directly in the row record as if they were fixed length strings. A "spelling table" is associated with each table and all identical variable length strings inserted in the rows of a table are only stored once.

Since FrontBase encodes VARCHARs very efficiently, use of variable length strings is generally recommended over use of fixed length strings.

Example:

CREATE TABLE T0(C0 VARCHAR(128), C1 CHARACTER VARYING(200000), ...);

NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NCHAR VARYING

As FrontBase supports Unicode exclusively, the NATIONAL CHARACTER VARYING datatypes are all mapped to CHARACTER VARYING.

Example:

CREATE TABLE T0(C0 NATIONAL CHAR VARYING(10),
    C1 NCHAR VARYING(10000), ...);

BIT

The bit datatype is conceptually a string of 1's and 0's but is implemented as an opaque binary datatype. For example, BIT(8) occupies one byte.

Example:

CREATE TABLE T0(C0 BIT(32), C1 BIT(256)...);

BIT VARYING

As BIT, but where bit strings can vary in length.

Example:

CREATE TABLE T0(C0 BIT VARYING(32), C1 BIT VARYING(256)...);

DATE

The traditional date datetype. Please note that DATE does not include any time components. DATE values are represented internally as seconds ('2001-01-01' is zero) and are stored as NUMERIC(0) values.

Example:

CREATE TABLE T0(C0 DATE, ...);

TIME

Holds only the time component of a complete timestamp. TIME values ('12:34:23') are represented internally as seconds and are stored as NUMERIC values. Please note that TIME values can be negative and are assumed to be expressed in the server's time zone. The server's time zone is applied to the time value when it is inserted.

Example:

CREATE TABLE T0(C0 TIME, ...);

INTERVAL

INTERVAL is actually two separate datatypes: an internal (inaccessable) datatype we'll call YEAR-MONTH INTERVAL and another internal (inaccessable) datatype we'll call DAY-TIME INTERVAL.

A YEAR-MONTH INTERVAL is represented internally as months and is stored as an INTEGER (32-bit integer).

A DAY-TIME INTERVAL is represented internally as seconds and is stored as a NUMERIC value.

One way to use intervals is when manipulating dates and timestamps. For example, to add a day or a month:

DATE '2000-01-25' + INTERVAL '02' MONTH
result: DATE '2000-03-25'

DATE '2000-02-28' + INTERVAL '02' DAY
result: DATE '2000-03-01'

Example:

CREATE TABLE T0(C0 INTERVAL YEAR TO MONTH, C1 INTERVAL MONTH, ...);

CREATE TABLE T1(D0 INTERVAL DAY TO SECOND, C1 INTERVAL HOUR, ...);

TIME WITH TIME ZONE

As TIME, except that the time zone offset is included and stored with the time values (e.g. '12:34:23-08:00'). The explicit time zone is returned to clients.

Example:

CREATE TABLE T0(C0 TIME WITH TIME ZONE, ...);

TIMESTAMP

Holds a complete timestamp value which includes both date and time components. TIMESTAMP values (e.g. '2001-01-24 12:34:23') are represented internally as seconds ('2001-01-01' is zero) and are stored as NUMERIC values. Please note that TIMESTAMP values are expressed in the server's time zone. The server's time zone is applied to the time value when it is inserted. This means that TIMESTAMP values can end up having a time zone that is different from the client!

Example:

CREATE TABLE T0(C0 TIMESTAMP, ...);

TIMESTAMP WITH TIME ZONE

As TIMESTAMP, except that the time zone offset is included and stored with the time values (e.g. '2001-01-24 12:34:23-08:00'). The explicit time zone is returned to clients. This datatype is useful if you require complete control over how time zone information is stored and displayed.

Example:

CREATE TABLE T0(C0 TIMESTAMP WITH TIME ZONE, ...);

If you have feedback or questions on this document, please send e-mail to doc-feedback@frontbase.com. Please reference the section number and topic. Thanks!!

©2000 FrontBase, Inc. All rights reserved.