Re: Issue with Money field using ODBC to Access database

Hi Adrian,

Thank you very much for your time and effort in looking at this. I will send
this on to the list as you suggest.

My concern is that using the ODBC with Access 97 gives us very little
control of what can actually happen.
We are not given a choice on how data types are mapped from PostgreSQL to
Access. A money field in PG will become a double field when linked in Access
97 and we have no way that I know of the make this any different.
I am also not aware of any way that we can structure a query in access that
will satisfy the requirement of item 12. that you have noted below. I do not
believe that it is possible in Access to do this using the ODBC.

That leaves us with one of two conclusions. Either the changes in psqlODBC
09.05.0100 that break this are intentional and therefore the PG ODBC can no
longer be used with Access if money fields are used, or there is a bug in
the ODBC and needs to be fixed.

Our other option is to avoid using the money data type altogether and
instead use the Numeric data type.
Numeric(15,4) maps in Access as a double but doesn't cause the same error
that using the money field does. However, when using this you get a "Two
users are attempting to changes the data at the same time" error when trying
to update data in a recordset query. (Note: This error also occurs in the
older versions of the ODBC driver.)
Numeric(18,4) maps as Text(18) in Access and seems to work just fine but it
would be nicer to be mapped at least to a number field of some sort. However
at the moment this seems to be our only option.

If anyone knows of a way to make a PG "Money" or "Numeric(x,x)" data type
map in Access to its "Currency" data type then we would be very pleased to
know how.

Again, thank you very much.

Regards,

John Van De Giessen
Thyme Software


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, 18 February 2016 1:56 AM
To: John Van De Giessen
Cc: Neville Cook; pgsql-odbc
Subject: Re: [ODBC] Issue with Money field using ODBC to Access database

On 02/16/2016 07:41 PM, John Van De Giessen wrote:

> Hi Adrian,
>
> Thank you for the response.
>
> I have re-run the query with logging turned on for both ODBC versions
> 9.3 and 9.5
>
> The query code is identical in both cases and again, it succeeds using
> the
> 9.3 driver and fails using the 9.5 driver.
> The log file for the failed attempt using the new 9.5 driver is
> attached. I am unable to include the log file for the successful
> attempt using the 9.3 driver as it is 156MB in size.

Have to believe it is related to this:

https://odbc.postgresql.org/docs/release.html
"
psqlODBC 09.05.0100 Release

12. Send datatype information for query parameters, when known If a query
parameter is bound with a specific SQL type, pass on that information to the
server. This makes the behaviour of queries like "SELECT '555' > ?" more
sensible, where the result depends on whether the query parameter is
interpreted as an integer or a string.
"


As the error shows up after the ResolveOneParam section of the log. It seems
to me the parameter is being set as a float. This will need confirmation
from someone with more knowledge of the internals then I though. I have NOT
Cced the list, as I am not sure how far you want the information in the log
to reach, so you might to send a condensed version of the above to the list
for more eyes to see.


>
> I have looked inside both of the log files but they are so different
> that I can't find a basis to compare them on.
>
> Many thanks,
>
> John Van De Giessen
> Thyme Software
>
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Wednesday, 17 February 2016 1:12 AM
> To: John Van De Giessen; pgsql-odbc@postgresql.org
> Cc: Neville Cook
> Subject: Re: [ODBC] Issue with Money field using ODBC to Access
> database
>
> On 02/11/2016 03:10 PM, John Van De Giessen wrote:
>> Hello,
>>
>> We are using Postgres 9.5 and ODBC ANSI driver version 9.05.01.00
>>
>> We are receiving the following error message:
>>
>> ODBC-update on a linked table 'tblMembers' failed
>>
>> ERROR: operator does not exist: money = double precision;
>>
>> Error while executing the query
>
> That is correct as far as Postgres is concerned:
>
> test=# select version();
>                                                              version
>
> ----------------------------------------------------------------------
> ------
> -------------------------------------------------
>    PostgreSQL 9.5.1 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
> 4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
> (1 row)
>
> test=# select 1.24::money = 1.25::float;
> ERROR:  operator does not exist: money = double precision LINE 1:
> select 1.24::money = 1.25::float;
>                              ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
>>
>> This occurs when we attempt to Update a recordset to a table that
>> contains a Money field using either:
>>
>> 1) Dao from within Delphi , or
>>
>> 2) >From within Access97 itself
>>
>> Note that we are not actually updating the Money field or referring
>> to it in any way in the query; We are updating a Text field but the
>> table happens to contain a Money field. (However, the same error
>> occurs if we do attempt to update the money field)
>>
>> The PostgreSQL Money field maps to a Double in Access.
>>
>> Note: _This is NOT an issue when using the 9.03.xx ODBC driver_; In
>> that case it also maps as a double and the same query/recordset
>> update, works perfectly fine.
>
> Have you turned on logging in both cases to see what the difference is?
>
>>
>> Regards,
>>
>> John Van De Giessen
>>
>> Thyme software
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com