Обсуждение: Issue with Money field using ODBC to Access database
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
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.
Regards,
John Van De Giessen
Thyme software
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
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
On 02/17/2016 03:27 PM, John Van De Giessen wrote: > 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. The above is part of the problem, Access 97 is 19 years old and falling behind every day. My guess is you will start seeing more incompatibilities crop up as you go forward. > 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.) Inherit to casting from double precision to numeric and back. There is loss of precision and Access sees this as an update, where the value it calculates for the update field does not match exactly the value stored in the database and returned to it. > 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. If you are going to stay with Access 97 I'm thinking you will need to stay with older versions of the ODBC driver. > > Again, thank you very much. > > Regards, > > John Van De Giessen > Thyme Software > -- Adrian Klaver adrian.klaver@aklaver.com
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