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’.
For resolving this problem you can define in connection string numeric_i2_rules=4 and read the value of this column as text:
We used numeric_i2_rules because NUMERIC(4,4) is mapped in SMALLINT datatype (two bytes integer).
NUMERIC may be mapped on INTEGER (4 bytes), INT64 (8 bytes) and INT128 (16 bytes), too.
For these datatypes provider provides similar properties — numeric_i4_rules, numeric_i8_rules and numeric_i16_rules.
For NUMERIC(4,4) you can use another way for avoiding this problem — change the precision from 4 to 5. It will works, too:
The same trick can be used for INTEGER/INT64 based NUMERIC but not for NUMERIC that is based on INT128. Because precision 39 is not allowed for NUMERIC datatype.
If you try to define in connection string «numeric_i16_rules=2» you will get an error like this:
The OLE DB provider «LCPI.IBProvider.5» for linked server «FB4C» supplied invalid metadata for column «N_19_0». The precision exceeded the allowable maximum.
For NUMERIC on base INT128 getting data as text (numeric_i16_rules=4) is a single way for resolving such problems:
We should mention here the one additional property — int128_rules. It also solves similar problems.
—
Note that numeric_i16_rules and int128_rules properties will help solve not only problems with MSSQL linked server but the problems with getting data through ADODB and ADO.NET providers, too.
—
So, all these things looks strange and difficult.
But it works and may help you fix such problems with minimum efforts.