Thread: ODBC Driver
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
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
> 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 | -----------------------------------------------------------------------------
> 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
> 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
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
> 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
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