I had the same issue with postgres and old version of FPC. It seems that this is fixed in new version of FPC (I use trunk version with Pilot Logic Code Typhon).
To reproduce error I use this query
SELECT CODE_DATABASE, CAST(1.401 as NUMERIC(8,4)) as NUM1, CAST(1.401 as NUMERIC(78,
) as NUM2
Then, if you try to fetch NUM2 with odler version on FPC you will get 10000 times bigger numbers.
It seems that this is too big number for TFloatField and because of that FPC use TBCDField but incorrectly.
Only Win64 compiled programs have that issue with postgres. I tried some other databases (MySQL and SQLite) and issue does not exists.
I use custom functions to fetch database field. This is fix for float which I used.
function FavDBGetExtended (InProtoLog, InProtoError: TFavProto; InQuery: TFavQuery; InFieldName: TFavString; InDefault: TFavFloat = 0): TFavFloat;
var MyExtended: TFavFloat;
MyVariant: Variant;
MyString: TFavString;
MyValue: TFavFloat;
// MyFieldType: TFavString;
// MyBCDField: TBCDField;
MyField: TField;
begin
MyExtended := InDefault;
try
MyField := InQuery.FieldByName(InFieldName);
MyVariant := InQuery.FieldByName(InFieldName).Value;
if FavVarIsNull(MyVariant) then begin
MyExtended :=0
end
else begin
if MyField is TFMTBCDField then begin
MyExtended := TFMTBCDField(MyField).AsFloat;
end
else if MyField is TBCDField then begin
MyExtended := TBCDField(MyField).AsFloat;
{$IFDEF WIN64}
// Date: 2014-10-24 10:00:00
// dinko - postgresql have this problem for WIN64 - every amount is bigger 10000 times
// this behaviour is visible when you have NUMERIC(18,2) type as field
// cast some query field to reproduce the problem
// I took query office balance query and replace
// cast((select sum(tran_value) from f_transaction where code_office=TAB1.code_office and code_transaction='001' and tran_day=TAB1.tran_day) as NUMERIC(12,2)) as TICKET_SPORT_RECEIVED,
// to reproduce the problem
// If you take NUMERIC(18,8) you will get TFMTBCDField
// If you take NUMERIC(12,2) you will get TBCDField
if InQuery.SQLConnection is TPQConnection then begin
// starting from CT560 FreePascal 3.1.1 Source 18-12-2015 SVN Rev 32679 this behavior is fixed
// so I do not need to devide with 10000
// I dont know how to detect correct behavior so I put variable
if FavSystem.SHOULD_I_FIX_GET_EXTENDED_BUG_BY_DEVIDE_WITH_10000 then begin
MyExtended := MyExtended / 10000;
end;
end;
// {$if FPC_FULlVERSION <= 30101}
// {$endif}
{$ENDIF}
end
else if MyField is TFloatField then begin
MyExtended := TFloatField(MyField).AsFloat;
end
else if MyField is TStringField then begin
MyExtended := FavToFloatSmart(InQuery.FieldByName(InFieldName).AsString);
end
else begin
MyExtended := TFloatField(InQuery.FieldByName(InFieldName)).Value;
end;
end;
except
on E: Exception do begin
FavFullProtoLn(InProtoLog, dldebug, 'FavDBGetExtended: ' + E.Message + '; ' + InFieldName + '; ' + InQuery.SQL.Text);
FavFullProtoLn(InProtoError, dlEmerg, 'FavDBGetExtended: ' + E.Message + '; ' + InFieldName + '; ' + InQuery.SQL.Text);
raise;
end;
end;
Result := MyExtended;
end;