Devart Forums

Posted: **Wed 21 Mar 2012 06:47**

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.

Posted: **Wed 21 Mar 2012 13:15**

Hello,

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com .

Posted: **Wed 21 Mar 2012 20:31**

I have emailed you a test database created as per the sql I posted here and a test app containing the exact code I posted here.

Posted: **Thu 22 Mar 2012 00:21**

Another test (same table as previously) shows that not all values are affected:

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;

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.

Posted: **Thu 22 Mar 2012 03:02**

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:

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 programmer knows that the top 2 bits of SignSpecialPlaces are significant but assumes the bottom bits are '000000'.

The line of code:

Code: Select all

    Result.SignSpecialPlaces := Result.SignSpecialPlaces or Scale;

needs to be changed to something like this:

Code: Select all

    Result.SignSpecialPlaces := (Result.SignSpecialPlaces and $C0) or (Scale + (Result.SignSpecialPlaces and $3F));

Probably this needs to be changed in IBDAC as well as DBXIDA.

Posted: **Thu 22 Mar 2012 14:30**

Thank you for the information. We reproduced the problem and the investigation is in progress. We will notify you when we have any results.

Posted: **Thu 05 Apr 2012 10:30**

We have fixed this problem. This fix will be included in the next dbExpress driver for InterBase & Firebird build.

Posted: **Tue 03 Jul 2012 03:30**

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?

Posted: **Wed 04 Jul 2012 07:20**

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.

Devart Forums

Wrong values returned for Numeric(12,3) fields

Wrong values returned for Numeric(12,3) fields

Re: Wrong values returned for Numeric(12,3) fields

Re: Wrong values returned for Numeric(12,3) fields