Strange ODBC behavior.

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.

--
Paul Lambert
Database Administrator
AutoLedgers


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Strange ODBC behavior.

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?


--
Paul Lambert
Database Administrator
AutoLedgers


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

Re: Strange ODBC behavior.

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?
1) Could you post here CREATE script for one of the tables with problem
you descrived? #delete message appears when something happens to the
primary keys.
2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI
driver version?
3) Please, have a look on the linked table structure as it's recognised
by MS Access. And post it here.


Andrei.


---------------------------(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: Strange ODBC behavior.

On Friday 03 August 2007 1:38 am, 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 believe this is more of an Access/ODBC issue. I recently had a similar
problem with a Foxpro ODBC setup and Access. In that case I had to renter the
path information to get Access to see the data in the tables. I have also
found it necessary to reboot the machine with Access on it for changes to be
seen.
--
Adrian Klaver
aklaver@comcast.net

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

Re: Strange ODBC behavior.

Andrei Kovalevski wrote:

>> Any thoughts on this or is it more of an access issue?
> 1) Could you post here CREATE script for one of the tables with problem
> you descrived? #delete message appears when something happens to the
> primary keys.
> 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI
> driver version?
> 3) Please, have a look on the linked table structure as it's recognised
> by MS Access. And post it here.
>
>
> Andrei.
>
>
>

I believe I have found the problem.

The primary key fields are defined in PG as type text, but MS access is
bringing them across as type "memo" - whatever that is.

It seems to be bringing most text type fields across like this.

I changed them to varchar on one of the tables I'm having a problem
with, relinked and they are coming across with a type of text on the
fields now and I can see the data without issue.

Strange... has anyone see that before?

--
Paul Lambert
Database Administrator
AutoLedgers


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

Re: Strange ODBC behavior.

On Sunday 05 August 2007 3:26 pm, Paul Lambert wrote:
> Andrei Kovalevski wrote:
> >> Any thoughts on this or is it more of an access issue?
> >
> > 1) Could you post here CREATE script for one of the tables with problem
> > you descrived? #delete message appears when something happens to the
> > primary keys.
> > 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI
> > driver version?
> > 3) Please, have a look on the linked table structure as it's recognised
> > by MS Access. And post it here.
> >
> >
> > Andrei.
>
> I believe I have found the problem.
>
> The primary key fields are defined in PG as type text, but MS access is
> bringing them across as type "memo" - whatever that is.
>
> It seems to be bringing most text type fields across like this.
>
> I changed them to varchar on one of the tables I'm having a problem
> with, relinked and they are coming across with a type of text on the
> fields now and I can see the data without issue.
>
> Strange... has anyone see that before?
Actually this has been noted before. Search Google or psql-odbc archives for
references. There are some changes you can make to the ODBC settings to help.
Unfortunately, I cannot remember them off the top of my head. I only bring
this up because I saw your post on the general list about changing the type
of 600 text columns. You might find it easier to change the behavior of the
ODBC driver.
--
Adrian Klaver
aklaver@comcast.net

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

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

Re: Strange ODBC behavior.

Adrian Klaver wrote:

> Actually this has been noted before. Search Google or psql-odbc archives for
> references. There are some changes you can make to the ODBC settings to help.
> Unfortunately, I cannot remember them off the top of my head. I only bring
> this up because I saw your post on the general list about changing the type
> of 600 text columns. You might find it easier to change the behavior of the
> ODBC driver.

I should have thought about checking the ODBC settings before.

There is a setting called "Text as LongVarChar" - I've unticked that and
now text fields are coming through as text.

Thanks for the pointers.

--
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: Strange ODBC behavior.

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


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

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