dbExpress Data Type Mapping for Supported Databases
Go Up to Developing Database Applications for the Win32 Platform Index
Data types of the various supported databases map to dbExpress data types. Where applicable, dbExpress provides:
- Consistent data type mapping across databases.
- Logical data types mapped to dbExpress types.
Data Types Usage
dbExpress includes a wide range of logical data types. dbExpress supports logical data type mappings for Adaptive Server Anywhere, DB2, Informix, InterBase, MS SQL, MSDE, MYSQL, Oracle, and Sybase.
The mapping tables indicate what data type you can both send to and get from a database, except as noted. These mappings will work, but other mappings may be possible. A database may map these types to a different type by default when querying a table. For example, the Geometry type by default returns a TDBXDataTypes.VarBytesType type field.
If you had a query such as this:
Select * from table
you would generally get back data of the type indicated in the mapping tables.
In some cases, you might want to specify the returned type. For instance, if you had a table with a column geometryCol that has a geometry type, you could say
Select geometryCol.ToString() from table
to get the data as a string and avoid getting the mapped data type for that data.
You could also specify the data you are sending to a database using parameters. For instance, if a table has a column intCol of type int and a column geometryCol that has a geometry type, you could use a statement like this:
insert into table (intCol, geometryCol) values (?, ?)
and then provide parameter values such as 3, 'Point(1 1)'.
InterBase
dbExpress provides data type mappings for InterBase 2020, which includes To-Go. Listed below are the data type mappings between dbExpress and InterBase 2020.
| InterBase Type | dbExpress Type |
|---|---|
| integer | TDBXDataTypes.Int32Type |
| smallint | TDBXDataTypes.Int16Type |
| blob | TDBXDataTypes.BlobType |
| blob sub_type text | TDBXDataTypes.BlobType with a subtype of TDBXSubDataTypes.MemoSubType or TDBXSubDataTypes.WideMemoSubType for a UTF-8 database |
| numeric | TDBXDataTypes.BcdType |
| decimal | TDBXDataTypes.BcdType |
| float | TDBXDataTypes.DoubleType |
| double precision | TDBXDataTypes.DoubleType |
| date | TDBXDataTypes.DateType |
| time | TDBXDataTypes.TimeType |
| timestamp | TDBXDataTypes.TimeStampType |
| boolean | TDBXDataTypes.BooleanType |
| char | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
| varchar | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
MS SQL and MSDE
Note that dbExpress supports all MS SQL 2008 data types. Below are the data type mappings for MS SQL and MSDE.
| MS SQL Type | dbExpress Type |
|---|---|
| int | TDBXDataTypes.Int32Type |
| bigint | TDBXDataTypes.Int64Type |
| smallint | TDBXDataTypes.Int16Type |
| tinyint | TDBXDataTypes.UInt8Type |
| char | TDBXDataTypes.AnsiStringType |
| varchar | TDBXDataTypes.AnsiStringType |
| text | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.MemoSubType |
| nchar | TDBXDataTypes.WideStringType |
| nvarchar | TDBXDataTypes.WideStringType |
| ntext | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.WideMemoSubType |
| geometry | TDBXDataTypes.AnsiStringType. Employ this type when using parameters. Database returns TDBXDataTypes.VarBytesType. |
| geography | TDBXDataTypes.AnsiStringType. Employ this type when using parameters. Database returns TDBXDataTypes.VarBytesType. |
| money | TDBXDataTypes.BcdType |
| smallmoney | TDBXDataTypes.BcdType |
| float | TDBXDataTypes.DoubleType |
| real | TDBXDataTypes.DoubleType |
| decimal | TDBXDataTypes.BcdType |
| numeric | TDBXDataTypes.BcdType |
| xml | TDBXDataTypes.WideStringType |
| sysname | TDBXDataTypes.WideStringType |
| date | TDBXDataTypes.DateType |
| time | TDBXDataTypes.TimeType |
| datetime | TDBXDataTypes.TimeStampType |
| datetime2 | TDBXDataTypes.TimeStampType |
| smalldatetime | TDBXDataTypes.TimeStampType |
| datetimeoffset | TDBXDataTypes.TimeStampOffsetType |
| binary | TDBXDataTypes.BytesType |
| varbinary | TDBXDataTypes.VarBytesType |
| image | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.BinarySubType |
| bit | TDBXDataTypes.BooleanType |
| hierarchyid | TDBXDataTypes.WideStringType. Use the TDBXDataTypes.WideStringType parameter, but database returns TDBXDataTypes.VarBytesType. |
| uniqueidentifier | TDBXDataTypes.WideStringType |
| sql_variant | TDBXDataTypes.VarBytesType |
MYSQL
dbExpress has the following MYSQL data type mappings:
| MYSQL Type | dbExpress Type |
|---|---|
| int | TDBXDataTypes.Int32Type |
| tinyint | TDBXDataTypes.Int8Type |
| smallint | TDBXDataTypes.Int16Type |
| mediumint | TDBXDataTypes.Int32Type |
| bigint | TDBXDataTypes.Int64Type |
| float | TDBXDataTypes.DoubleType |
| double | TDBXDataTypes.DoubleType |
| decimal | TDBXDataTypes.BcdType |
| numeric | TDBXDataTypes.BcdType |
| timestamp | TDBXDataTypes.TimeStampType |
| datetime | TDBXDataTypes.TimeStampType |
| date | TDBXDataTypes.DateType |
| time | TDBXDataTypes.TimeType |
| year | TDBXDataTypes.Int16Type |
| binary | TDBXDataTypes.AnsiStringType |
| varbinary | TDBXDataTypes.AnsiStringType |
| tinytext | TDBXDataTypes.AnsiStringType |
| text | TDBXDataTypes.AnsiStringType |
| mediumtext | TDBXDataTypes.AnsiStringType |
| longtext | TDBXDataTypes.AnsiStringType |
| tinyblob | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.BinarySubType |
| blob | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.BinarySubType |
| mediumblob | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.BinarySubType |
| longblob | TDBXDataTypes.BlobType, subtype TDBXSubDataTypes.BinarySubType |
| bit | TDBXDataTypes.VarBytesType |
| bool | TDBXDataTypes.Int8Type |
| enum | TDBXDataTypes.AnsiStringType |
| set | TDBXDataTypes.AnsiStringType |
| char | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
| varchar | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
Firebird
dbExpress now provides support for all Firebird data types. Below are the data type mappings between dbExpress and Firebird.
| Firebird Type | dbExpress Type |
|---|---|
| integer | TDBXDataTypes.Int32Type |
| bigint | TDBXDataTypes.Int64Type |
| smallint | TDBXDataTypes.Int16Type |
| char | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
| varchar | TDBXDataTypes.WideStringType if the server uses UTF-8; otherwise TDBXDataTypes.AnsiStringType |
| float | TDBXDataTypes.DoubleType |
| double | TDBXDataTypes.DoubleType |
| decimal | TDBXDataTypes.BcdType |
| numeric | TDBXDataTypes.BcdType |
| date | TDBXDataTypes.DateType |
| time | TDBXDataTypes.TimeType |
| timestamp | TDBXDataTypes.TimeStampType |
| blob | TDBXDataTypes.BlobType |
Oracle
dbExpress now provides support for all Oracle 11g data types, including the new XMLType, Binary_Float and Binary_Double. Below are the data type mappings between dbExpress and Oracle 11g.
| Oracle Type | dbExpress Type |
|---|---|
| bfile | TDBXDataTypes.BlobType |
| blob | TDBXDataTypes.BlobType |
| char | TDBXDataTypes.AnsiStringType |
| clob | TDBXDataTypes.AnsiStringType |
| date | TDBXDataTypes.TimeStampType |
| float | TDBXDataTypes.BcdType |
| binary_float | TDBXDataTypes.SingleType |
| binary_double | TDBXDataTypes.DoubleType |
| interval day to second | TDBXDataTypes.IntervalType |
| interval year to month | TDBXDataTypes.IntervalType |
| long | TDBXDataTypes.AnsiStringType |
| long raw | TDBXDataTypes.BytesType |
| nchar | TDBXDataTypes.WideStringType |
| nclob | TDBXDataTypes.WideStringType |
| number | TDBXDataTypes.BcdType |
| nvarchar2 | TDBXDataTypes.WideStringType |
| raw | TDBXDataTypes.BytesType |
| timestamp | TDBXDataTypes.TimeStampType |
| timestamp with local time zone | TDBXDataTypes.TimeStampType |
| timestamp with time zone | TDBXDataTypes.TimeStampType |
| varchar2 | TDBXDataTypes.AnsiStringType |
| xmltype | TDBXDataTypes.AnsiStringType |