UniDAC

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:

  1. 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;

  2. if scale is greater than zero, the appropriate Delphi type is chosen using the following sequence of rules:
  3. .

    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:

  1. EnableBCD is True and field precision, scale is less or equal 14,4 - uses ftBCD. When using InterBaseUniProvider, set the SimpleNumericMap option to False;
  2. EnableFMTBCD is True - uses ftFMTBCD;
  3. 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.

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

See Also

© 1997-2020 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback