informix odbc problem 3.8x

We have problems with informix odbc 3.81. From MS-ACCES 2000 to linked tables in IDS7.31 on aix through odbc 3.81 a select with 'order by' results in SQL error -309. The select is "select a, b from tab1 order by a" This is good SQL for IDS7.31. IDS returns error -309 ?? In the odbc trace we see that above query wil result in another query that first extracts the index of tab1, and this ordered by a. The odbc will first do : "select index from tab1 order by a" This syntax is not supported by IDS 7.31, col "a" must be in the select list. And so IDS returns error -309. I assume that the odbc will first return the index and then the list of the first query. However ODBC 3.81 is validated with IDS7.31 (release notes) !! No problem with an older version ODBC 3.34 Could someone help us to resolve this problem with 3.81 ??

forum.subscriber@iiug.org wrote on 03/08/2005 04:26:50 AM: > We have problems with informix odbc 3.81. > From MS-ACCES 2000 to linked tables in IDS7.31 on aix through odbc > 3.81 a select with 'order by' results in SQL error -309. > The select is "select a, b from tab1 order by a" > This is good SQL for IDS7.31. > IDS returns error -309 ?? > > In the odbc trace we see that above query wil result in another > query that first extracts the index of tab1, and this ordered by a. > The odbc will first do : "select index from tab1 order by a" > This syntax is not supported by IDS 7.31, col "a" must be in the select list. > And so IDS returns error -309. > > I assume that the odbc will first return the index and then the list > of the first query. > However ODBC 3.81 is validated with IDS7.31 (release notes) !! > > No problem with an older version ODBC 3.34 > Could someone help us to resolve this problem with 3.81 ?? I was tempted to respond with "Doctor, Doctor, it hurts when I hit myself on the head with this mallet. So, stop hitting yourself." However, your last but one line allows you off the hook - you appear to have a compatibility issue. Was the older version of ODBC a driver provided by IBM/Informix? I'm not as au fait with ODBC versions as I perhaps should be. Several observations. (1) The SQL standard does not permit your select statement - columns listed in the ORDER BY clause must appear in the select list too. (2) IDS 9.40 and above permits you to order by columns that are not selected; IDS 7.31 does not, and never will (99.9999% probability). (3) The code you're submitting is valid in 7.31 - as you note. Therefore, we need to track the source of the extra query that you didn't write. It seems to be wrong. You should probably go to Tech Support with this - I presume you do have support... -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"