Thread: ODBC driver adding extra characters to table names.
*REPOST* Just in case this got missed on the end of my last thread. I'm still having the problem with a few of the tables in my database 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. for example, the psqlodbc_<pid>.log file has the following when I attempt to link a table called billing_code: 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 the table details. It seems to me that the odbc driver is not handling underscores in the table name correctly - perhaps a conflict with the driver and me having turned on standard_conforming_strings in postgresql.conf? Any thoughts? -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> for example, the psqlodbc_<pid>.log file has the following when I
> attempt to link a table called billing_code:
> 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 the table details.
It sounds like you have standard_conforming_strings turned ON (making
backslashes not special in string literals) but there is some part of
the client-side code that is not aware of that, and thinks it needs to
double the backslash in the LIKE pattern. Either turn off
standard_conforming_strings or make sure you are using code that is
new enough to cope.
regards, tom lane
Tom Lane wrote: > It sounds like you have standard_conforming_strings turned ON (making > backslashes not special in string literals) but there is some part of > the client-side code that is not aware of that, and thinks it needs to > double the backslash in the LIKE pattern. Either turn off > standard_conforming_strings or make sure you are using code that is > new enough to cope. > Correct, standard_conforming_strings is ON. Client side is microsoft access doing a link table. Would it not be the ODBC driver itself that is adding the backslashes rather than M$ Access? Seems to me a bit strange that access would do something of that nature. My initial assumption was that the ODBC driver makes an assumption that standard_conforming_strings will not be on and adjusts the table names accordingly. The following from the mylog file seems to me to indicate this sort of behavior: [35520-7.722]adjust in=billing\_code(13) [35520-7.722]adjust output=billing\\_code(14) [35520-7.722]adjust in=public(6) [35520-7.722]adjust output=public(6) Up to this point in the log everything says billing_code, after this, everything says billing\\_code. P. -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert <paul.lambert@autoledgers.com.au> writes:
> My initial assumption was that the ODBC driver makes an assumption that
> standard_conforming_strings will not be on and adjusts the table names
> accordingly.
Which ODBC driver version are you using? I'd quite expect a pre-8.2
release to not be prepared to deal with this. If the 8.2 version still
can't hack it then you've got a legitimate beef ...
regards, tom lane
Tom Lane wrote: > Paul Lambert <paul.lambert@autoledgers.com.au> writes: >> My initial assumption was that the ODBC driver makes an assumption that >> standard_conforming_strings will not be on and adjusts the table names >> accordingly. > > Which ODBC driver version are you using? I'd quite expect a pre-8.2 > release to not be prepared to deal with this. If the 8.2 version still > can't hack it then you've got a legitimate beef ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > I'm using version 8.2.4 of Postgres, and 8.02.04 of the ODBC driver - first had the problem using 8.02.03 ODBC, but upgraded with no change. P -- Paul Lambert Database Administrator AutoLedgers
Hi. Sorry, very late reaction... Um, I tried it on ACCESS2000.(08.02.0402) This is the table which stretched the link. http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link1.PNG Query which you say was imitated.? http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link2.PNG It is tested. http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link3.PNG Your problem is not reproduced....I may have missed something. If possible, please let me know reproducible environment. Thanks! Regards, Hiroshi Saito From: "Paul Lambert" > Tom Lane wrote: >> Paul Lambert <paul.lambert@autoledgers.com.au> writes: >>> My initial assumption was that the ODBC driver makes an assumption that >>> standard_conforming_strings will not be on and adjusts the table names >>> accordingly. >> >> Which ODBC driver version are you using? I'd quite expect a pre-8.2 >> release to not be prepared to deal with this. If the 8.2 version still >> can't hack it then you've got a legitimate beef ... >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >> >> > > I'm using version 8.2.4 of Postgres, and 8.02.04 of the ODBC driver - > first had the problem using 8.02.03 ODBC, but upgraded with no change. > > P > > -- > Paul Lambert > Database Administrator > AutoLedgers > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Hiroshi Saito wrote: > Hi. > > Sorry, very late reaction... > > Um, I tried it on ACCESS2000.(08.02.0402) > This is the table which stretched the link. > http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link1.PNG > Query which you say was imitated.? > http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link2.PNG > It is tested. > http://www.skcapi.co.jp/~saito/psqlODBC_TEST/psqlODBC_link3.PNG > > Your problem is not reproduced....I may have missed something. > If possible, please let me know reproducible environment. > > Thanks! > > Regards, > Hiroshi Saito > > It's happening on all of my client sites that I've turned standard_conforming_strings ON in postgresql.conf. Table definition as follows: create table billing_code ( dealer_id text not null , date_changed timestamp null , time_changed time null , billing_code_id text not null , des text , billing_code_type text , labour_rate numeric(19,4) null , labour_sales_group smallint null , customer_no int null , parts_sales_group smallint null , comeback text , workshop text not null, franchise text not null, location text not null, deleteflag text ) WITHOUT OIDS TABLESPACE ts_autodrs_main ; GRANT ALL PRIVILEGES ON billing_code TO "AutoDRS"; alter table billing_code add constraint pk_billing_code primary key ( billing_code_id, dealer_id, franchise, workshop, location ) ; Steps to reproduce: 0) Make sure standard_conforming_strings is turned ON and pg has been restarted. 1) Open up MS Access (2000 and 2003 both give me the problem) 2) Go to tables section, right click and select link table. 3) Find your ODBC data source. 4) Select public.billing_code from the list of tables. 5) Error is presented at that stage (don't even get to the point of writting a select query) I can attach the full mylog if you so wish - I haven't put it on yet because the log created from just that one query is around 800K so I'm leaving it off unless it's specifically requested. -- Paul Lambert Database Administrator AutoLedgers
Hi. From: "Paul Lambert" <paul.lambert@autoledgers.com.au> > > I can attach the full mylog if you so wish - I haven't put it on yet > because the log created from just that one query is around 800K so I'm > leaving it off unless it's specifically requested. Ooops Ok, I understood that TEXT(MEMO)-field had a problem. It was sure of the necessity of investigating in which the problem between MDAC or psqlODBC.:-( It seems that the degrade was caused by somewhere... Thanks! P.S) I'm the schedule of a weekend to vacation.. sorry in the delay of a response. Regards, Hiroshi Saito
Hi. From: "Paul Lambert" <paul.lambert@autoledgers.com.au> > Steps to reproduce: > 0) Make sure standard_conforming_strings is turned ON and pg has been > restarted. > 1) Open up MS Access (2000 and 2003 both give me the problem) > 2) Go to tables section, right click and select link table. > 3) Find your ODBC data source. > 4) Select public.billing_code from the list of tables. > 5) Error is presented at that stage (don't even get to the point of > writting a select query) > > I can attach the full mylog if you so wish - I haven't put it on yet > because the log created from just that one query is around 800K so I'm > leaving it off unless it's specifically requested. I pointed out the problem of TEXT-field involving this. Furthermore, your problem was in others again... Do you use FAKEOIDINDEX? Probably, Turning it OFF may help. However, It is necessary to continue TEXT-Field problem. Regards, Hiroshi Saito