mORMot Open Source

angusj

Member

Registered: 2016-11-30

Posts: 5

Problem with PostgreSQL 'money' fields

Hi.

I have PostgreSQL 9.6 installed on Windows 10 Prof.
I have installed the latest available PostgreSQL ODBC driver (psqlodbc_09_05_0400.zip) from here:
https://www.postgresql.org/ftp/odbc/versions/msi/

With Synopse mORMot, I've created a database with a table containing a 'money' field and populated the table with data.
This table with money values appears exactly as expected in the PostgreSQL tool pgAdmin III.
However, when I try to view this table with Synopse mORMot, all the money values are ZERO ...

var
  i,j: integer;
  fields: TSQLDBColumnDefineDynArray;
  rows: ISQLDBRows;
begin
  props.GetFields(table_name, fields); //this returns money fields correctly as columntype = ftCurrency 
  rows := props.Execute('SELECT * FROM ' + table_name  +';', []);
  i := 0;
  //nb: pseudocode with result[][]
  while rows.Step do
  begin 
    for j := 0 to high(fields) do
      result[i][j] := rows.ColumnString(j); //this is always wrong for 'money' fields
    inc(i);
  end;
end;

I think I've narrowed the problem to TODBCStatement.BindColumns which calls DescribeColW() to fill a DataType and I believe this returned value is incorrect for 'money' fields. Is this an PostgreSQL ODBC driver issue?

ps: if I change the money field to numeric then data is displayed correctly.
pps: I've also tried older ODBC drivers but this problem remains.
ppps: TSQLDBConnectionProperties.GetFields() does return the expected ColumnType (ftCurrency) on 'money' fields.

Last edited by angusj (2016-12-13 22:15:27)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,276

Website

Re: Problem with PostgreSQL 'money' fields

Did you try with the SynDBZeos driver, directly accessing the PostgreSQL client?

angusj

Member

Registered: 2016-11-30

Posts: 5

Re: Problem with PostgreSQL 'money' fields

Hi Arnaud.
No. I didn't think to do that. I will investigate now.
Thanks again for your fantastic software libraries.
Angus

angusj

Member

Registered: 2016-11-30

Posts: 5

Re: Problem with PostgreSQL 'money' fields

OK, I've now tried accessing the same table with money fields using ZEOSLib and I'm still seeing mostly incorrect results.
If I display money fields using IZResultSet.GetString(I) the values are displayed correctly (though in an annoying format that includes a '$').
But if I try to get the raw (un-formatted) currency values using IZResultSet.GetCurrency(I) then results are completely wrong (even though the column type is stCurrency).
It seems that I should avoid using 'money' fields in PostgreSQL and instead use numeric.