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?
[table].FieldByName(‘Cost’).AsDecimal := 1.111111111111111111111111111111111111111111111;
will be equal to
[table].FieldByName(‘Cost’).AsDecimal := 1.1111111111;
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:
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;
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.
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.
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.
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
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;
it wasn’t added because it has influence to performance.
update uDAFireDACDriver.pas as
procedure TDAEFireDACQuery.SetParamValues(AParams: TDAParamCollection);
{$IFDEF FIREDAC_DECIMAL_FIX}
var
i: Integer;
{$ENDIF}
begin
SetADParamValuesFromDA(AParams, TFDQuery(DataSet).Params, True);
{$IFDEF FIREDAC_DECIMAL_FIX}
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;
{$ENDIF}
end;
you shouldn’t see this issue again.
we don’t override DataAbstract_user.inc at installation of DA so this define will be applied for all future versions of DA.
