Skip site navigation (1) Skip section navigation (2)

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

From: Michael Goei <postgres(at)ballistech(dot)com>
To: 'Peter Eisentraut' <peter_e(at)gmx(dot)net>
Cc: "'pgsql-odbc(at)postgresql(dot)org'" <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: psqlODBC 'ERROR: invalid input syntax for integer: "
Date: 2005-03-30 18:20:42
Message-ID: C969C878B71ED311AA2500104B973EF5632761@WWW (view raw or flat)
Thread:
Lists: pgsql-odbc
I'm attempting to link the postgres table with MS Access, via the ODBC
driver.  After the link process, when I open the linked table from within MS
Access, an error is generated.  When I inspect the connection log, I find
the following entry (SQL auto-generated by MS Access/ODBC):

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'

I believe that MS Access (or ODBC) is misinterpreting the id field.  It's
actually defined as a custom domain of type int4, but because of the
misinterpretation, MS Access is trying to select a page full of records
using erroneous SQL (it assumes that the "id" field is character instead of
int4).

When I create a test table and specify that the attribute "id" is of type
int4 (instead of a custom int4 datatype), then MS Access/ODBC can open the
table from it's GUI properly.  So, I've traced the problem down to a
misinterpretation of the custom domain.

I'm looking for a solution that will allow MS Access/ODBC to recognize the
int4 domain and open the table properly.

Thanks,

mg

-----Original Message-----
From: Peter Eisentraut [mailto:peter_e(at)gmx(dot)net]
Sent: Wednesday, March 30, 2005 10:08 AM
To: Michael Goei
Cc: 'pgsql-odbc(at)postgresql(dot)org'
Subject: Re: [ODBC] psqlODBC 'ERROR: invalid input syntax for integer:
"A"'


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/

Responses

pgsql-odbc by date

Next:From: Kelly BurkhartDate: 2005-03-30 18:37:14
Subject: Re: 32-bit ints on 64-bit linux
Previous:From: Peter EisentrautDate: 2005-03-30 18:16:42
Subject: Re: SQL_CHAR, SQL_INTEGER or SQL_BIT datatype problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group