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_.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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: ODBC driver adding extra characters to table names.

Paul Lambert writes:
> for example, the psqlodbc_.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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: ODBC driver adding extra characters to table names.

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: ODBC driver adding extra characters to table names.

Paul Lambert 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

Re: ODBC driver adding extra characters to table names.

Tom Lane wrote:
> Paul Lambert 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

Re: ODBC driver adding extra characters to table names.

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 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: ODBC driver adding extra characters to table names.

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: ODBC driver adding extra characters to table names.

Hi.

From: "Paul Lambert"

>
> 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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: ODBC driver adding extra characters to table names.

Hi.

From: "Paul Lambert"

> 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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend