FrontBase Documentation

Backtrack:   Welcome!     6. Original Documentation

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

6.17. Data Types of FrontBase

SQL 92 offers an extensive list of datatypes all of which are supported by FrontBase. Additionally, FrontBase also supports a number of datatypes from SQL3. Although the list of datatypes seem long and maybe even confusing, don't worry, many of the names denote the same datatype (such is the work that comes from a comittee).

SQL92 dataypes:	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 TIME ZONE
Datatypes from SQL3:	BLOB	CLOB	BOOLEAN
FrontBase proprietary datatypes:	BYTE

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 max.) and 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 e.g. 1.23, this is the value that gets stored and returned, not 1.229994599 or whatever. This also applies to the NUMERIC, FLOAT, REAL, and DOUBLE PRECISION (see below) 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 max.) and 0 for <scale>. NUMERIC can be used instead of DECIMAL if you don't need the 38 digit precision (and thus reduce the storage requirement).

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 max.).

Example:

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

REAL

Implemented as a 64-bit integer + 32 bits to hold sign and exponent. Default value for <precision> is 19 (the max.). REAL and FLOAT are implemented identically, except that you can specify the max. 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. Default value for <precision> is 38 (the max.). For many purposes this is the best choice for mapping an NSDecimalNumber/java.math.BigDecimal. See the "Mapping of Foundation/Java objects into FrontBase" document for details.

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, e.g. æøåÆØÅ, when encoded into the UTF8 format, occupies two bytes.

The max. 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 into 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 it was a fixed length string). A socalled 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 in general recommend over 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 into 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, i.e. BIT(8) occupies one byte. See below as concerns EOF and BYTE.

Example:

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

BIT VARYING

As BIT, but with the obvious exception that the bit strings are variable in length.

Example:

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

BYTE

A simple wrapper for BIT, i.e. BYTE(n) is identical to BIT(n*8). This dataype is not part of the SQL92 standard, but has been introduced to better support EOF's automatic primary key generation. If you use 12-byte binary key's, EOF can automatically generate a primary key without doing a roundtrip to the database server (and thus cause a transaction to be initiated).

Example:

   CREATE TABLE T0(C0 BYTE(12), ...);

DATE

The traditional date datetype. Please note that DATE does not include any time components. DATE values are internally represented 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 internally represented as seconds and are stored as NUMERIC values. Please note that TIME values, which can be negative, are assumed to be expressed in the servers time zone, i.e. the servers time zone is applied to the time value when it is inserted.

Example:

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

TIME WITH TIME ZONE

As TIME, except that the time zone offset is included and stored with the time values ('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 the date and time components. TIMESTAMP values ('2001-01-24 12:34:23') are internally represented as seconds (2001-01-01 is zero) and are stored as NUMERIC values. Please note that TIMESTAMP values will be expressed in the servers time zone, i.e. the servers 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 ('2001-01-24 12:34:23-08:00'). The explicit time zone is returned to clients. This datatype is needed if you want to be in complete control over how time zone information is stored and displayed.

Example:

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

INTERVAL

INTERVAL is actually two separate datatypes: a datatype called year-month interval and a datatype called day-time interval.

A year-month interval is internally represented as months and is stored as a 32-bit integer.

A day-time interval is internally represented as seconds and is stored as a NUMERIC value.

One way to use intervals is when manipulating dates and timestamps, e.g. when adding a day or month:

   DATE '2000-01-25' + INTERVAL '02' MONTH (result: DATE '2000-03-25' )
      or
   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, ...);

BLOB

A Binary Large OBject is an opaque binary datatype, i.e. the bytes you store are not interpreted in any way and are returned in the same form as when inserted. FrontBase implements BLOBs very efficiently which includes streaming on the server side, i.e. no unnecessary copying. Client side streaming is planned enabled in FrontBase 2.0. A BLOB value can be up to 2 GB in size.

Example:

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

CLOB

A Character Large OBject is a datatype for very large character strings, i.e. strings that you don't want to search on and where you would like the increased efficiency compared to normal CHARACTER/VARCHAR values (which gets copied into e.g. INSERT or UPDATE SQL statements). CLOBs are implemented as efficiently as BLOBs. CLOB values are encoded in the UTF8 format with encoding and decoding taking place on the client side.

Example:

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

BOOLEAN

Implemented as an unsigned byte. Please note that SQL 92 uses three-valued logic, i.e. the possible values are FALSE (0), TRUE (1), and UNKNOWN (255).

Example:

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

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.