Databases - Field Types

This is one of the more frustrating parts of database design - each tool supports different field types. Because of this, it is almost impossible to convert one type of table into another.

Quick Comparison | Paradox | MS Access | SQL | Interbase / Firebird | MySQL | dBase & Foxpro | Foxpro | Delphi


Quick Comparison

SQL is "supposed" to be a standard. However, the reality is that there are many different versions. The following table only shows a "few" possibilities.

  Paradox MS Access SQL Delphi Visual Basic
Text A Text TEXT, ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR TStringField, TWideStringField String
1 Byte Int ns Number/Byte BYTE TBytesField Byte
2 Byte Int S Number/Integer SHORT TSmallIntField, TWordField Integer
4 Byte int I Number/Long Integer LONG, INT, INTEGER TIntegerField Long
8 Byte int ns ns ? TLargeIntField ?
4 Byte Float N Number/Single SINGLE, REAL ? Single
8 Byte Float ns Number/Double DOUBLE, FLOAT, NUMBER TFloatField Double
Autoincrement + AutoNumber COUNTER TAutoIncField Long
Date/Time @ Date/Time DATETIME, TIMESTAMP TDateTimeField Date
Date D ns DATE TDateField Date
Time T ns TIME TTimeField Date
Memo M Memo LONGTEXT, LONGCHAR, MEMO, NOTE TMemoField String
Formatted Memo F ns ns ? ns
Binary B ns BINARY TBlobField, TGraphicField, TBytesField, TVarBytesField ns
Boolean L Yes/No BOOLEAN TBooleanField Boolean
ns - Not Supported


Paradox

Paradox 8 help provides summaries of the fields provided by and the rules that apply to each. To get this, search help for alphanumeric fields, adding, then click Related Topics

Field names can be up to 25 characters long.

There are 17 field types in version 7/8. Search help for field types and select Paradox field types and sizes.

Type Symbol Size
Alpha A Up to 255 characters
Memo M No limit
Formatted Memo F No limit
Graphic G .BMP, .PCX, .TIF, .GIF, or .EPS, but stored in separate files in bmp format.
Binary B Raw data of any size.
Bytes Y 1 to 255 bytes of raw data.
timestamp @ Contains both Data and Time
Date D January 1, 9999 BC to December 31, 9999 AD
Time T Number of milliseconds since midnight
Autoincrement + 4 bytes, Stored as a long integer. These increment by one each time a new record is added.
Logical L Size is not specified. By default displayed as True/False.
OLE O Word document, Spreadsheet, Picture, Sound, etc.

Numbers - the help does not always include the number of bytes used.

* - the number type has 15 significant figures, but a very small range

The following blob fields are stored in a separate file (.mb).

This allows them to be any size while the table file (.db) contains only fixed size records.


MS Access 97

To get a full description of a specific field type, open a table in design mode, click in the Data Type field, and press F1.

Text Up to 255 characters
Memo Up to 64K characters, may be larger if only printable characters are included.
Number Can be any of the following
    Size Bytes Digits Range
    byte  1  2 0 to 255
    integer  2  4 +/- 32K
    long integer  4  9 +/- 2E9
    single  4  7  
    double  8 15  
    Replication ID 16 nana
Date/Time 8 bytes, years from 100 to 9999
Currency 8 bytes, 15 digits.4 digits
AutoNumber 8 bytes, Stored as a long integer. Normally, these increment by one each time a new record is added. However, the help suggest having a random number selected if replication is being used.
Yes/No 1 bit. Can be displayed as Yes/No, True/False, On/Off
OLE Object Word document, Spreadsheet, Picture, Sound, etc.
Hyperlink Up to 6K bytes
Lookup Wizard Allows a list of values to be defined where these are the only values allowed in that field. Can also define a table which contains the lookup values.


SQL

It would be nice if there was such a thing as an SQL standard that was actually followed. In SQL-89, some parts, such as referential integrity, are optional. The current standard - SQL-92 - does not cover stored procedures or Blobs.


Interbase / Firebird

Restrict object names to 31 characters, including dollar signs ($), underscores (_), 0 to 9, A to Z, and a to z. Some objects, such as constraint names, are restricted to 27 bytes in length. (from InterBase\Doc\LanguageReference.pdf)

Some phrases that are acceptable field names in MS Access, but can not be used in Interbase

These are the main field types in the Interbase 5 documentation - most are available via the Database Explorer 4.0 (comes with Delphi 5).

Type Size Description
BLOB variable  
CHAR(n) 1 to 32,767 characters Fixed length string, Alternate key word - CHARACTER
VARCHAR(n) 1 to 32,765 characters Alternate keywords: CHAR VARYING, CHARACTER VARYING.
DATE 8 bytes 1 Jan 100 to 11 Jan 5941 - Also includes time
DOUBLE PRECISION 8 bytes 1.7 * 10-308 to 1.7 * 10308 - 15 digits of precision
(Note: The size is actually platform dependent.)
FLOAT 4 bytes 3.4 * 10-38 to 3.4 * 1038 - 7 digits of precision
Quad 8 bytes Supported in Database Explorer, but not explained in the Interbase 5 help
INTEGER 4 bytes -2,147,483,648 to 2,147,483,648
SMALLINT 2 bytes -32768 to 32767
NUMERIC(precision, scale) variable Used to hold exact numbers

precision (1 to 15) specifies how many digits to store.

scale (1 to 15) (when present) specifies location of decimal point. Must be less than or equal to precision.

Example - NUMERIC(10,3) - ppppppp.sss - stores exactly 10 digits

DECIMAL(precision, scale) variable Secifies the minimum precision desired, but more is ok

precision (1 to 15) specifies the minimum number of digits to store.

scale (1 to 15) (when present) specifies location of decimal point. Must be less than or equal to precision.

Example - DECIMAL(10,3) - ppppppp.sss - stores at least 10 digits

Supported by Database Desktop, Not supported in Database Explorer.

The Database Explorer supplied with Delphi 5 also supports CSTRING and QUAD which are not listed in the Interbase 5.0 help files. (CSTRING is documented with respect to User Defined Functions.)

DECIMAL is listed in the Interbase 5.0 help files, but it is not supported in Database Explorer. It is similar to, but not the same as NUMERIC.

Interbase also supports arrays of datatypes (except BLOBs). Use square brackets to indicate the number of elements. The first element is one (1).

Autoincrement fields can be created by using Generators with triggers.

Database Desktop and Database Explorer allow slightly different field types - for instance SHORT vs SMALLINT.

See Whether to store string in BLOB, or CHAR, or VARCHAR ? for details on how these fields are stored and what the trade offs are.

The Interbase DataPump is a freeware program that reads and converts any ADO/BDE/ODBC source to an Interbase / Firebird database.


MySQL

Among other things, MySQL does not support

mysql.com
The MySQL Mailing List Archive


dBase & Foxpro

dBase and Foxpro tables are similar. However, since I don't actually have either of these products, I have tried to determine the supported field types using Database Explorer and Database Desktop (both distributed with Delphi 5) and the information available on the web. Guess what - the data does not match.

The Database Desktop supported fields were obtained by using it to create a new table and simply inspecting the available field types.

Database Explorer did not let me create new tables - as a result, I simply opened existing tables and looked at what was available.

dBase for windows
Database Explorer
Character Float Numeric Date Logical Memo OLE Binary
Database Desktop
Character Float Number Date Logical Memo OLE Binary
dBase IV
Database Explorer
Not available
Database Desktop
Character Float Number Date Logical Memo
Foxpro
Database Explorer
AutoInc Character Float Double Numeric Date Logical Memo OLE Binary DateTime Long
Database Desktop
Character Float Numeric Date Logical Memo OLE
MySQL field-types to Dbase field-types


Foxpro

Type Size Description
Character 1 to 254  
Currency 8 bytes 18 significant figures, including 4 decimal places
Not available in Database Explorer
Date 8 bytes January 1st, 1 A.D to December 31st, 9999 A.D
DateTime 8 bytes  
Logical 1 byte  
Variant   Can hold any type of field
Double 8 bytes +/-4.94065645841247E-324 to +/-8.9884656743115E307
13 significant figures
This range does not make sense
Numeric 1 to 20 bytes - .9999999999E+19 to .9999999999E+20
up to 10 significant figures (specified by the user), same as Float
Float 1 to 20 bytes - .9999999999E+19 to .9999999999E+20
up to 10 significant figures (specified by the user), same as Numeric
Integer 4 bytes -2,147,483,647 to 2,147,483,647
Integer (Autoinc) 4 bytes Autoincrement field
General 4 bytes in table Reference to an OLE object
The type is OLE in Database Explorer
Memo 4 bytes in table Values may be modified depending on the language mapping
Database Explorer indicates that these are actually 10 bytes each
Memo (Binary) 4 bytes in table Values are not modified When the code page changes

Foxpro supports several types of blob fields - General, Memo, Memo (Binary) - in each case, only 4 bytes are stored in the table and the blob is stored in another file.

Conversion programs

Reference


Delphi 5.0

All supported field types are decendents of TField (search the help for TField).


References

Delphi BDE - more driver issues (12-28-97) explains how the BDE converts between Paradox, Oracle, and dBase field types. It also provides the field naming rules.


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / Databases / FieldTypes.html