Mapping SQL Server data types to Firebird data types
The following table shows the mapping that has been used in porting the OleDbEncap module From SQL Server to Firebird. Only those (OLE DB) data types that were being used within the FieldWorks applications (as at Aug 2003) feature in the table.
Firebird v.2.0 introduced a new character set UTF8. From all accounts, this is a dramatic improvement over the older UNICODE_FSS. The character set supports two collations: 1) UNICODE, which supports the Unicode Collation Alogrithm (UCA), and 2) UCS_BASIC, which sorts in Unicode code point order. Most of the Firebird doc hasn't been updated for this (as of June 2007). However, an updated site on Firebird 2 character sets and collations can be found here: 
Notes on choice of individual data types:
- The Firebird manual recommends a CHAR for binaries. The team chose to use blobs: "binary can be larger than the limit imposed by CHAR and therefore one of the reasons BLOB was chosen. Given that we have to deal with BLOBs anyways this probably is no bigger burden for the developer".
- "OleDbEncap expects the caller to supply a two byte buffer when it passes or requests a DBTYPE_BOOL value. That's why we've used CHAR(2). CHARACTER SET OCTETS is used to indicate that the data is binary rather than text."
- For tinyint, "OleDbEncap expects the caller to supply a one byte buffer when it passes or requests a DBTYPE_UI1 value. That's why we've used CHAR(1). CHARACTER SET OCTETS is used to indicate that the data is binary rather than text."
- For uniqueidentifier, "OleDbEncap expects the caller to supply a sixteen byte buffer when it passes or requests a DBTYPE_GUID value. That's why we've used CHAR(16). CHARACTER SET OCTETS is used to indicate that the data is binary rather than text."
- The type for bit below is CHAR(2) CHARACTER SET OCTETS. However, I someone on the Firebird mailing list wrote that he uses the following for a boolean: CREATE DOMAIN BOOLCHAR AS Char(1) check( (value in ('T','F')) or value is null ) COLLATE NONE;
Some Firebird User Defined Functions (UDF)s have been built to support data types which MS SQL Server has, but Firebird lacks. A list of these are below.
|OLE DB data type||SQL Server data type||Firebird data type|
|DBTYPE_BOOL||bit||char(2) character set octets (but see note above)|
|DBTYPE_WSTR||nchar||char(n) character set UTF8 or varchar(n) character set UTF8|
|DBTYPE_WSTR||ntext||char(n) character set UTF8 or varchar(n) character set UTF8|
|DBTYPE_WSTR||sysname||char(n) character set UTF8 or varchar(n) character set UTF8|
|DBTYPE_UI1||tinyint||char(1) character set octets|
|DBTYPE_GUID||uniqueidentifier||char(16) character set octets|
|DBTYPE_WSTR||nvarchar||char(n) character set UTF8 or varchar(n) character set UTF8|
Note: Some of the above SQL Server data types may not be in use in FieldWorks.
Firebird UDFs for unsupported data types:
|Firebird UDF||Input Type||Result Type|
UDF_BIT is to convert integer values to a format that oledbencap will interpret correctly as a bit value and so we can do inserts into tables that have fields set as Bit (CHAR(2). CHARACTER SET OCTETS) using integer values.
There is also a UDF_TINYINT that needs to be used when dealing with tinyint values.
Counterparts to these two functions do not exist currently. It has to be verified if there is a reason why they weren't (are they really needed?).