Getting NUMERIC and DECIMAL as text

The new build (5.31.0.43938) of IBProvider adds the new feature — getting the NUMERIC/DECIMAL values as text.

It will help resolve the problems with reading these datatypes through linked server.

For example, Firebird (and InterBase) allows to save value 1 into column with datatype NUMERIC(4,4). Yes, it is really possible:

When you try to read this data through MSSQL linked server, you will get the error:

OLE DB provider ‘LCPI.IBProvider.5’ for linked server ‘FB4’ returned invalid data for column ‘[FB4]…[NUM].N_4_4’.

(далее…)

The support of DBTIMESTAMPOFFSET

The following example demonstrates the support of DBTIMESTAMPOFFSET datatype for non-FB4 servers.

This simple code connects to FB3 and asks IBProvider to translate ‘2023-08-09 21:53 Europe/Moscow’ string into binary presentation of TIMESTAMP WITH TIME ZONE datatype — DBTIMESTAMPOFFSET.

Provider will do it with help ICU library. (далее…)

[FB4] TIMESTAMP WITH TIME ZONE

Hello All.

I think, the development of FB4 TIMESTAMP WITH TIME ZONE support has been arrived a final stage.

Provider can process time zone regions itself and through ICU (v63.1).

By default, this datatype will be mapped on the text (WSTR) but you can map it on MSSQL datatype DBTIMESTAMPOFFSET, too.

For configuring a behavior the following properties were added:

So, please, download IBProvider Trial 5.31.0.43750 and contact us if you have any questions or comments on this feature.

Thanks.

MSSQL and TIMESTAMP WITH TIME ZONE

Hello one more time.

As I have said early, MSSQL processes TIMESTAMP WITH TIME ZONE datatype using DBTIMESTAMPOFFSET structure OLE DB.

Today I have checked it again. It is so really.

By default IBProvider publishes TIMESTAMP WITH TIME ZONE as text but you can change this behavior using dbtimestamp_with_tz_rules property. That I did in this case — I defined this property in connection string with «1» value.

PS. I think, it is the first time when MSSQL connect to FB4. Although, maybe, one of our clients have already done it 🙂

MSBuild and long file path

Hello.

If you have a problem with long file path while the compilation of project in Visual Studio, look at this trick that help me from time to time:

<DefineConstants>$(DefineConstants);V_SRC_ROOT_DIR__TESTS=$([System.IO.Path]::GetFullPath('$(IBP_RootDir)..\TestCode\'))</DefineConstants>

[System.IO.Path]::GetFullPath(‘…’) converts relative path into absolute and this operation can significantly reduce the final string of file path.

Every time when it helps me, I say ‘Hallelujah!’ 🙂

Friday news

The load test of IBProvider v5.30 with FB4 lasted more than two weeks and ended today.

No problems were detected.

Firebird v4.0.3.2950 state.

Ok. Let’s start the next tests.

MSSQL For Real Man

When I started adding the support of a new FB4 datatype TIMESTAMP WITH TIME ZONE, I did a small test with MSSQL 2008, where this datatype is presented as datetimeoffset:

create table TBL__TS_WITH_TZ
(
 COL_TS_WITH_TZ datetimeoffset(4)
);

insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-04-27 11:18:00.0000 +03:30')
insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-04-27 11:19+04:30')
insert into TBL__TS_WITH_TZ (COL_TS_WITH_TZ) VALUES ('2023-06-21 13:19:57+04:37')

It was a couple months ago.

Today it was needed to read these records through MSSQL OLE DB provider (MSOLEDBSQL) to check this data. Provider returns datetimeoffset in DBTIMESTAMPOFFSET structure.

Ok, I was thought and wrote the following utility on C++: (далее…)

FB4 and Connection Pool

Hello All.

The new example was added at IBProvider website — «Resetting the session settings before reusing a connection (C#, FB4)».

This examples demonstrates an implicit usage of the new magic FB4 feature — «ALTER SESSION RESET».

It allows to implement the true connection pool at last.

Both installers (IBProvider Trial and ADO.NET provider) were updated for including the source code of this example.

The support of DECFLOAT16 and DECFLOAT34 [FB4]

Hello All.

The new trial [5.30.0.42557] provides the support for the new FB4 datatypes — DECFLOAT16 and DECFLOAT34.

There is supported simple columns and columns with arrays.

Currently, IBProvider maps these types to string datatype (WSTR).

I think, in the future the provider will provide other, native ways for work with these datatypes.

Load test of IBP with FB4

Hello Everyone.

I have just gotten the some of results about load test IBP with FB4 those can be analyzed and compared.

In short, the situation is the same as for FB3.

Test with using built-in Firebird client

[20.05.2023 23:36:21] [info] Provider DLL    :lcpi.ibprovider-v5_vc17_w64_prof_i.dll
[20.05.2023 23:36:21] [info] Provider Version:5.29.0.42372
[20.05.2023 23:36:21] [info] Server Name     :Firebird
[20.05.2023 23:36:21] [info] Server Version  :4.0.3.2937
[20.05.2023 23:36:21] [info] Client Name     :LCPI.IBProvider.RemoteFB
[20.05.2023 23:36:21] [info] Client Version  :5.29.0.42372
[20.05.2023 23:36:21] [info] Database ODS    :13.0
[20.05.2023 23:36:21] [info] Database Dialect:3
...
[22.05.2023 01:26:07] [summary] ------------------------------------------- [SUMMARY INFORMATION]
[22.05.2023 01:26:07] [summary] [TESTS]
[22.05.2023 01:26:07] [summary] EXECUTED      : 738000
[22.05.2023 01:26:07] [summary] SUCCEEDED     : 737931
[22.05.2023 01:26:07] [summary] FAILED        : 12 [ 12 error(s) ]
[22.05.2023 01:26:07] [summary] WITH WARNINGS : 57 [ 89 warning(s) ]
[22.05.2023 01:26:07] [summary] 
[22.05.2023 01:26:07] [summary] - - - - - - - - - - - - - - - - - - - - - -
[22.05.2023 01:26:07] [summary] [TEST TIMES]
[22.05.2023 01:26:07] [summary] REAL          : 9261941591927 [10 day(s)17:16:34.1591927]
[22.05.2023 01:26:07] [summary] USER          : 888992968750  [1 day(s)00:41:39.2968750]
[22.05.2023 01:26:07] [summary] KERNEL        : 369377500000  [10:15:37.7500000]
[22.05.2023 01:26:07] [summary] TOTAL         : 1258370468750 [1 day(s)10:57:17.0468750]
[22.05.2023 01:26:07] 
[22.05.2023 01:26:07] [TEST HEAP] Test heap is empty
[22.05.2023 01:26:07] [TEST HEAP] Validate ... OK
[22.05.2023 01:26:07] [TEST HEAP] Destroy ... OK
[22.05.2023 01:26:07] 
[22.05.2023 01:26:07] [summary] ------------------------------------------- [PROCESS INFORMATION]
[22.05.2023 01:26:07] [summary] [VIRTUAL MEMORY]
[22.05.2023 01:26:07] [summary] 
[22.05.2023 01:26:07] [summary] PRIVATE BYTES      : 28392 KB       [27MB 744KB]
[22.05.2023 01:26:07] [summary] PEAK PRIVATE BYTES : 13077864 KB    [12GB 483MB 360KB]
[22.05.2023 01:26:07] [summary] VIRTUAL SIZE       : 4539936 KB     [4GB 337MB 544KB]
[22.05.2023 01:26:07] [summary] PAGE FAULT COUNT   : 117698046
[22.05.2023 01:26:07] [summary] 
[22.05.2023 01:26:07] [summary] - - - - - - - - - - - - - - - - - - - - - -
[22.05.2023 01:26:07] [summary] [HANDLES]
[22.05.2023 01:26:07] [summary] 
[22.05.2023 01:26:07] [summary] HANDLES            : 176
[22.05.2023 01:26:07] [summary] 
[22.05.2023 01:26:07] [summary] - - - - - - - - - - - - - - - - - - - - - -
[22.05.2023 01:26:07] [summary] TOTAL ERRORS  : 12
[22.05.2023 01:26:07] [summary] TOTAL WARNINGS: 89
[22.05.2023 01:26:07] [summary] FULL PASSES   : 1
[22.05.2023 01:26:07] [summary] GEN TESTS     : 8332072

Test with using fbclient.dll

[23.05.2023 05:14:21] [info] Provider DLL    :lcpi.ibprovider-v5_vc17_w64_prof_i.dll
[23.05.2023 05:14:21] [info] Provider Version:5.29.0.42372
[23.05.2023 05:14:21] [info] Server Name     :Firebird
[23.05.2023 05:14:21] [info] Server Version  :4.0.3.2937
[23.05.2023 05:14:21] [info] Client Name     :Firebird
[23.05.2023 05:14:21] [info] Client Version  :4.0.3.2937
[23.05.2023 05:14:21] [info] Database ODS    :13.0
[23.05.2023 05:14:21] [info] Database Dialect:3
...
[24.05.2023 07:11:58] [summary] ------------------------------------------- [SUMMARY INFORMATION]
[24.05.2023 07:11:58] [summary] [TESTS]
[24.05.2023 07:11:58] [summary] EXECUTED      : 738012
[24.05.2023 07:11:58] [summary] SUCCEEDED     : 737946
[24.05.2023 07:11:58] [summary] FAILED        : 17 [ 17 error(s) ]
[24.05.2023 07:11:58] [summary] WITH WARNINGS : 50 [ 106 warning(s) ]
[24.05.2023 07:11:58] [summary] 
[24.05.2023 07:11:58] [summary] - - - - - - - - - - - - - - - - - - - - - -
[24.05.2023 07:11:58] [summary] [TEST TIMES]
[24.05.2023 07:11:58] [summary] REAL          : 9308170696534 [10 day(s)18:33:37.0696534]
[24.05.2023 07:11:58] [summary] USER          : 1063273750000 [1 day(s)05:32:07.3750000]
[24.05.2023 07:11:58] [summary] KERNEL        : 389622968750  [10:49:22.2968750]
[24.05.2023 07:11:58] [summary] TOTAL         : 1452896718750 [1 day(s)16:21:29.6718750]
[24.05.2023 07:11:58] 
[24.05.2023 07:11:58] [TEST HEAP] Test heap is empty
[24.05.2023 07:11:58] [TEST HEAP] Validate ... OK
[24.05.2023 07:11:58] [TEST HEAP] Destroy ... OK
[24.05.2023 07:11:58] 
[24.05.2023 07:11:58] [summary] ------------------------------------------- [PROCESS INFORMATION]
[24.05.2023 07:11:58] [summary] [VIRTUAL MEMORY]
[24.05.2023 07:11:58] [summary] 
[24.05.2023 07:11:58] [summary] PRIVATE BYTES      : 476108 KB      [464MB 972KB]
[24.05.2023 07:11:58] [summary] PEAK PRIVATE BYTES : 13164124 KB    [12GB 567MB 604KB]
[24.05.2023 07:11:58] [summary] VIRTUAL SIZE       : 5006404 KB     [4GB 793MB 68KB]
[24.05.2023 07:11:58] [summary] PAGE FAULT COUNT   : 503582383
[24.05.2023 07:11:58] [summary] 
[24.05.2023 07:11:58] [summary] - - - - - - - - - - - - - - - - - - - - - -
[24.05.2023 07:11:58] [summary] [HANDLES]
[24.05.2023 07:11:58] [summary] 
[24.05.2023 07:11:58] [summary] HANDLES            : 255
[24.05.2023 07:11:58] [summary] 
[24.05.2023 07:11:58] [summary] - - - - - - - - - - - - - - - - - - - - - -
[24.05.2023 07:11:58] [summary] TOTAL ERRORS  : 17
[24.05.2023 07:11:58] [summary] TOTAL WARNINGS: 106
[24.05.2023 07:11:58] [summary] FULL PASSES   : 1
[24.05.2023 07:11:58] [summary] GEN TESTS     : 8332072

The errors in first case (built-in client) are linked with new unsupported FB4 datatypes. Will be fixed in next IBP releases.

These errors exist in second case plus the old problems of fbclient.dll with statement cancel.


In general, the current state is not so bad.