Decimal rounding problem fixed?

[tcmdvm]

I have been having the decimal rounding problem described here: http://zeos.firmos.at/viewtopic.php?t=3589.

I have been having this problem since the early betas for version 7.0. I have an early beta that didn't have the problem. I have been comparing the old code with the new code to see if I could find what was different that would cause the problem.

In unit ZDbcInterbase6Utils.pas at line 1888 I added the line as indicated:

Code: Select all

procedure TZParamsSQLDA.UpdateBigDecimal(const Index: Integer; Value: Extended);
var
  SQLCode: SmallInt;
begin
  CheckRange(Index);
  SetFieldType(Index, sizeof(Int64), SQL_INT64 + 1, -4);//added this line makes it work
  {$R-}
  with FXSQLDA.sqlvar[Index] do
  begin
    if (sqlind <> nil) and (sqlind^ = -1) then
       Exit;

    SQLCode := (sqltype and not(1));

    if (sqlscale < 0)  then
...

Adding the line of code makes the decimals work properly.

I am not a programmer so I am not sure whether this is the proper way to fix the problem or not. Maybe it will be helpful in finding a solution.

[EgonHugeist]

tcmdvm,

thanx for helping. I've no behavior changes with your patch.

Can you explain your intentions? Is your patch logical?
IMHO you change the Field-Type and Scale allways. Did you checkout 7.1 from SVN too?

[EgonHugeist]

tcmdvm,

double post ):

if no rounding issues if i use NUMERIC fields, but the same procedures (with floating scale param) where called.

I found a blog of some germans who had been running into the same issues. They wrote NUMERIC is more precise than DECIMAL. Can you confirm my findings?

[tcmdvm]

Michael,

I changed the field from DECIMAL to NUMERIC. Yes, it now works properly.

There must be a difference between DECIMAL and NUMERIC.

When I initially set up the database, I debated as to which one would be best to use and never found any good information.

I guess if you want more precision it is best to use a NUMERIC field.

Thanks for your help.
_________________
Best regards, tcmdvm

[EgonHugeist]

tcmdvm,

I guess if you want more precision it is best to use a NUMERIC field.

annoying isn't it? We've no clue how to fix that! Only idea/feeling i have: We've to know the count of decimal digits of the value we want update. Than we can overwrite the scale for each update. Any idea how to extract that?

For me the whole story is nightmare. I can't find a FB document or a "known issues" list which points me to the DECIMAL vs. NUMERIC diffs, even if the german suggest theire is one (years ago).


EDIT!!!!:

I found the reason! Not firebird was the trouble-maker, nope function Trunc(): Int64;

I've implemented a workaround:

Code: Select all

procedure TZParamsSQLDA.UpdateBigDecimal(const Index: Integer; Value: Extended);
var
  SQLCode: SmallInt;
  TempStr: string;
  TempFloat: Extended;
begin
  CheckRange(Index);

  {$R-}
  with FXSQLDA.sqlvar[Index] do
  begin
    if (sqlind <> nil) and (sqlind^ = -1) then
       Exit;

    SQLCode := (sqltype and not(1));

    if (sqlscale < 0)  then
    begin //http://code.google.com/p/fbclient/wiki/DatatypeMapping
      case SQLCode of
        SQL_SHORT  : PSmallInt(sqldata)^ := Trunc(Value * IBScaleDivisor[sqlscale]);
        SQL_LONG   : PInteger(sqldata)^  := Trunc(Value * IBScaleDivisor[sqlscale]);
        SQL_INT64,
        SQL_QUAD   : //PInt64(sqldata)^    := Trunc(Value * GetIBScaleDivisor(sqlscale)); EgonHugeist: Trunc seems to have rounding issues!
          begin
            if sqlscale > 0 then
              TempFloat := RoundTo(Value, sqlscale*-1)
            else
              TempFloat := RoundTo(Value, sqlscale);
            TempStr := FloatToStrF(TempFloat * GetIBScaleDivisor(sqlscale), ffFixed, 18, 0);
            //remain issues if decimal digits > scale than we've school learned rounding success randomly only
            //each aproach did fail: RoundTo(Value, sqlscale*-1), Round etc.
            //so the developer has to take care for this case
            PInt64(sqldata)^    := StrToInt64(TempStr);
          end;
...

But read my comments toooooo (:

Patch done R2282 \testing7.1 (SVN)