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

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	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

Paradox

Paradox 8 help provides summaries of the fields provided by

dBase Informix Interbase Oracle Sybase

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.

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.

Type	Symbol	Bytes	Digits	Range
short	S	2	4	+/- 32K
long integer	I	4	9	+/- 2E9
number*	N	8	15	+/- 10K
Money	$	unk		Size is not specified. Precision maintained to 6 decimal places.
BCD	#	unk	15	na

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

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

Memo FormattedMemo Graphic OLE Binary

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 na na

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

Type, Value, Password, unique, Date, Current
Database, Table, Action, Date, Active, Order

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 * 10³⁰⁸ - 15 digits of precision
(Note: The size is actually platform dependent.)
FLOAT 4 bytes 3.4 * 10^-38 to 3.4 * 10³⁸ - 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.

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 * 10³⁰⁸ - 15 digits of precision (Note: The size is actually platform dependent.)
FLOAT	4 bytes	3.4 * 10^-38 to 3.4 * 10³⁸ - 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).

CREATE TABLE TABLE1
  (NAME CHAR(10),
   CHAR_ARR CHAR(10)[4],     /* 1, 2, 3, 4   */
   INT_ARR3 INTEGER[4,5,6],  /* 3 dimensions */
   INT_ARR INTEGER[0:3]      /* 0, 1, 2, 3   */
  );

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

Each of the following integer types may be followed by UNSIGNED and/or AUTO_INCREMENT 
TINYINT   or BOOL
SMALLINT
MEDIUMINT
INT       or INTEGER
BIGINT

FLOAT
DOUBLE    or REAL      or DOUBLE PRECISION
DECIMAL   or NUMERIC

TIMESTAMP
DATE
TIME
DATETIME
YEAR

CHAR
VARCHAR
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
ENUM
SET

Among other things, MySQL does not support

Triggers	Planned for version 5.0
Stored procedures	Planned for version 5.0
Views	Planned for version 5.1
Subselects (nested Select statements)	Added to version 4.1 - in beta

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

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

dbf2mysql
dbf2sql-program version 2.2,

Reference

Delphi 5.0

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


TADTField         TDateField         TReferenceField
TAggregateField   TDateTimeField     TSmallIntField
TArrayField       TFloatField        TStringField
TAutoIncField     TGraphicField      TTimeField
TBCDField         TGuidField         TVarBytesField
TBlobField        TIDispatchField    TVariantField
TBooleanField     TIntegerField      TWideStringField
TBytesField       TLargeIntField     TWordField
TCurrencyField    TMemoField

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