Thread: psqlODBC 'ERROR: invalid input syntax for integer: "A"'

psqlODBC 'ERROR: invalid input syntax for integer: "A"'

From
Michael Goei
Date:
Hello PostgreSQL gurus!

Can someone please help me solve this problem please?

I'm using Microsoft Access 2000 (9.0.6926 SP-3) to access (PostgreSQL) 7.4.6
via the psqlODBC drivers (both versions 7.3.02 and 8.0.0101).  The table I'm
accessing, "trx", has a primary key "id" column defined as a custom domain,
"id_dom".  The relevant DDL code is below.  When I attempt to link MSAccess
to the postgres table, I receive an error in the connection log: 'ERROR:
invalid input syntax for integer: "A"'.  I've pasted more of the error log
below.  I have several other tables with this same "id" column definition
and the ODBC driver is choking on those tables, yet it is connecting
properly to other tables (ones which don't have the "id" column).

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.

I would like to get this ODBC driver connecting properly.  Can anyone please
help?

Thanks in advance!

mg

*** DDL and error log below ***

CREATE DOMAIN id_dom
  AS int4
  NOT NULL;

CREATE TABLE trx
(
  id id_dom NOT NULL DEFAULT nextval('trxID_seq'::text),
  unid unid_dom,
  rid rid_dom,
  trxtype code2nn_dom,
  bilb bool NOT NULL DEFAULT false,
  entjobcustid rid_dom,
...

conn=122491600, 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" =
'à''
ERROR from backend during send_query: 'ERROR:  invalid input syntax for
integer: "A"'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'

Re: psqlODBC 'ERROR: invalid input syntax for integer:"A"'

From
Peter Eisentraut
Date:
Michael Goei wrote:
> Can someone please help me solve this problem please?

Well, the column is defined as an integer and you are trying to put
non-numeric characters in it.  Why is that?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/