Data Types

This topic describes in what way server data types are mapped to the Delphi field types and demonstrates common approaches for working with large data types.

The table below represents the server data types mapped to certain Delphi field types by default. There are several options that change the default mapping. These changes are reflected in the table as footnotes.

Delphi Type	Oracle Types	SQL Server Types	MySQL Types [1]	InterBase Types	PostgreSQL Types	SQLite Types	ODBC Types	DB2 Types	Access Types	Advantage Types	SAP Sybase ASE Types	NexusDB
ftSmallint	NUMBER(p, 0)[2] (p < 5)	SMALLINT	TINYINT(M) (M > 1) SMALLINT	SMALLINT	SMALLINT	TINYINT SMALLINT	SQL_SMALLINT	SMALLINT	SMALLINT	SHORT	SMALLINT	SHORTINT, SMALLINT
ftWord	-	TINYINT	TINYINT(M) UNSIGNED (M > 1) SMALLINT UNSIGNED YEAR	-	-	-	SQL_TINYINT	-	BYTE	-	TINYINT	WORD, BYTE, TINYINT
ftInteger	NUMBER(p, 0)[2] (4 < p < 10)	INT	MEDIUMINT MEDIUMINT UNSIGNED INT	INTEGER	INTEGER	INTEGER INT	SQL_INTEGER	INTEGER	INTEGER	INTEGER	INT	INTEGER, AUTOINC, RECREV
ftLargeint	NUMBER(p, 0)[2] (9 < p < 19)	BIGINT	BIT INT UNSIGNED BIGINT BIGINT UNSIGNED	BIGINT	BIGINT	BIGINT	SQL_BIGINT	BIGINT	-	-	BIGINT	LARGEINT, DWORD
ftFloat	NUMBER(p, s)[2] BINARY FLOAT(FLOAT) BINARY DOUBLE	DECIMAL(p, s)[3] FLOAT REAL	DECIMAL(p, s)[3] FLOAT DOUBLE	NUMBER(p, s)[3] FLOAT DOUBLE PRECISION	DECIMAL[3] REAL DOUBLE PRECISION	DECIMAL(p, s)[3] FLOAT DOUBLE PRECISION	SQL_DECIMAL(p, s) SQL_NUMERIC(p, s) SQL_REAL SQL_FLOAT SQL_DOUBLE	DECIMAL(p, s) REAL DOUBLE	DECIMAL(p, s) DOUBLE	DECIMAL(p, s) DOUBLE CURDOUBLE MONEY	DECIMAL(p, s)[3] FLOAT REAL MONEY SMALLMONEY	FLOAT, DOUBLE PRECISION, EXTENDED
ftBCD	NUMBER(p, s)[2] (p < 15) and (s < 5)	DECIMAL(p, s)[3] (p < 15) and (s < 5)	DECIMAL(p, s)[3] (p < 15) and (s < 5)	DECIMAL(p, s)[3] (p < 15) and (s < 5)	DECIMAL[3]	DECIMAL[3]	SQL_DECIMAL SQL_NUMERIC	DECIMAL	DECIMAL	DECIMAL CURDOUBLE MONEY	DECIMAL[3] MONEY SMALLMONEY	DECIMAL
ftFMTBcd	NUMBER(p, s)[2] (14 < p < 39) and> (4 < s < 39)	DECIMAL(p, s) (14 < p < 39) and (4 < s < 39)	DECIMAL(p, s)[3] (14 < p < 39) and (4 < s < 39)	DECIMAL(p, s)[3] (14 < p < 19) and (4 < s < 19)	DECIMAL[3]	DECIMAL[3]	SQL_DECIMAL SQL_NUMERIC	DECIMAL	DECIMAL	DECIMAL CURDOUBLE MONEY	DECIMAL[3] MONEY SMALLMONEY	-
ftCurrency	-	MONEY SMALLMONEY	-	-	MONEY	MONEY	-	-	-	-	-	MONEY
ftBoolean	-	BIT	TINYINT[4] BOOL[4] BOOLEAN[4]	BOOLEAN	BOOLEAN	BOOLEAN	SQL_BIT	-	BOOLEAN	LOGICAL	BIT	BOOLEAN
ftString	VARCHAR2 NVARCHAR2 VARCHAR CHAR NCHAR RAW[5] INTERVAL DAY TO SECOND INTERVAL DAY TO MONTH ROWID UROWID	CHAR VARCHAR	CHAR VARCHAR ENUM SET BINARY[6] VARBINARY[6]	CHAR VARCHAR	CHAR VARCHAR	CHAR VARCHAR	SQL_CHAR SQL_VARCHAR	CHAR VARCHAR	TEXT	CHAR CICHAR VARCHAR	CHAR VARCHAR NCHAR NVARCHAR	VARCHAR, NULLSTRING, SHORTSTRING, CHAR, SINGLECHAR
ftWideString	See note [7]	NCHAR NVARCHAR	See note [7]	See note [7]	See note [7]	See note [7]	SQL_WCHAR SQL_WVARCHAR Aslo See note [7]	GRAPHIC VARGRAPHIC Also See note [7]	See note [7]	See note [7]	UNICHAR UNIVARCHAR Aslo See note [7]	NSINGLECHAR, NCHAR, NVARCHAR
ftMemo	LONG Also see note [8]	TEXT NTEXT[9]	TINYTEXT TEXT MEDIUMTEXT LONGTEXT	BLOB TEXT	TEXT	TEXT CLOB	SQL_LONGVARCHAR	LONG VARCHAR CLOB	MEMO	MEMO	TEXT	TEXT CLOB
ftWideMemo	See note[10]	NTEXT[11]	See note[10]	See note[10]	See note[10]	See note[10]	SQL_WLONGVARCHAR See note[10]	LONG VARGRAPHIC DBCLOB See note[10]	See note[10]	See note[10]	UNITEXT Also See note[10]	NCLOB
ftOraClob	CLOB NCLOB	-	-	-	-	-	-	-	-	-	-	NCLOB
ftBlob	LONG RAW	IMAGE	TINYBLOB BLOB MEDIUMBLOB LONGBLOB Spatial Data Types	BLOB BINARY	BYTEA	BLOB	SQL_LONGVARBINARY	LONG VARCHAR FOR BIT DATA BLOB	-	BLOB	IMAGE	BLOB, IMAGE
ftOraBlob	BLOB	-	-	-	LARGE OBJECT	-	-	-	-	-	-	-
ftBytes	-	BINARY TIMESTAMP	BINARY	-	-	-	SQL_BINARY	CHAR FOR BIT DATA	-	RAW	BINARY	BYTEARRAY
ftVarBytes	RAW	VARBINARY	VARBINARY	CHAR VARCHAR (CHARSET = OCTETS)	-	BINARY VARBINARY	SQL_VARBINARY	VARCHAR FOR BIT DATA	-	VARBINARY	VARBINARY	-
ftDate	-	-	DATE	DATE	DATE	DATE	SQL_TYPE_DATE	DATE	-	DATE	-	DATE
ftDateTime	DATE	DATE	DATETIME	TIMESTAMP	TIMESTAMP	TIMESTAMP DATETIME	SQL_TYPE_TIMESTAMP	TIMESTAMP	DATE	TIMESTAMP	DATE	DATETIME
ftTime	-	-	TIME	TIME	TIME	TIME	SQL_TYPE_TIME	TIME	-	TIME	-	TIME
ftTimeStamp	TIMESTAMP TIMESTAMP WITH TIMEZONE	-	-	-	-	-	-	-	-	-	-	-
ftCursor	REF CURSOR	-	-	-	REFCURSOR	-	-	-	-	-	-	-
ftGuid	-	UNIQUEIDENTIFIER	-	-	-	-	-	-	-	-	-	GUID
ftVariant	-	SQL_VARIANT	-	-	-	-	-	-	-	-	-	-

NOT SUPPORTED	BFILE OBJECT XML	CURSOR XML TABLE	-	-	-	-	SQL_TYPE_UTCDATETIME SQL_TYPE_UTCTIME SQL_INTERVAL SQL_GUID	-	-	-	-	-

[1] - If the FieldsAsString option is True, all fields except BLOB and TEXT fields are mapped to ftString

[2] - The Oracle provider maps the NUMBER data type with different precision and scale to certain Delphi types depending on the provider options in the following way:

if scale equals zero, provider checks values of the specific options to choose the correct Delphi type in the following order:
1.1 field precision is less or equal PrecisionSmallint (default is 4) - uses ftSmallint;
1.2 field precision is less or equal PrecisionInteger (default is 9) - uses ftInteger;
1.3 field precision is less or equal PrecisionLargeInt (default is 18) - uses ftLargeint;
if scale is greater than zero, the appropriate Delphi type is chosen using the following sequence of rules:

2.1 field precision is less or equal PrecisionFloat (default is 0) - uses ftFloat;
2.2 EnableBCD is True and field precision, scale is less or equal PrecisionBCD (default is 14,4) - uses ftBCD;
2.3 EnableFMTBCD is True and field precision, scale is less or equal PrecisionFMTBCD (default is 38,38) - uses ftFMTBCD;
2.4 uses ftFloat.

[3] - The appropriate Delphi type is chosen using the following sequence of rules:

EnableBCD is True and field precision, scale is less or equal 14,4 - uses ftBCD. When using InterBaseUniProvider, set the SimpleNumericMap option to False;
EnableFMTBCD is True - uses ftFMTBCD;
uses ftFloat.

[4] - If the EnableBoolean option is True

[5] - If the RawAsString option is True

[6] - If the BinaryAsString is True

[7] - If the UseUnicode option is True, all server types mapped to ftString will be mapped to ftWideString.

[8] - If the LongStrings option is False, and the field length is greater than 255, all server types mapped to ftString will be mapped to ftMemo.

[9] - For all Delphi versions prior to BDS 2006.

[10] - If the UseUnicode option is True, in BDS 2006 and later versions all server types mapped to ftMemo will be mapped to ftWideMemo.

[11] - For BDS 2006 and higher IDE versions.

Working with large objects

Server field types used to store large objects (BLOB, LOB, TEXT, etc.) are represented in Delphi as TBlobField and TMemoField. The TWideMemoField field was added in Delphi 2006.

TBlobField is used to store binary objects.
TMemoField is used to store single-byte and multibyte character data using database character set.
TWideMemoField is used to store Unicode (UTF-16) data.

Generally there is no difference in working with these three field types in UniDAC. The Pictures and Text demos demonstrate working with datasets that contain TBlobField and TMemoField. If you want to insert a BLOB value into a table directly (without opening a dataset), please take a look at the example below. It demonstrates inserting a new record into the UniDAC_BLOB table with the TUniSQL component:

UniSQL.SQL.Text := 'INSERT INTO UniDAC_BLOB(ID, Title, Picture) VALUES (1, ''A new picture'', :BLOBValue)'
UniSQL.ParamByName('BLOBValue').LoadFromFile('World.bmp', ftBlob);
UniSQL.Execute;

If a BLOB value must be formed in you program, without using a file, and inserted into a field, you can use the LoadFromStrem method:

var
  Stream: TStringStream;
begin
  Stream := TStringStream.Create('');
  try
    Stream.WriteString('The first line' + #13#10);
    Stream.WriteString('The second line');
    UniSQL.SQL.Text := 'INSERT INTO UniDAC_Text(ID, Title, TextField) VALUES (1, ''A new text value'', :TEXTValue)';
    UniSQL.ParamByName('TEXTValue').LoadFromStream(Stream, ftMemo);
    UniSQL.Execute;
  finally
    Stream.Free;
  end;

A BLOB values can be retrieved from the server in two ways. The first way is using a SELECT query from the table containing a BLOB field:

UniQuery.SQL.Text := 'SELECT TextField FROM UniDAC_Text WHERE ID = 1';
UniQuery.Open;
(UniQuery.FieldByName('TextField') as TBlobField).SaveToFile('A_file_name');
UniQuery.Close;

The second way is to use output parameters like in the following example. Note that the query may differ depending on your database server.

UniSQL.SQL.Text := 'SELECT :TEXTValue = TextField FROM UniDAC_Text WHERE ID = 1';
UniSQL.ParamByName('TEXTValue').ParamType := ptOutput;
UniSQL.Execute;
ShowMessage(UniSQL.ParamByName('TEXTValue').AsString);

Data Types

Working with large objects

See Also