Re: [INTERFACES] ODBC Driver - Mailing list pgsql-general

Bruce Tong wrote:
>
> I knew I needed to check my work before bringing the ODBC driver problems
> out in the open. Here's what I've found.
>
> I was designing a simple query using MS-Access having already defined
> an external data source to a PostgreSQL database just to make sure
> things work as I expected. The database is bigger than this, but here's
> the relevant parts of the tables...
>
> Table: School
>   school_id      integer unique not null,
>   school_name    varchar( 50 )
>
> Table: Contact
>   contact_id     integer unique not null,
>   contact_lname  varchar( 50 ),
>   contact_fname  varchar( 50 ),
>   contact_school integer not null
>
> I wanted to see contact_lname, contact_fname, and school_name. Using MS
> Access's query view I dragged a link from contact_school to school_id,
> then I dragged the three fields I wanted to my query. (Maybe you can see
> where this is going.) I attempted to view the results.
>
> A window appeared and said "ODBC-call failed".
>
> Another window appeared and said "Error while executing the query. ERROR:
> The field being ordered by must appear in the target list (#1)."
>
> Since I hadn't specified any sorting, the "order by" part of the message
> had me confused. I decided to have MS Access show me the SQL it had
> generated:
>

Actually, there are two issues here.  First, the message about the field
being ordered by must appear in the target list is appearing because
Access is throwing that order by clause in there for some reason which
we have been unable to discover (perhaps for optimization?).  BTW, are
you using Access 97 or 95?  Second, Dave has a patch for Postgres on our
website which allows the order by and group by clauses to not be in the
target list, which would probably get you past this.

I created your tables in my database and was not able to re-create the
error in Access.  You might try upgrading to the latest driver (.0248)
which is what I was using to test with.  I used to get the same message
you got here but I haven't been able to reproduce it lately.  You might
want to relink your tables in Access and/or re-create the query before
you try your test again.

> SELECT contact.contact_lname, contact.contact_fname, school.school_name
> FROM contact INNER JOIN school ON contact.contact_school =
> school.school_id;
>
> If I remember correctly, INNER JOIN is not yet supported in PostgreSQL,
> which would explain the failure. Sorry to have accused the failure on the
> ODBC driver. While some people's ignorance is bliss, mine thrashes. At
> least now I know what a $#@!% INNER JOIN is. ;)

Sort of true.  The INNER JOIN clause is not yet implemented in postgres,
but the Microsoft Jet Database engine will translate this query into a
standard join (i.e., select ... from contact, school WHERE
contact.contact_school = school.school_id).  If you look in the commlog
file (psqlodbc.log) on your pc, you will see the translated query.

Hope this helps.

Byron