Post to Decimal field error when exceed precision

We have problem with Decimal columns. Using SQL Server with FireDAC.

Because we need preserve full precision we use BDC type fileds. Colums in DB are defined decimal(28,10).

If value is set to field like this:

[table].FieldByName(‘Cost’).AsDecimal := 1.111111111111111111111111111111111111111111111;

then everything is working correctly and it is saved to DB formatted as expected.

Here is catch - we are scaling value. If value saved in BCD is multiplied by fraction then result can exceed precision and fail with FireDAC error.

eg.

cost := [table].FieldByName(‘Cost’).AsDecimal;
scale := 0.9999999999999999999999999999999999;
[table].FieldByName(‘Cost’).AsDecimal := cost * scale;

I tested this with TFDQuery and TFDConnection and it also fails. Fortunately both these components have option FormatOptions.CheckPrecision and FormatOptions.Round2Scale. Should not be BCD values to be formatted automatically to destination field format? Or is there any way how to set behaviour via exposed properties?

Hi,

can you specify what exception you have caught, pls?
I’ve created a simple testcase and everything works as expected.

note: you have Decimal(28,10) so

[table].FieldByName(‘Cost’).AsDecimal := 1.111111111111111111111111111111111111111111111;
will be equal to
[table].FieldByName(‘Cost’).AsDecimal := 1.1111111111;

Hi,

I’m now even more puzzled with behaviour. In my original post I just placed some random numbers because did not want replicate complete logic from our application. You are right with standalone example you have created error is not present.

Finally I replicate my problem by tweaking your code:


tbl_dbotalk20745.Append;
tbl_dbotalk20745.FieldByName(‘Cost’).AsDecimal := 1.11111111111111;
tbl_dbotalk20745.Post;
tbl_dbotalk20745.ApplyUpdates(True);
tbl_dbotalk20745.First;
cost := tbl_dbotalk20745.FieldByName(‘Cost’).AsDecimal;
scale := 0.999999999999999;
tbl_dbotalk20745.Edit;
tbl_dbotalk20745.FieldByName(‘Cost’).AsDecimal := cost * scale * scale;
tbl_dbotalk20745.Post;

tbl_dbotalk20745.ApplyUpdates(True);

Error is: [FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type decimal to decimal.

Hi,

I can confirm that 1,11111111111110777777777777778111111111111111 value was converted to decimal incorrectly.

Thanks, logged as bugs://83541

bugs://83541 got closed with status fixed.

Hi,

I see issue is fixed. Is it some simple change I can apply to code or do I need wait for next version release?

Hi,

you can update uROBinaryHelpers.pas as:

function StringToDecimal(const aString: string; aDot: Char): TDecimal;
resourcestring
  err_DecimalOverflow = 'Value was either too large or too small for a TDecimal';

function StringToDecimal(const aString: string; aDot: Char): TDecimal;
var
  scalepos, pos, i: Integer;
  mulres: Integer;
  d: Int64;
  aRes: TDecimal;
  processedDigits: Integer;
  lastdigits: Integer;
begin
  if Astring = '' then EConvertError.CreateRes(@SInvalidDecimal);
  Fillchar(aRes, sizeof(aRes), 0);
  pos := 0;
  scalepos := -1;
  processedDigits := 0;
  lastdigits:=-1;
  for i := 1 to Length(aString) do begin
    mulres := 0;
    case aString[i] of
      '0': ; // already set
      '1': mulres := 1;
      '2': mulres := 2;
      '3': mulres := 3;
      '4': mulres := 4;
      '5': mulres := 5;
      '6': mulres := 6;
      '7': mulres := 7;
      '8': mulres := 8;
      '9': mulres := 9;
    else
      if aString[i] = '-' then begin
        aRes[3] := aRes[3] or $80000000;
        continue;
      end
      else if aString[i] = aDot then begin
        if scalepos = -1 then begin
          scalepos := pos;
          if (processedDigits=0) then processedDigits :=1;
        end;
        continue;
      end
      else
        continue; // ignore invalid chars for now
    end;
    if (processedDigits=0) and (mulres=0) and (scalepos=-1) then continue;
    inc(processedDigits);
    if processedDigits > 29 then begin
      if processedDigits = 30 then
        lastdigits := mulres
      else
        if (lastdigits = 5) and (mulres>0) then lastdigits := 6;
      if (scalepos = -1) then raise EOverflow.Create(err_DecimalOverflow);
      if scalepos = -1 then begin
        Inc(pos);
      end
      else begin
        if lastdigits >5 then Break;
      end;
      continue;
    end;
    Inc(pos);

    // max number $FFFFFFFF FFFFFFFF FFFFFFFF
    // aRes[2] = max /10 = $19999999 99999999 9999999A
    if (scalepos = -1) and (processedDigits = 29) and
      (((aRes[2] > $19999999)) or
       ((aRes[2] = $19999999) and (aRes[1] >= $99999999) and (aRes[0] >= $9999999A)))then
      raise EOverflow.Create(err_DecimalOverflow);

    d := Int64(aRes[0]) * 10 + mulres;
    aRes[0] := d;
    mulres := d shr 32;
    d := Int64(aRes[1]) * 10 + mulres;
    aRes[1] := d;
    mulres := d shr 32;
    aRes[2] := Int64(aRes[2]) * 10 + mulres;
  end;
  if scalepos <> -1 then begin
    pos:= pos - scalepos;
    aRes[3] := aRes[3] or Cardinal(Pos shl 16);
  end;
{$IFDEF DELPHI10UP}{$REGION 'fix the last digit (if needed)'}{$ENDIF}
  if lastdigits>5 then begin
    d := Int64(aRes[0]) + 1;
    aRes[0] := d;
    mulres := d shr 32;
    if mulres >0 then begin
      d := Int64(aRes[1]) + mulres;
      aRes[1] := d;
      mulres := d shr 32;
      aRes[2] := Int64(aRes[2]) +mulres;
    end;
  end;
{$IFDEF DELPHI10UP}{$ENDREGION}{$ENDIF}
  Result := aRes;
end;