Re: 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(dot)klaver(at)aklaver(dot)com]
> Sent: Wednesday, 17 February 2016 1:12 AM
> To: John Van De Giessen; pgsql-odbc(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com