Possible issue with PostgreSQL decimal fields.

[MJFShark]

Hi All!

I'm having some issues when entering floating point types into PostgreSQL. This appears to affect query field editing as well as using parameters for data entry. Here's a small console mode program that gives a quick example. In this case inserting "23.82" into a Decimal(10,6) column causes it to truncate to 23, but trying different values gives a wide range of results (often incorrect ones.) Thanks for any help!

-Mark

Code: Select all

program ZeosPgDecimalTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes,
  Windows,
  Data.DB,
  ZDbcIntfs, ZConnection, ZDataset;

var
  ZConn: TZConnection;
  ZQuery: TZQuery;
begin
  try
    ZConn := TZConnection.Create(nil);
    ZConn.Protocol := 'postgresql';
    ZConn.User := 'mark';
    ZConn.Password := 'zeosRox';
    ZConn.HostName := 'myserver';
    ZConn.Database := 'testdb';
    ZConn.LibraryLocation := 'C:\U\DbClients\Postgres\32bit\libpq.dll';
    ZConn.Connect;

    if ZConn.Connected then
      Writeln('Connected');

    ZConn.ExecuteDirect('create table if not exists dectest (id bigint primary key, mydecimal decimal(10,6))');
    ZConn.ExecuteDirect('delete from dectest');

    ZQuery := TZQuery.Create(nil);
    ZQuery.Connection := ZConn;

    // Note that we have to double the :: on := to escape it from the parser.
    ZQuery.SQL.Text := 'select * from dectest';

    ZQuery.Open;

    ZQuery.Insert;
    ZQuery.Fields[0].Value := 1;
   // It doesn't seem to matter how we enter it...
    // ZQuery.Fields[1].Value := 23.82;   // truncates to 23
    // ZQuery.Fields[1].Value := '23.82';    // truncates to 23
    ZQuery.Fields[1].AsFloat := 23.82;   // truncates to 23

    Writeln('Input value: 23.82');

    ZQuery.Post;

    ZQuery.Close;

    ZQuery.SQL.Text := 'select mydecimal from dectest where id = 1';
    ZQuery.Open;
    while not ZQuery.Eof do
    begin
      Writeln('Output value: ' + ZQuery.Fields[0].AsString);
      ZQuery.Next;
    end;

    Writeln('Done');

    ZConn.Disconnect;
    ZQuery.Free;
    ZConn.Free;

  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Write('Press Enter to quit...');
  ReadLn;

end.

[marsupilami]

Hello Mark,

hmm - I seem to remember that PostgreSQL doesn't convert from float to decimal automatically and that Egonhugeist implemented a fix where the number gets sent to the database as a string. Mabe there is an error there. What happens if you use ZQuery.Fields[1].AsBCD := 23.82?
Sorry - but I cannot test that myself right now...

With best regards,

Jan

[MJFShark]

Hi Jan,

Thanks. I've now tested using .AsBCD as well as just an .AsString (with the same results, truncated values) and it appears that all roads eventually lead through the BCD2PGNumeric procedure. I'm not savvy enough with BCD or Pg's internal format to debug it (and it's easily possible that I'm looking in the wrong place.)

-Mark

[MJFShark]

Figured it out where the issue is! BCD2PGNumeric fails on any value where the number of decimal places is not 0 or a multiple of 4 digits. The result is a truncation to that "level" so 1.12 becomes 1, 1.123456 becomes 1.1234, etc.

1 (or any integer): Works!
1.1: Fails (returns 1)
1.12: Fails (returns 1)
1.123: Fails (returns 1)
1.1234: Works!
1.12345: Fails (returns 1.1234)
etc.
1.12345678: Works!
1.123456789: Fails
etc.
1.123456789012 Works!
and so on.

Thanks!

-Mark

[MJFShark]

I think I fixed it. In procedure BCD2PGNumeric change the following line:

From:

Code: Select all

  NBASEDigit := (PWords - Dst - 8) shr 1; //calc count of nbase digits

To:

Code: Select all

  NBASEDigit := Max((PWords - Dst - 8), 1); //calc count of nbase digits
  NumBasedDigits := NBASEDigit;

In my test program which generates thousands of random BCDs, encodes them to pg and then decodes them I now see no errors. Any feedback appreciated!

[update!] The returned size is incorrect as well. I think someone more savvy on this routine needs to take a look, but I fixed it be changing:

From:

Code: Select all

  Size := (pWords - Dst); //return size in bytes

To:

Code: Select all

  Size := (4 + NumBasedDigits) * SizeOf(Word); // Size in bytes.

-Mark

[marsupilami]

Hello Mark,

I checked your changes into trunk / master. Currently I am waiting for the test results.

Best regards,

Jan

[marsupilami]

Hello Mark,

your change makes three tests fail:
dbc.TZGenericTestDbcResultSet.TestDbcBCDValues
dbc.TZGenericTestDbcArrayBindings.TestArrayBindings
component.TZGenericTestDataSet.TestAssignDBRTLParams

The error message is in german unfortunately. Basically it says something like >invalid figure in external "numeric" value<.

Could you please have a look at these?

Best regards,

Jan

[MJFShark]

Hi Jan,

Thanks! I've now realized that the only reason my changes seemed to work is because memory was zeroed before the pgnumeric was filled in, as soon as I filled it with something else instead my tests all failed!

However I may have actually fixed things (instead of my non-fix from above lol.) The change below seems to fix all my tests. I'm not setup to run the "real" zeos tests however. Note that the changes I suggested previously should not be applied, just adding the "Inc(PWords, SizeOf(Word));" should do it.

Code: Select all

  {$IFNDEF ENDIAN_BIG}
  if (FactorIndexOrScale <> 0) then begin
    NBASEDigit := PWord(pWords)^;
    PWord(pWords)^ := (NBASEDigit and $00FF shl 8) or (NBASEDigit and $FF00 shr 8);
    Inc(PWords, SizeOf(Word));   // This is what I added.
  end;
  {$ENDIF !ENDIAN_BIG}

-Mark

[marsupilami]

Hello Mark,

I applied the change and it doesn't raise any errors. So I merged it back into Zeos 8.0.

Best regards,

Jan