numeric type and odbc from access 2000

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