Horton

Thread

  1. ODBC Driver

    Bruce Tong <zztong@laxmi.ev.net> — 1998-07-22T17:11:29Z

    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:

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. ;)


Bruce Tong                 |  Got me an office; I'm there late at night.
Systems Programmer         |  Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE  |  
zztong@laxmi.ev.net        |  -- Joe Walsh for the 21st Century

  2. Re: [INTERFACES] ODBC Driver

    Byron Nikolaidis <byronn@insightdist.com> — 1998-07-22T22:42:46Z

    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

  3. Re: [INTERFACES] ODBC Driver

    Sbragion Denis <infotecn@tin.it> — 1998-07-23T06:39:47Z

    Hello,

At 13.11 22/07/98 -0400, you wrote:
>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:
>
>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. ;)

Are you sure this is the cause of the problem? I have been able to perform
INNER JOIN queries (and even outer joins) using Visual Basic 5.0 without
problems even if pgsql doesn't support them. The undelrying Jet database
engine performs the join using simpler queries. Things are pretty slow, but
it works. As far as I know Access should use the same Jet database engine
so things should be the same. 

Any hint here? 

ByX!

	Dr. Sbragion Denis
	InfoTecna
	Tel, Fax: +39 39 2324054
	URL: http://space.tin.it/internet/dsbragio

  4. Re: [GENERAL] ODBC Driver

    Maarten Boekhold <maartenb@dutepp0.et.tudelft.nl> — 1998-07-23T09:23:37Z

    > 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

Aren't INNER JOIN's not exactly the same as what we call a 'normal' join?
ie. isn't it an idea to add the syntax of an INNER JOIN to the parser and 
just kinda ignore it? You could have the scanner return just a "," (comma)
for the INNER_JOIN token....

Maarten

_____________________________________________________________________________
| TU Delft, The Netherlands, Faculty of Information Technology and Systems  |
|                   Department of Electrical Engineering                    |
|           Computer Architecture and Digital Technique section             |
|                          M.Boekhold@et.tudelft.nl                         |
-----------------------------------------------------------------------------

  5. Re: [INTERFACES] ODBC Driver

    Bruce Tong <zztong@laxmi.ev.net> — 1998-07-23T12:48:54Z

    > >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. ;)
> 
> Are you sure this is the cause of the problem? I have been able to perform
> INNER JOIN queries (and even outer joins) using Visual Basic 5.0 without
> problems even if pgsql doesn't support them. The undelrying Jet database
> engine performs the join using simpler queries. Things are pretty slow, but
> it works. As far as I know Access should use the same Jet database engine
> so things should be the same.

No, I'm not sure that is the problem. ;) That is essentially my guess
based on what I remember reading. A number of people have reported more
detailed explanations which are undoubtably more correct.


Bruce Tong                 |  Got me an office; I'm there late at night.
Systems Programmer         |  Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE  |  
zztong@laxmi.ev.net        |  -- Joe Walsh for the 21st Century

  6. Re: [INTERFACES] ODBC Driver

    Bruce Tong <zztong@laxmi.ev.net> — 1998-07-23T12:59:06Z

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

Access 97.

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

I can make my database available to you over the net if you want. I just
need your IP address so I can authorize you in pg_hba. Contact me directly
if you're interested and I'll create an account for you, etc. The actual
tables are a little bigger than the examples I posted. I can also send you
the exact table definitions as I have all the SQL to create them in a file
I use in accordance with a makefile. Let me know if/how you want to
proceed.

I'll grab the latest driver and try that as well.


Bruce Tong                 |  Got me an office; I'm there late at night.
Systems Programmer         |  Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE  |  
zztong@laxmi.ev.net        |  -- Joe Walsh for the 21st Century

  7. Re: [INTERFACES] ODBC Driver

    Bruce Tong <zztong@laxmi.ev.net> — 1998-07-23T15:14:49Z

    > I'll grab the latest driver and try that as well.

Installed, threw away the old data link, created a new data link, same
problem occurred.


Bruce Tong                 |  Got me an office; I'm there late at night.
Systems Programmer         |  Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE  |  
zztong@laxmi.ev.net        |  -- Joe Walsh for the 21st Century

  8. Re: [INTERFACES] ODBC Driver

    Byron Nikolaidis <byronn@insightdist.com> — 1998-07-23T15:57:16Z

    Bruce Tong wrote:
> 
> Installed, threw away the old data link, created a new data link, same
> problem occurred.
> 

OK, could you try this before we resort to more drastic things.  When
you are looking at the design view of your query,  right click and call
up "properties" or call it up under the "View" menu.  For the "RecordSet
type" property, try changing it to "Snapshot".  See if this causes
Access to not throw the "order by" clause in the query.

Of course having the recordset type as a snapshot means you can't update
the query resultset.  Let me know if it works.

Byron

  9. Re: [INTERFACES] ODBC Driver

    Bruce Tong <zztong@laxmi.ev.net> — 1998-07-23T17:11:57Z

    > OK, could you try this before we resort to more drastic things.  When
> you are looking at the design view of your query,  right click and call
> up "properties" or call it up under the "View" menu.  For the "RecordSet
> type" property, try changing it to "Snapshot".  See if this causes
> Access to not throw the "order by" clause in the query.
> 
> Of course having the recordset type as a snapshot means you can't update
> the query resultset.  Let me know if it works.

Same error.


Bruce Tong                 |  Got me an office; I'm there late at night.
Systems Programmer         |  Just send me e-mail, maybe I'll write.
Electronic Vision / FITNE  |  
zztong@laxmi.ev.net        |  -- Joe Walsh for the 21st Century

  10. Re: [INTERFACES] ODBC Driver -- Access Order By problem solved!!!

    Byron Nikolaidis <byronn@insightdist.com> — 1998-07-24T14:36:49Z

    Bruce Tong wrote:
> 
> 
> 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:
> 


I finally figured out what the heck is going on with this order by
problem in MS Access.  I tested your query on a computer that has older
versions of the Microsoft Jet db engine on it and guess what?  It does
the same thing you described!  The reason that I used to have the
problem and it miraculously went away was because I installed Visual
Basic 5.0 and it upgraded the MS Jet dll's.

Your right about the snapshot.  It still tries to throw the order by
clause in there.
But, here's a simple workaround that you can easily try.  When looking
at your query in Access, right click on the fields area and select
"Totals".  This will add a group by clause but it won't have any real
effect, other than it will let your query run!

So, there are two things you must do to fully fix the order by problem:
1. Upgrade the MS Jet DLL (this is the real problem here)
2. Get the patch for the order by/group by from our web site (it will be
in postgres 6.4 also)

Interestingly, even if you get the postgres patch like we have here, the
older MS Jet engine will still not let you sort the columns the way you
want, because it always throws that same old order by in there.  

The working version of \windows\system\MSJET35.DLL is 3.51.0623.4
The broken version I tested was 3.50.3602.4
I'm not sure at what release they fixed this problem.

I will dig around on MS web site.  

Does anyone know if its OK to redistribute the MS JET DB Engine DLL,
like with our ODBC driver for example???  (That thing is big too,
1MB!!!)

For that matter, is there a simple way to get it from Microsoft?

Byron