JET Database/Data types

From Wikibooks, open books for an open world
< JET Database
Jump to: navigation, search
JET Database

Introduction · Creating and connecting · Data types · Object names and constants · Data integrity · Security · Data definition language · Select · Insert · Update · Delete · Functions · Views and stored procedures · Manipulation tools · Integration and migration · Design tools

Quick Reference[edit]

JET type VBA type Size Values
byte, tinyint,
integer1
Byte 1 byte integers 0 to 255
smallint, short,
integer2
Integer 2 bytes integers -32,768 to 32,767
integer, int,
long, integer4
Long 4 bytes integers -2,147,483,648 to 2,147,483,647
single, real,
float4, IEEESingle
Single 4 bytes -3.402823E38 to -1.401298E-45 for negative values;

1.401298E-45 to 3.402823E38 for positive values

double, number,
double precision,
float, float8,
IEEEDouble
Double 8 bytes -1.79769313486231E308 to -4.94065645841247E-324 for negative values;

4.94065645841247E-324 to 1.79769313486232E308 for positive values

currency, money Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807
decimal, numeric 17 bytes fixed precision decimal numbers
counter, identity,
autoincrement
Long 4 bytes auto-incrementing integer for surrogate keys
bit, yesno,
logical, logical1
Boolean 1 bit 0 or 1 (may appear as 0 or -1 sometimes)
datetime,
date, time
Date 8 bytes 1 January, 100 to 31 December, 9999 with optional time information
text, varchar String <= 255 characters variable-length text strings, with Unicode support since JET 4.0
char String <= 255 characters fixed-length text strings, with Unicode support since JET 4.0
longchar, memo Memo <= 2 GB variable length text BLOB
binary,
binary varying
Binary <= 510 bytes fixed-length binary data
longbinary, image,
general, oleobject
Binary <= 2 GB variable length BLOB

Text types[edit]

There are two basic data types, plus a BLOB type, that are used to hold text values in JET databases. Prior to JET 4.0 they could only hold 8-bit text characters, but databases created with an Engine Type code of 5 or greater (i.e. JET 4.0) can also store Unicode characters.

Since JET 4.0, text values that are primarily one byte (8-bit) text characters are stored in two bytes unless otherwise specified. This increases the storage required for all text data, effectively doubling it. JET 4.0 has an additional property that can be set on text columns to compress text data to one-byte characters when it is able to. This property is set by specifying WITH COMPRESSION on each text column that requires it. When a table is created in a JET database using Microsoft Access, text columns are normally created with this setting turned on (as Unicode compression).

Fixed-length text[edit]

The char data type is used to store fixed-length text with up to 255 characters. Specifying the number of characters to store limits how big the column will be. Text values retrieved from a char column are padded with spaces, if necessary, to the size of the column.

The char data type is not available from the Access designer.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

CREATE TABLE T1 (c1 CHAR(10) WITH compression, c2 CHAR WITH compression)

Variable-length text[edit]

The varchar data type is used to store variable-length text with up to 255 characters.Text values retrieved from a varchar column are trimmed of any trailing spaces.

The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

CREATE TABLE T2 (c1 VARCHAR(10) WITH compression, c2 VARCHAR WITH compression)

Text BLOB[edit]

The longchar data type is used to store variable-length text with an unspecified number of characters, limited only by the maximum size of JET database files (2 GB – about 1 billion uncompressed Unicode characters).

Some software libraries are able to handle longchar columns as basic text columns, but others must use BLOB techniques for accessing their data. In particular, the ADO components so often used in Visual Basic, VBA and ASP applications can access longchar columns as basic text when using the JET 4.0 OLE-DB provider to access the database, but must use BLOB handling routines (GetChunk / AppendChunk) when using an ODBC connection.

The following statement creates a table with two variable-length text BLOB columns, both with Unicode compression:

CREATE TABLE T3 (tb1 longchar WITH compression, tb2 memo WITH compression)

Text pseudo-types[edit]

The text data type is a pseudo-type. When a size is specified, it is taken to be a varchar and is limited to 255 characters. When no size is specified, it is taken to be a longchar.

The following statement creates a table with a 10-character text column and a variable-length text BLOB column, both with Unicode compression:

CREATE TABLE T4 (c1 text(10) WITH compression, tb1 text WITH compression)

Numeric types[edit]

There are several basic numeric data types available in JET, allowing for a wide range of data storage possibilities for numeric values.

Byte[edit]

The byte data type is an 8-bit unsigned integer that can hold values between 0 and 255 inclusive. It can be referred to by the following aliases: byte, tinyint, integer1.

It is analogous with the Visual Basic type Byte.

Smallint[edit]

The smallint data type is a 16-bit (2-byte) signed integer that can hold values between -32,768 and 32,767 inclusive. It can be referred to by the following aliases: smallint, short, integer2.

It is analogous with the Visual Basic type Integer.

Integer[edit]

The integer data type is a 32-bit (4-byte) signed integer that can hold values between -2,147,483,648 and 2,147,483,647 inclusive. It can be referred to by the following aliases: integer, int, long, integer4.

It is analogous with the Visual Basic type Long.

Single[edit]

The single data type is a 32-bit (4-byte) single precision floating point number that can hold values from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. It can be referred to by the following aliases: single, real, float4, IEEESingle.

It is analogous with the Visual Basic type Single.

Double[edit]

The double data type is a 64-bit (8-byte) double precision floating point number that can hold values from -1.79769313486231E308 to -4.94065645841247E-324 for negative values, and 4.94065645841247E-324 to 1.79769313486232E308 for positive values. It can be referred to by the following aliases: double, double precision, number, float, float8, IEEEDouble.

It is analogous with the Visual Basic type Double.

Currency[edit]

The currency data type is a 64-bit (8-byte) exact precision decimal number (implemented as a scaled integer) that can hold values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 inclusive. It can be referred to by the following aliases: currency, money.

It is analogous with the Visual Basic type Currency.

Decimal[edit]

The decimal data type is a 17-byte exact precision decimal number that can hold values from -1028-1 to 1028-1. It can be referred to by the following aliases: decimal, dec, numeric.

Boolean (Yes/No)[edit]

JET supports a data type called bit that can hold one bit of information, used for holding simple Boolean states of true or false. It can only hold the values 0 or 1, but some interfaces into the JET database present these as 0 and -1, or No and Yes. It can be referred to by the following aliases: bit, logical, logical1, yesno.

It is analogous with the Visual Basic type Boolean.

Dates and times[edit]

The datetime data type is a 64-bit (8-byte) double precision floating point number that can hold both date and time information, in the range 1 January, 100 to 31 December, 9999 and with optional times in hours, minutes, seconds. It can be referred to by the following aliases: datetime, date, time, all capable of holding just date, just time, and both date and time information.

It is analogous with the Visual Basic type Date.

To determine whether a datetime holds time information, check to see whether the double precision number is the same as the integer truncation of that number; if they differ, then there is time information, otherwise there is no time information.

CREATE TABLE D1 (dt1 datetime)
GO
INSERT INTO D1(dt1) VALUES (#2007-02-02#)
GO
INSERT INTO D1(dt1) VALUES (#2007-02-02 16:26:29#)
GO
SELECT dt1, CDbl(dt1) - CLng(dt1) AS check_time FROM D1
GO
dt1                     check_time
----------------------- -------------------------
02/02/2007 16:26:29     -0.314942129632982
02/02/2007              0

Counters[edit]

The counter data type is an auto-incrementing 32-bit (4-byte) integer, often used for creating surrogate keys. It can be referred to by the following aliases: counter, autoincrement, identity.

A counter can take optional parameters, defining the initial value to start counting at, and the increment to add each time a new value is created. The following code shows both the default, starting at one with increment of one, and with specified start of 10 and increment of 2. Each table can have, at most, one column of type counter.

CREATE TABLE A1 (id1 counter, c1 CHAR(1))
GO
CREATE TABLE A2 (id2 counter(10, 2), c2 CHAR(1))
GO
INSERT INTO A1(c1) VALUES('x')
GO
INSERT INTO A1(c1) VALUES('y')
GO
INSERT INTO A2(c2) VALUES('x')
GO
INSERT INTO A2(c2) VALUES('y')
GO
SELECT * FROM A1
GO
SELECT * FROM A2
GO
id1         c1 
----------- ------
1           x
2           y

id2         c2
----------- ------
10          x
12          y

BLOBs[edit]

JET can store binary large objects, or BLOB data, in the database directly as binary data. Accessing this data typically requires special BLOB handling functions, for example, ADO provides the functions GetChunk and AppendChunk for reading and writing BLOB data.

Binary[edit]

The binary data type is a variable-length binary object type that can hold up to 510 bytes of binary data. If the size is not specified, then the maximum size of 510 bytes is used. It can be referred to by the following aliases: binary, binary varying.

Longbinary[edit]

The longbinary data type is a variable-length binary object type with an unspecified capacity, limited only by the maximum size of JET database files (2 GB). It can be referred to by the following aliases: longbinary, general, image, oleobject.