Re: Strange ODBC behavior.

[Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au>]

Paul Lambert wrote:
> Paul Lambert wrote:
> > I had a server have a power outage on one of my PostgreSQL servers 
> > yesterday and am having a strange ODBC issue now - not sure if they 
> > are related occurances though.
> >
> > I have a table called billing_code and one called models in my 
> > database. If I connect via psql to the database with my standard db 
> > user I can see both of these tables as well as the data contained within.
> >
> > If I attempt to link these tables from MS Access, the billing_code one 
> > returns "The Microsoft Jet database engine could not find the object 
> > 'public.billing_code'. Make sure the object exists and that you spell 
> > its name and the path name correctly" - this error despite the fact 
> > that I had to physically select the table from a list it presented me 
> > when I went into the linked table manager.
> >
> > If I link the models table, it links fine, but when I attempt to open 
> > it, I just get #Deleted in every column of every row for about 54,000 
> > rows (the total number in the table)
> >
> > The same problem happens with some of the other tables in the database 
> > - some will get the first problem, some will get the second problem, 
> > and some will link and load data without issue.
> >
> > I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in 
> > case there was a problem with the driver getting corrupted but the 
> > problem persists.
> >
> > Can anyone offer any thoughts or suggestions on what the problem might 
> > be?
> >
> > Thanks in advance,
> > Paul.
>
> I've done a full pg_dump, deleted the database and tablespaces, 
> recreated everything, restored the data and the same behavior is being 
> exhibited - at least as far as returning #Deleted in some of the tables.
>
> Users with an Access database that already has the table linked don't 
> have a problem accessing the data, but those adding a new link to the 
> table do.
>
> Any thoughts on this or is it more of an access issue?

I'm still having the problem with a few of the tables reporting:

"The Microsoft Jet database engine could not find the object 
public.billing_code'. Make sure the object exists and that you spell its 
name and the path name correctly"

When I attempt to link them in MS access.

It seems that any tables in my database that contain an underscore in 
the table name are giving this problem.

Can't see any settings in the DSN that might fix this though.

The psqlodbc_<pid>.log file has the following:

select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, 
a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, 
d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace 
n on n.oid = c.relnamespace and c.relname like 'billing\\_code' and 
n.nspname like 'public') inner join pg_catalog.pg_attribute a on (not 
a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join 
pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d 
on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order 
by n.nspname, c.relname, attnum'

Note the 'billing\\_code' in there.

If I run this query in psql I get no rows. If I change it to 
'billing\_code' or 'billing_code' (taking out one or both of the 
backslashes) I get correct results.

Seems to me that the odbc driver is not handling underscores in the 
table name correctly - perhaps a conflict with it and me having turned 
on standard_conforming_strings?

Any thoughts?

--
Paul Lambert
Database Administrator
AutoLedgers