Post to Decimal field error when exceed precision

#1

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?

#2

Hi,

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

Untitled
Untitled1326×499 43.2 KB

note: you have Decimal(28,10) so

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

#3

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.

#5

Hi,

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

#6

Thanks, logged as bugs://83541

#7

bugs://83541 got closed with status fixed.

#8

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?

#9

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;
#10

Hi,

Can we reopen this call. Your change fixed error in example provided but I am still experiencing error in real application with customers data. Now I replicated problem again in standalone code.

image
image800×427 9.71 KB

Weird is each record on its own does not fail, neither if I create two copies of first record or two copies of second one. But combination of both within one update fail.

Could you investigate it, please?

Ales

#11

Hi,
I can reproduce this issue only with FireDAC and can’t with other drivers like ADO, SDAC, etc

Looks like something is wrong in FireDAC.

#12

Hi,

I have no clue where exactly but spotted something what makes sense in ‘weird’ behaviour. I checked what exact SQL is passed to server and here is result:

In example provided in previous post SQL is:

declare @p1 int
set @p1=3
exec sp_prepare @p1 output,N’@P1 decimal(30,28)’,N’INSERT INTO dbo.tblTest (
[Cost])
VALUES (@P1)’,1
select @p1
go
exec sp_execute 3,8633007117437876437782626280
go
exec sp_execute 3,95524.22202784850934763119660
go
exec sp_unprepare 3
go

and insert fail.
But when you swap inserted value then insert pass without error because statement generated is:

declare @p1 int
set @p1=3
exec sp_prepare @p1 output,N'@P1 decimal(30,22)',N'INSERT INTO dbo.tblTest (
[Cost])
 VALUES (@P1)',1
select @p1
go
exec sp_execute 3,95524.22202784850934763119660
go
declare @p1 int
set @p1=4
exec sp_prepare @p1 output,N'@P1 decimal(30,28)',N'INSERT INTO dbo.tblTest (
[Cost])
 VALUES (@P1)',1
select @p1
go
exec sp_execute 4,8633007117437876437782626280
go
exec sp_unprepare 4
go

For some reason it decided create new statement with different decimal declaration.
I assume following logic - if number of decimals within one ApplyUpdates is increased then new statement is created but if number of decimals is reduced then type is not changed but not enough digits is passed.

#13

I also tested ADO driver and you are correct - it does not fail. It does not mean it works as should.

In case of ADO there are always 2 insert statements (way it works also with FD). On other hand used data type is wrong and precision is lost.

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 money',N'INSERT INTO dbo.tblTest (
[Cost])
 VALUES (@P1)
',$0.0863
select @p1
go
exec sp_unprepare 1
go
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 money',N'INSERT INTO dbo.tblTest (
[Cost])
 VALUES (@P1)
',$190.9621
select @p1
go
exec sp_unprepare 2
go
#14

Hi,

I can suggest to update uDAFireDACDriver.pas as

procedure TDAEFireDACQuery.SetParamValues(AParams: TDAParamCollection);
var
  i: Integer;
begin
  SetADParamValuesFromDA(AParams, TFDQuery(DataSet).Params, True);
  if AParams.Count > 0 then begin
    for i := 0 to AParams.Count-1 do begin
      if aParams[i].DataType= datDecimal then begin
        TFDQuery(DataSet).Prepared := False;
        TFDQuery(DataSet).Prepared := True;
        Break;
      end;
    end;
  end;
end;

Note: it has influence to performance

#15

Thank you. This change solved problem. I rather take performance hit than software crash :slight_smile: