Mapping SQL Server data types to Firebird data types

From LSDevLinux
Jump to: navigation, search

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: [1]

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;

The doc from Firebird on data type mapping is here: [2]. The IBPhoenix take on it is here: [3]

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_I8 bigint bigint
DBTYPE_BYTES binary blob
DBTYPE_BOOL bit char(2) character set octets (but see note above)
DBTYPE_DBTIMESTAMP datetime timestamp
DBTYPE_BYTES image blob
DBTYPE_I4 int integer
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_DBTIMESTAMP smalldatetime timestamp
DBTYPE_WSTR sysname char(n) character set UTF8 or varchar(n) character set UTF8
DBTYPE_BYTES timestamp blob
DBTYPE_UI1 tinyint char(1) character set octets
DBTYPE_GUID uniqueidentifier char(16) character set octets
DBTYPE_BYTES varbinary blob
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_ASCII_CHAR INTEGER CSTRING(1)
UDF_BIT INTEGER CHAR(2)
UDF_CHAR_TO_INT CHAR(2) INT
UDF_GUID_TO_VARCHAR CHAR(16) CHAR(36)
UDF_NEW_GUID CHAR(16)
UDF_TINY_INT INTEGER CHAR(1)
UDF_VARCHAR_TO_GUID CHAR(36) CHAR(16)

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?).