Thanks for the response, John.
You're intuition is good ... I did some more testing and eliminated the
id_dom domain and created a table "test" with an "id" attribute defined as
int4. MS Access was able to recognize this test table via ODBC properly.
So, I've confirmed that the problem is that MS Access/ODBC doesn't recognize
the underlying int4 definition within the user-created id_dom domain.
My next challenge is to figure out how to fix this problem. How can I get
the ODBC driver and Access to handle the domain properly? The alternative
is to convert all attributes defined as a domain back to straight INTEGER
attributes. However, I'd rather get this working instead of putting a
band-aid on the symptom.
I inspected the SQL log both when connecting to "id" as type "id_dom" and as
type "int4". In the first case, the problem is illustrated by the SELECT
query that is executed by MS Access (note the single quotes and the
non-numeric values):
query='SELECT "id","unid","rid","trxtype","bilb","entjobcustid" FROM
"public"."trx" WHERE "id" = 'A' OR "id" = 'Ø' OR "id" = 'Ù' OR "id" =
'Ú' OR "id" = 'Û' OR "id" = 'Ü' OR "id" = 'Ý' OR "id" = 'Þ' OR "id" =
'ß' OR "id" = 'à'' <-- gives an error
Here's the SELECT query executed by MS Access when connecting to the "id"
field of type "int4" (note the properly formed WHERE clause that provides
actual numeric vales and no single quotes):
query='SELECT "id","name","modts","modusr","crets","creusr" FROM
"public"."test" WHERE "id" = 1 OR "id" = 2 OR "id" = 2 OR "id" = 2 OR "id"
= 2 OR "id" = 2 OR "id" = 2 OR "id" = 2 OR "id" = 2 OR "id" = 2' [ fetched 2 rows ] <-- works great
Does anyone have any ideas as to how to get the MS Access / ODBC connection
to use the proper SELECT syntax when selecting on a primary key field
defined as a domain (with the underlying type as int4)?
Thanks,
mg
-----Original Message-----
From: John DeSoi [mailto:desoi@pgedit.com]
Sent: Monday, March 28, 2005 12:40 PM
To: mg
Cc: 'pgsql-interfaces@postgresql.org'
Subject: Re: [INTERFACES] MS Access/ODBC 'ERROR: invalid input syntax
for integer: "A"'
On Mar 28, 2005, at 10:57 AM, mg wrote:
> I've searched http://search.postgresql.org/ and Google on this error
> to no
> avail. I suspect this problem relates to the integer / single quote
> issue
> referenced in previous posts. It may also relate to the domain I
> created,
> but I'm not sure how to proceed.
What happens if you just define id as int4 rather than using the
domain? I'm not really familiar with ACCESS/ODBC, but I'm wondering if
it really knows anything about domains.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL