(Solved) Postgre numeric field - wrong value after update fpc from
2.6.4 to 3.0
lazarus / 16-Databases

----------------------------------------------------------------------
mith · 2017-01-23 21:25

I'm wondering if someone has (had) a problem with reading data from
postgre numeric fields. I prepared a simple table with one numeric
field. I filled it with two records: one with value 30 and second
30.0. When I use Lazarus with FPC ver. 2.6.4 the data I read from the
table is correct, but when using FPC 3.0 it gives me the values
multiplies by 10000.

Maybe it's not a bug but only lack of my competence, but I realy need
help with this.

Please find a simple demo program attached. Bellow You can find a
script to create database with demo data.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Create
database x;create user x with password 'x';grant all privileges on
database x to x;CREATE TABLE public.x(  x numeric,  id serial, 
CONSTRAINT xxx PRIMARY KEY (id))WITH (  OIDS=FALSE);ALTER TABLE
public.x OWNER TO x;INSERT INTO x (x) values (30);INSERT INTO x (x)
values (30.0);

----------------------------------------------------------------------
dinmil · 2017-01-23 21:39

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;

----------------------------------------------------------------------
mith · 2017-01-24 09:13

Thank you for your help.
Regarding the issue - you are right. It's already fixed.

http://bugs.freepascal.org/view.php?id=29760