Thread: numeric type and odbc from access 2000
Greetings,
I am a newcomer to postgresql and so far so good. Although some may argue
with
the use of Access in developing front-end clients, I have created one.
Everything
works well except when a table contains numeric datatype, one can not delete
rows
or update them from access.
I am using Postgress 7.01 on a SUN/solaris 8, using the postgres odbc 6.5,
and Access 2000 as the client.
The problem as I see it is when access is deleting a row it includes all the
columns within the sql statement instead of just using the primary key or
oid. That is:
a table with
col1 as integer
col2 as varchar(10)
col3 as numeric (9,3)
primary keys (col1, col2)
Access will write the sql as:
delete from "tablename" where "col1" = 1234 AND "col2" = 'text' AND "col3" =
100.200
It also seems that the problem can be duplicated within psql:
Given any table with a numeric type, if one includes the numeric column in
the delete or update statement you get:
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast
Any ideas from anyone would be appreciate it, I hate to convert my numeric
column to an integer and divide by 1000 each time in order to solve this
problem.
Thanks
shahab
From: "Shahab Asgharzadeh" <shahab@nwu.edu> > Access will write the sql as: > delete from "tablename" where "col1" = 1234 AND "col2" = 'text' AND "col3" = > 100.200 > > ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8' > You will have to retype this query using an explicit cast > It is treating "100.200" as float and can't find an '=' operator that matches numeric with float. You can handle it with where col3=100.200::numeric - Richard Huxton
At 15:47 07.02.2001 +0000, Richard Huxton wrote: >[...] It is treating "100.200" as float and can't find an '=' operator that >matches numeric with float. You can handle it with > >where col3=100.200::numeric Yes, we can, but Access cannot, I have not found out how Delphi can, either. If You Quote the values, everything is all right, too. So whose fault is it? Delphi/Access for not casting/quoting, or is it a Postgres-Bug, not recognizing 100.200 as a valid NUMERIC(6,3) :-) But most important how can we work around it from Delphi or Access? Any Ideas? Stefan
The problem can be fixed in psql with explicit casting but the windows odbc driver for postgres does not make that determination correctly so update and delete statements from access give errors when there is a float or a numeric/decimal type in any table. And unfortunately, there is no way to adjust the sql statements sent from access to the ODBC to explicitly cast datatypes (or at least I think that's true) This seems to be a major issue if one is using access as a front-end client beacuse any table with float/numeric type will not be updated or deleted. Any idea (from anyone) when there will be an ODBC fix or a fix so the numeric type don't have to be explicitly casted in postgresql. cheers, Shahab Shahab Asgharzadeh, MD Bioinformatics Director, Neurogenetics Lab Assistant Professor of Neurology, Northwestern University Pediatric Clinical Associate, University of Chicago 312-503-4737 "You must be the change you wish to see in the world." - Gandhi
I also use the ODBC driver and have noticed some things that could be
improved and some bugs
Since it's open source I was thinking of getting the source code and making
some changes myself
Has anyone done this? My knowledge of C is passable so maybe I could do
this?
The ODBC driver doesn't seem to be too well supported which is a shame
because its the only link
PG has with the Windows world if your using any MS app or developing in VB.
MC.
>The problem can be fixed in psql with explicit casting but the windows
odbc
driver for postgres does not make that determination correctly so update
and delete statements from access give errors when there is a float or a
numeric/decimal type in any table. And unfortunately, there is no way to
adjust the sql statements sent from access to the ODBC to explicitly cast
datatypes (or at least I think that's true)
This seems to be a major issue if one is using access as a front-end client
beacuse any table with float/numeric type will not be updated or deleted.
Any idea (from anyone) when there will be an ODBC fix or a fix so the
numeric type don't have to be explicitly casted in postgresql.
cheers,
Shahab
Shahab Asgharzadeh, MD
Bioinformatics Director, Neurogenetics Lab
Assistant Professor of Neurology, Northwestern University
Pediatric Clinical Associate, University of Chicago
312-503-4737
"You must be the change you wish to see in the world." - Gandhi
--
NOTICE: The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or
entity to which it is directed and may contain information that is
privileged or otherwise confidential. If you have received this electronic
mail transmission in error, please delete it from your system without
copying or forwarding it, and notify the sender of the error by reply email
or by telephone (collect), so that the sender's address records can be
corrected.
Sure. I recommend getting the current snapshot in ftp:/pub/dev because the ODBC driver has change in the past few days. > > I also use the ODBC driver and have noticed some things that could be > improved and some bugs > > Since it's open source I was thinking of getting the source code and making > some changes myself > > Has anyone done this? My knowledge of C is passable so maybe I could do > this? > > The ODBC driver doesn't seem to be too well supported which is a shame > because its the only link > > PG has with the Windows world if your using any MS app or developing in VB. > > > > MC. > > > > > > >The problem can be fixed in psql with explicit casting but the windows > odbc > driver for postgres does not make that determination correctly so update > and delete statements from access give errors when there is a float or a > numeric/decimal type in any table. And unfortunately, there is no way to > adjust the sql statements sent from access to the ODBC to explicitly cast > datatypes (or at least I think that's true) > > This seems to be a major issue if one is using access as a front-end client > beacuse any table with float/numeric type will not be updated or deleted. > > Any idea (from anyone) when there will be an ODBC fix or a fix so the > numeric type don't have to be explicitly casted in postgresql. > > cheers, > > Shahab > Shahab Asgharzadeh, MD > > Bioinformatics Director, Neurogenetics Lab > Assistant Professor of Neurology, Northwestern University > Pediatric Clinical Associate, University of Chicago > 312-503-4737 > "You must be the change you wish to see in the world." - Gandhi > > > > > > -- > > NOTICE: The information contained in this electronic mail transmission is > intended by Convergys Corporation for the use of the named individual or > entity to which it is directed and may contain information that is > privileged or otherwise confidential. If you have received this electronic > mail transmission in error, please delete it from your system without > copying or forwarding it, and notify the sender of the error by reply email > or by telephone (collect), so that the sender's address records can be > corrected. > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
martin.chantler@convergys.com wrote: > > I also use the ODBC driver and have noticed some things that could be > improved and some bugs > > Since it's open source I was thinking of getting the source code and making > some changes myself > > Has anyone done this? I've half(or more) done it. I hope I could commit it in a few days. Regards, Hiroshi Inoue