IBProvider v5.31. Support for TIMESTAMP WITH TIME ZONE [FB4] and other improvements
We have completed the third step in the process of implementing FB4 support in our OLE DB provider.
This release adds support for TIMESTAMP WITH TIME ZONE, improves support for INT128 and improves support for NUMERIC data types.
Please review the changes carefully.
At the end of the news we have prepared a special offer for you.
TIMESTAMP WITH TIME ZONE support
IBProvider v5.31 supports reading and writing data of the TIMESTAMP WITH TIME ZONE type for both simple columns/parameters and columns/parameters with arrays.
By default, data of type TIMESTAMP WITH TIME ZONE will be available as text (WSTR). This allows you to save information about regions (for example — Europe/Moscow).
You can also receive this data in a structured form. In this case, the DBTIMESTAMPOFFSET structure will be used, which can only store time zone information as an offset.
New initialization property dbtimestamp_with_tz_rules alllows to select how to represent TIMESTAMP WITH TIME ZONE.
If you are using IBProvider with MSSQL or with LCPI ADO.NET provider it makes sense to specify «dbtimestamp_with_tz_rules=1» in the connection string.
New initialization property timezones_source allows to select how to handle time zone regions.
By default, the provider will use the FB4 capabilities to handle time zone regions and will not require any additional configuration or external libraries.
You can also use ICU to handle time zone regions. To do this, you need to specify in the connection string «timezones_source=icu» and the location of the ICUIN library (see properties icuin_library/icuin_library_64). Note that ICUIN library uses ICUUC library (see icu_library/icu_library_64).
Please note that IBProvider supports DBTIMESTAMPOFFSET for all server versions and types (IB/FB), not just FB4. If you work with any version of IB or old versions of FB (FB1-FB3) and you need maximum support for this data type, specify the path of ICUIN, ICUUC libraries in the connection string.
In order to correctly handle time zones through ICU, a fresh database of time zones is required. It must be in the tzdata directory at the same level as the ICUUC (not ICUIN) library — the last thing is linked with the specific of ICU initialization. You can get ICU libraries and tzdata from the FB4 installation directory.
The corrected support of INT128
Mapping INT128 to NUMERIC now requires «int128_rules=0» in the connection string. Note that the column precision will be 38, not 39. See the updated description for the int128_rules property.
If you are working with MSSQL/ADODB/ADO.NET and need to get all valid INT128 values, specify «int128_rules=2» in the connection string.
Getting NUMERIC as text
The initialization properties numeric_i2_rules, numeric_i4_rules, numeric_i8_rules, numeric_i16_rules have a new flag (4) that allows you to receive NUMERIC/DECIMAL data as text.
If you are working with MSSQL/ADODB/ADO.NET and need to handle the entire allowed range of INT128 based NUMERIC values, specify «numeric_i16_rules=4» in the connection string.
Look at this post «Getting NUMERIC and DECIMAL as text» in our blog, too.
New connection initialization properties
Current restrictions
You cannot currently read and save data with the TIME WITH TIME ZONE type. At the metadata level, this type is supported.
Other changes
1. The sizes of columns with text (WSTR) representation of TIMESTAMP/DATE/TIME data types have been corrected:
- TIMESTAMP — 24 characters
- DATE — 10 characters
- TIME — 13 characters
See initialization properties dbtimestamp_rules, dbdate_rules, dbtime_rules.
2. Support for Windows XP was stopped. IBProvider has started using the CompareStringEx system function, which is not supported by XP.
Testing
Firebird v4.0.3.2976 (Windows, x64, SS) was used for load testing.
We also tested the work with FB4 through the linked server «MSSQL 2012» and «LCPI ADO.NET provider for OLE DB».
Additional
We have updated our components to work with FB/IB from .NET applications:
- LCPI ADO.NET Provider for OLE DB – v1.27.1
- LCPI EFCore Provider for OLE DB – v6.0.21.200
Special offer
Purchase any license with annual maintenance before the end of summer 2023 and get 4 months of additional maintenance.
If you already have a purchased license, you can use the renew link in your customer account and get an additional 20% discount. It’s high time you updated your IBProvider for professionals 🙂