Wrong values returned for Numeric(12,3) fields
Wrong values returned for Numeric(12,3) fields
Delphi: XE2
DBXIDA: 3.1.2
Firebird: 2.5
Summary: With OptimizedNumerics=True, EnableBCD=True, and EnableLargeint=True, values in NUMERIC(12,3) fields come back 10 times larger than the value stored. This is not observed on NUMERIC(12,2) or NUMERIC(12,4) fields.
Steps:
Create a dialect 3 database and make the following domains, table and data.
CREATE DOMAIN T_N12_2 AS NUMERIC(12,2);
CREATE DOMAIN T_N12_3 AS NUMERIC(12,3);
CREATE DOMAIN T_N12_4 AS NUMERIC(12,4);
create table test (ID BigInt, Val1 T_N12_2, Val2 T_N12_3, Val3 T_N12_4);
insert into test (ID, Val1, Val2, Val3) values (1, 50, 50, 50);
In delphi, create a TSQLConnection, TSimpleDataSet, TDataSource and TDBGrid.
Write some code:
SQLCon.Close;
SQLCon.DriverName := DbxIdaDriverLoader.sBuiltinDriverName;
SQLCon.LoginPrompt := False;
SQLCon.Params.Clear;
SQLCon.Params.Add('User_Name=IC');
SQLCon.Params.Add('Password=IC');
SQLCon.Params.Add('Database=' + ExtractFilePath(Application.EXEName) + 'Test.fdb');
SQLCon.Params.Add('SQLDialect=3');
SQLCon.Params.Add('BlobSize=-1');
SQLCon.Params.Add('DevartInterBase TransIsolation=ReadCommited');
SQLCon.Params.Add('WaitOnLocks=True');
SQLCon.Params.Add('OptimizedNumerics=True');
SQLCon.Params.Add('EnableBCD=True');
SQLCon.Params.Add('EnableLargeint=True');
SQLCon.Params.Add('LongStrings=True');
SQLCon.Params.Add('UseQuoteChar=False');
SQLCon.Params.Add('FetchAll=False');
SQLCon.Params.Add('CharLength=0');
SQLCon.Params.Add('TrimFixedChar=True');
SQLCon.Params.Add('UseUnicode=True');
SQLCon.Params.Add('Charset=ASCII');
SQLCon.Params.Add('VendorLib=' + ExtractFilePath(Application.EXEName) + 'fbembed.dll');
SQLCon.Open;
Qry1.Open;
Now the grid will show the data, with Val1 being 50, Val being 50 and Val2 being 500 (incorrect).
Changing EnableBCD to false changes Val2 to 50, or changing OptimizeNumerics to False changes Val2 to 50.
DBXIDA: 3.1.2
Firebird: 2.5
Summary: With OptimizedNumerics=True, EnableBCD=True, and EnableLargeint=True, values in NUMERIC(12,3) fields come back 10 times larger than the value stored. This is not observed on NUMERIC(12,2) or NUMERIC(12,4) fields.
Steps:
Create a dialect 3 database and make the following domains, table and data.
CREATE DOMAIN T_N12_2 AS NUMERIC(12,2);
CREATE DOMAIN T_N12_3 AS NUMERIC(12,3);
CREATE DOMAIN T_N12_4 AS NUMERIC(12,4);
create table test (ID BigInt, Val1 T_N12_2, Val2 T_N12_3, Val3 T_N12_4);
insert into test (ID, Val1, Val2, Val3) values (1, 50, 50, 50);
In delphi, create a TSQLConnection, TSimpleDataSet, TDataSource and TDBGrid.
Write some code:
SQLCon.Close;
SQLCon.DriverName := DbxIdaDriverLoader.sBuiltinDriverName;
SQLCon.LoginPrompt := False;
SQLCon.Params.Clear;
SQLCon.Params.Add('User_Name=IC');
SQLCon.Params.Add('Password=IC');
SQLCon.Params.Add('Database=' + ExtractFilePath(Application.EXEName) + 'Test.fdb');
SQLCon.Params.Add('SQLDialect=3');
SQLCon.Params.Add('BlobSize=-1');
SQLCon.Params.Add('DevartInterBase TransIsolation=ReadCommited');
SQLCon.Params.Add('WaitOnLocks=True');
SQLCon.Params.Add('OptimizedNumerics=True');
SQLCon.Params.Add('EnableBCD=True');
SQLCon.Params.Add('EnableLargeint=True');
SQLCon.Params.Add('LongStrings=True');
SQLCon.Params.Add('UseQuoteChar=False');
SQLCon.Params.Add('FetchAll=False');
SQLCon.Params.Add('CharLength=0');
SQLCon.Params.Add('TrimFixedChar=True');
SQLCon.Params.Add('UseUnicode=True');
SQLCon.Params.Add('Charset=ASCII');
SQLCon.Params.Add('VendorLib=' + ExtractFilePath(Application.EXEName) + 'fbembed.dll');
SQLCon.Open;
Qry1.Open;
Now the grid will show the data, with Val1 being 50, Val being 50 and Val2 being 500 (incorrect).
Changing EnableBCD to false changes Val2 to 50, or changing OptimizeNumerics to False changes Val2 to 50.
Another test (same table as previously) shows that not all values are affected:
And here is the output (edited)
1-> 1, 1, 1
2-> 2, 2, 2
... All correct in this range
8-> 8, 8, 8
9-> 9, 9, 9
10-> 10, 100, 10
11-> 11, 110, 11
12-> 12, 120, 12
... All WRONG in this range
97-> 97, 970, 97
98-> 98, 980, 98
99-> 99, 990, 99
100-> 100, 100, 100
101-> 101, 101, 101
102-> 102, 102, 102
... All correct in this range
997-> 997, 997, 997
998-> 998, 998, 998
999-> 999, 999, 999
1000-> 1000, 10000, 1000
1001-> 1001, 10010, 1001
1002-> 1002, 10020, 1002
... All WRONG in this range
1008-> 1008, 10080, 1008
1009-> 1009, 10090, 1009
1010-> 1010, 10100, 1010
Notice that some values are retrieved correctly while others are not.
Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var
i: integer;
s: string;
begin
Memo1.Lines.Clear;
Qry2.SQL.Text := 'select * from Test';
for i := 1 to 1010 do
begin
s := Format('update test set Val1 = %0:d, Val2 = %0:d, Val3 = %0:d', [i]);
SQLCon.ExecuteDirect(s);
Qry2.Open;
s := IntToStr(i) + '-> ' + Qry2.FieldByName('Val1').AsString + ', ' + Qry2.FieldByName('Val2').AsString + ', ' + Qry2.FieldByName('Val3').AsString;
Memo1.Lines.Add(s);
Qry2.Close;
end;
end;
1-> 1, 1, 1
2-> 2, 2, 2
... All correct in this range
8-> 8, 8, 8
9-> 9, 9, 9
10-> 10, 100, 10
11-> 11, 110, 11
12-> 12, 120, 12
... All WRONG in this range
97-> 97, 970, 97
98-> 98, 980, 98
99-> 99, 990, 99
100-> 100, 100, 100
101-> 101, 101, 101
102-> 102, 102, 102
... All correct in this range
997-> 997, 997, 997
998-> 998, 998, 998
999-> 999, 999, 999
1000-> 1000, 10000, 1000
1001-> 1001, 10010, 1001
1002-> 1002, 10020, 1002
... All WRONG in this range
1008-> 1008, 10080, 1008
1009-> 1009, 10090, 1009
1010-> 1010, 10100, 1010
Notice that some values are retrieved correctly while others are not.
I think this is the problem.
In Delphi XE2 the implementation of TryStrToBcd has changed. Looks like they have added an optimization by not doing nibble calculations.
There is a bit of code like this:
Now back IBCClasses, the function DBDecimalToBcd(Value: int64; Scale: integer): TBcd; does this:
The programmer knows that the top 2 bits of SignSpecialPlaces are significant but assumes the bottom bits are '000000'.
The line of code:
needs to be changed to something like this:
Probably this needs to be changed in IBDAC as well as DBXIDA.
In Delphi XE2 the implementation of TryStrToBcd has changed. Looks like they have added an optimization by not doing nibble calculations.
There is a bit of code like this:
Code: Select all
// Because it's easier to shift bytes than nibbles,
// Always make it an even precision, add a 0 if needed
if (Pos and 1) = 1 then
begin
Inc(Bcd.Precision);
Inc(Bcd.SignSpecialPlaces);
end;
Now back IBCClasses, the function DBDecimalToBcd(Value: int64; Scale: integer): TBcd; does this:
Code: Select all
Result := StrToBcd(bcdstr);
if (StrLen >= Scale) and (Scale > 0) then begin
...
Result.SignSpecialPlaces := Result.SignSpecialPlaces or Scale;
The line of code:
Code: Select all
Result.SignSpecialPlaces := Result.SignSpecialPlaces or Scale;
Code: Select all
Result.SignSpecialPlaces := (Result.SignSpecialPlaces and $C0) or (Scale + (Result.SignSpecialPlaces and $3F));
Re: Wrong values returned for Numeric(12,3) fields
How is that new version going?
It has been 3 months - seems rather a long time to make an update to fix such a core problem, especially when the solution was handed to you.
Do you have an eta for the next version?
It has been 3 months - seems rather a long time to make an update to fix such a core problem, especially when the solution was handed to you.
Do you have an eta for the next version?
Re: Wrong values returned for Numeric(12,3) fields
Unfortunately, we have not released any new version of dbExpress driver for Interbase and Firebird so far, because we worked on the release of the new versions of DAC.
Now when the release of the new versions of DAC took place, we will release a new version of dbExpress driver for Interbase and Firebird in several weeks.
Now when the release of the new versions of DAC took place, we will release a new version of dbExpress driver for Interbase and Firebird in several weeks.