Thread: Problems with PGSQL ODBC drivers
I posted this to the general newsgroup
'comp.databases.postgresql.questions' a little while ago, and got no takers!
<grin>
As I am fairly sure this is a ODBC driver issue I am reposting it here,
somewhat ammended, in the hope of enlightenment.
===================================
I have run up against a problem trying to use the PostgreSQL ODBC
Windows driver, and wondered if anyone can point me in the right
direction.
Basics:
Server: PostgreSQL 7.2.1 on i386-unknown-freebsd4.5,
compiled by GCC 2.95.3
ODBC driver: PostgreSQL (not beta) 7.02.00.05
Windows: NT4 - SP 4
Excel 2000
I have a view on the server from which I wish to import data into
Excel, using MS Query.
If I type the SQL query direct into MSQuery it works fine, and the
data is fetched.
If I attempt to use the MSQuery 'wizard' then when I get to 'Finish'
and ask to return the data to Excel then I get an Error box with the
message: "Error: No such attribute or function 'oid'". This message
is echoed in the PostgreSQL logs.
I enabled the log files in the ODBC driver, and noticed that an
attribute 'oid' was added to the query that I would otherwise have
expected the MSQuery app to send. AFAIK views don't have oids, so it
would seem to me that the driver is misidentifying the view as a table
with oids?
Looking further at the logs, and going though the driver source, it
*looks* to me as if the code for the ODBC function SQLSpecialColumns
when called with IdentifierType = SQL_BEST_ROWID may be the culprit.
It *always* (AFAICT) returns oid for this call.
Looking through info.c I note the following:
There is a comment early in the function where it says 'Create the
query to find out if this is a view or not...', and fetches
pg_class.relhasrules. However in the code for SQLTables is the
comment 'view is represented by its relkind since 7.1'.
Any suggestions as to work-arounds, patches, or tests to try would
be welcomed.
========================
Regards,
Harry.
Harry Broomhall wrote:
>
> I posted this to the general newsgroup
> 'comp.databases.postgresql.questions' a little while ago, and got no takers!
>
> <grin>
>
> As I am fairly sure this is a ODBC driver issue I am reposting it here,
> somewhat ammended, in the hope of enlightenment.
>
> ===================================
>
> I have run up against a problem trying to use the PostgreSQL ODBC
> Windows driver, and wondered if anyone can point me in the right
> direction.
>
> Basics:
>
> Server: PostgreSQL 7.2.1 on i386-unknown-freebsd4.5,
> compiled by GCC 2.95.3
>
> ODBC driver: PostgreSQL (not beta) 7.02.00.05
>
> Windows: NT4 - SP 4
>
> Excel 2000
>
> I have a view on the server from which I wish to import data into
> Excel, using MS Query.
>
> If I type the SQL query direct into MSQuery it works fine, and the
> data is fetched.
>
> If I attempt to use the MSQuery 'wizard' then when I get to 'Finish'
> and ask to return the data to Excel then I get an Error box with the
> message: "Error: No such attribute or function 'oid'". This message
> is echoed in the PostgreSQL logs.
>
> I enabled the log files in the ODBC driver, and noticed that an
> attribute 'oid' was added to the query that I would otherwise have
> expected the MSQuery app to send. AFAIK views don't have oids, so it
> would seem to me that the driver is misidentifying the view as a table
> with oids?
Please try to turn off the *Updatable cursors* DSN option.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
Hiroshi Inoue writes:
[Charset iso-2022-jp unsupported, filtering to ASCII...]
>
> Please try to turn off the *Updatable cursors* DSN option.
Thank you for your quick response.
I have tried your suggestion with no success! :-(
The problem is as before.
(Note - I tried previously to reply, but the 'From:' header line wasn't
set for the list, and the remote mailer had some error.)
Regards,
Harry.
Harry Broomhall wrote:
> Hiroshi Inoue writes:
> [Charset iso-2022-jp unsupported, filtering to ASCII...]
>>
>> Please try to turn off the *Updatable cursors* DSN option.
>
> Thank you for your quick response.
>
> I have tried your suggestion with no success! :-(
>
> The problem is as before.
>
> (Note - I tried previously to reply, but the 'From:' header line
> wasn't set for the list, and the remote mailer had some error.)
>
> Regards,
> Harry.
>
So that MS Access can auto work out which field to use as a key I first of all create a view as a table with primary key and then create a select rule. A similar thing may stop Excel trying to choose oid as primary key. This is assuming that you've already made sure that its not showing the OID column and isn't set to use it as a fake index.
hth,
- Stuart
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.
Henshall, Stuart - Design & Print writes:
> Harry Broomhall wrote:
> > Hiroshi Inoue writes:
> > [Charset iso-2022-jp unsupported, filtering to ASCII...]
> >>
> >> Please try to turn off the *Updatable cursors* DSN option.
> >
> > Thank you for your quick response.
> >
> > I have tried your suggestion with no success! :-(
> >
> > The problem is as before.
> >
> > (Note - I tried previously to reply, but the 'From:' header line
> > wasn't set for the list, and the remote mailer had some error.)
> >
> > Regards,
> > Harry.
> >
> So that MS Access can auto work out which field to use as a key I first of
> all create a view as a table with primary key and then create a select rule.
Thanks for your comments. I wasn't aware particularly of the 'Create Rule'
command, probably because it isn't in the standard!
I note the manual suggests that using 'create view' is better style
than creating a table and select rule! <grin>
> A similar thing may stop Excel trying to choose oid as primary key. This is
> assuming that you've already made sure that its not showing the OID column
Er - I thought that views (created by 'CREATE VIEW') didn't have OIDs
anyway? So it hasn't got one to show?
> and isn't set to use it as a fake index.
Can you expand on that please? I'm not sure what you mean here.
Regards,
Harry.
Harry Broomhall wrote:
> Henshall, Stuart - Design & Print writes:
>> Harry Broomhall wrote:
>>> Hiroshi Inoue writes:
>>> [Charset iso-2022-jp unsupported, filtering to ASCII...]
>>>>
>>>> Please try to turn off the *Updatable cursors* DSN option.
>>>
>>> Thank you for your quick response.
>>>
>>> I have tried your suggestion with no success! :-(
>>>
>>> The problem is as before.
>>>
>>> (Note - I tried previously to reply, but the 'From:' header line
>>> wasn't set for the list, and the remote mailer had some error.)
>>>
>>> Regards,
>>> Harry.
>>>
>> So that MS Access can auto work out which field to use as a key I
>> first of all create a view as a table with primary key and then
>> create a select rule.
>
>
> Thanks for your comments. I wasn't aware particularly of the
> 'Create Rule' command, probably because it isn't in the standard!
>
> I note the manual suggests that using 'create view' is better style
> than creating a table and select rule! <grin>
True, but this way you can specify a primary key for your view which some things seem to like (eg MS Access). It is however a little naughty :)
>
>> A similar thing may stop Excel trying to choose oid as primary key.
>> This is assuming that you've already made sure that its not showing
>> the OID column
>
> Er - I thought that views (created by 'CREATE VIEW') didn't have
> OIDs anyway? So it hasn't got one to show?
>
>> and isn't set to use it as a fake index.
>
> Can you expand on that please? I'm not sure what you mean here.
>
> Regards,
> Harry.
>
>
You're correct about views shouldn't have OIDs, but I was wondering if the ODBC datasource was set to use OIDs with fake indexes (page 2 of the datasource advance options) it might be picking that up as a primary key; Alternativly this might be totaly irrelevant :/
- Stuart
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.
Henshall, Stuart - Design & Print writes:
> Harry Broomhall wrote:
[SNIP]
> >
> >> and isn't set to use it as a fake index.
> >
> > Can you expand on that please? I'm not sure what you mean here.
> >
> > Regards,
> > Harry.
> >
> >
>
> You're correct about views shouldn't have OIDs, but I was wondering if the
> ODBC datasource was set to use OIDs with fake indexes (page 2 of the
> datasource advance options) it might be picking that up as a primary key;
> Alternativly this might be totaly irrelevant :/
Ah! I see what you are refering to now. In the second page for OID
options I have 'Show Column' unchecked, and 'Fake Index' is greyed out (and
not set).
Regards,
Harry.
Henshall, Stuart - Design & Print writes:
> So that MS Access can auto work out which field to use as a key I first of
> all create a view as a table with primary key and then create a select rule.
Thinking about this further, it occurs to me that if the driver
routines for SQLSpecialColumns returned the actual Primary Key for a table
(if it existed) rather than OID then Access may have the required hint?
The sort of scheme I had in mind goes something like this:
if (view)
return SQL_NO_DATA
else if (table) {
if (primary key esists)
return all elements of PK.
else if (oid exists)
return oid
else
return SQL_NO_DATA
}
Or am I way off-beam with this idea?
Regards,
Harry.
Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ . regards, Hiroshi Inoue > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Harry Broomhall > Sent: Friday, January 10, 2003 8:48 PM > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Problems with PGSQL ODBC drivers > > > Henshall, Stuart - Design & Print writes: > > So that MS Access can auto work out which field to use as a key > I first of > > all create a view as a table with primary key and then create a > select rule. > > > Thinking about this further, it occurs to me that if the driver > routines for SQLSpecialColumns returned the actual Primary Key for a table > (if it existed) rather than OID then Access may have the required hint? > > The sort of scheme I had in mind goes something like this: > > if (view) > return SQL_NO_DATA > > else if (table) { > if (primary key esists) > return all elements of PK. > else if (oid exists) > return oid > else > return SQL_NO_DATA > } > > Or am I way off-beam with this idea? > > Regards, > Harry. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Hiroshi Inoue writes: > Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/ . I'm impressed! This snapshot allows the use of the 'wizard' without giving the OID error. Many thanks for such quick work. Regards, Harry.
Hi Scenario : PostgresSQL 7.1.3, freeBSD, ODBC Sometimes ado return this error 'ADO is returning an E-FAIL state' What's happening ? Thanks