MS-Access: can't sort linked INFORMIX table?
Sorting an updateable ODBC-linked Informix table from Access 97 fails with error -309 ("ORDER BY column must be in SELECT list"). Explanation: for updateable (dynaset) links Jet rewrites the query to select only the key column while ordering by another, which Informix rejects; Informix says it correctly reports this requirement via SQLGetInfo, so it's a Jet/Access issue (Informix bug 86030). Workarounds offered: use pass-through queries or an Informix view with ORDER BY, link as non-updateable/snapshot, add the sort column to the select list, use "top 100%", or upgrade to the Intersolv ODBC 3.11 driver and set WorkArounds=1073741824. No fix from Microsoft is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have an MS-Access front end using an ODBC link to an Informix backend and here is my problem: Attempting to sort an updateable linked table using MS access to Informix back end results in an ODBC error "[Informix][Odbc Informix Driver][Informix] ORDER BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc Informix Driver][Informix]A syntax error has occurred.". If the table is linked in non-updateable mode i.e. without a unique field, then you are able to sort. Filters work in either case. Has anyone seen this before? A can't believe this doesn't work! Here is are the details of my config: Backend: HP-UX helpful B.10.20 A 9000/877 INFORMIX-OnLine Version 7.20.UC2 ODBC: INFORMIX 2.80 32 BIT 2.80.0004 Configuration: Data Source Name : Year 2000 Database: y2k Server: Helpful Host: helpful service: sqlturbo Protocol: onsoctcp MS Access: Microsoft Access 97 SR-1 -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
On Sun, 21 Mar 1999 13:49:04 GMT, mfalcian9913@my-dejanews.com wrote: I read the error message as saying that the field(s) you ORDER BY, should also occur in your SELECT list. E.g. "SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerCity" would fail, while "SELECT CustomerID, CustomerName, CustomerCity FROM Customers ORDER BY CustomerCity" would succeed. This is not an unusual requirement. I believe some ADO drivers need this as well. -Tom. >I have an MS-Access front end using an ODBC link to an Informix backend and >here is my problem: > >Attempting to sort an updateable linked table using MS access to Informix back >end results in an ODBC error "[Informix][Odbc Informix Driver][Informix] ORDER >BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc Informix >Driver][Informix]A syntax error has occurred.". > >If the table is linked in non-updateable mode i.e. without a unique field, >then you are able to sort. Filters work in either case. > >Has anyone seen this before? A can't believe this doesn't work! > >Here is are the details of my config: >Backend: >HP-UX helpful B.10.20 A 9000/877 >INFORMIX-OnLine Version 7.20.UC2 > >ODBC: >INFORMIX 2.80 32 BIT 2.80.0004 >Configuration: >Data Source Name : Year 2000 >Database: y2k >Server: Helpful >Host: helpful >service: sqlturbo >Protocol: onsoctcp > >MS Access: >Microsoft Access 97 SR-1 > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Hi I don't know who, Yes i have the same problem over in the office. I will have to check out there, what version of the ODBC driver i have and CLI is important too. I'm running also INFORMIX-OnLine Version 7.20.UC2 in a HP-UX. But think that the problem reside in the ODBC driver or CLI can decide. What i can say too you more than you allready explain, is that if you don't have the index created in the Informix, you will not be able to sort that field in any query or table. The turn around that problem for me was, to create Query Pass-through that way you can retrive data sorted that doesn't have indexes created in the INFORMIX. I suspect that with a new version of the ODBC driver that could be solved, but neither Informix nor Intersolve have give me any answer's. Pedro Gil
pmpg_No Spam_98@Hotmail.com (Pedro Gil ) wrote: > But think that the problem reside in the ODBC driver or CLI can > decide. > > What i can say too you more than you allready explain, is that if you > don't have the index created in the Informix, you will not be able to > sort that field in any query or table. > > The turn around that problem for me was, to create Query Pass-through > that way you can retrive data sorted that doesn't have indexes created > in the INFORMIX. Obviously, one could get the Informix DBA to create an index on the field or create one yourself if you do Informix DBA work. An appropriate passthrough query should solve the problem. Another solution would be to create a View in Informix with the appropriate ORDER BY clause. As I understand it, this is a problem with the way Jet handles the queries it sends to the server (Informix) -- they differ from what you see in Access, even in SQL view. I don't think InterSolv is going to be able to solve it for you. And, of course, as you point out, you get the erroneous message even though the field _IS_ in your select list. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
mfalcian9913@my-dejanews.com wrote: > > I have an MS-Access front end using an ODBC link to an Informix backend and > here is my problem: > > Attempting to sort an updateable linked table using MS access to Informix back > end results in an ODBC error "[Informix][Odbc Informix Driver][Informix] ORDER > BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc Informix > Driver][Informix]A syntax error has occurred.". > > If the table is linked in non-updateable mode i.e. without a unique field, > then you are able to sort. Filters work in either case. > > Has anyone seen this before? A can't believe this doesn't work! > > Here is are the details of my config: > Backend: > HP-UX helpful B.10.20 A 9000/877 > INFORMIX-OnLine Version 7.20.UC2 > > ODBC: > INFORMIX 2.80 32 BIT 2.80.0004 > Configuration: > Data Source Name : Year 2000 > Database: y2k > Server: Helpful > Host: helpful > service: sqlturbo > Protocol: onsoctcp > > MS Access: > Microsoft Access 97 SR-1 > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own You can change the property of the query in DB-Access from dynaset to snapshot.Then the query is no longer updateable. -- Klaus Gotthardt, Johann Wolfgang Goethe-Universität,Verwaltungsdatenverarbeitung Email: K.Gotthardt@em.uni-frankfurt.de Tel:+49 (0)69-798-23380 Fax:+49 (0)69-798-23381 pgp-key: http://www.rz.uni-frankfurt.de/~gotthard/KLAUS_GOTTHARDT.PGP
Hi,
This is a Microsoft issue and they have to clear it out. The Informix bug is
86030. The reason you get this error is because on a query in which the user would
select lname from customer order by lname, Access changes to select customer_num
from customer order by lname. The ODBC specification has a function namedSQLGetInfo() that allows the program to gather information about the database that
it is connected to. One of the pieces of information that can be gathered is
whether or not the database server requires the column in the order by to be in
the select list.
The arguement to SQLGetInfo for this information is SQLL_ORDER_BY_COLUMNS_
IN_SELECT. Informix has confirmed that the correct value is returned in this call.
Therefore, the select statement that is being issued should be expected to come
back with a 309 error and this is expected behaviour as far as Informix goes.
Possible workarounds:
1. Write pass-through SQL in Access, i.e. hand code the statement.
This works, but the end user loses almost all the benefits of using Access
as a front end.
2. One of the messages cited below suggested using a win.ini parameter to set
SnapshotOnly to true. The modern equivalent is to edit the registry:
HKEY: HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Jet\\3.5\\Engines\\ODBC
KEY: SnapshotOnly
KEY_TYPE REG_DWORD
VALUE: 1
However this DID NOT WORK.
3. Add the offending column to the select list.
At first this seems to work, in that the query runs without getting the error and
produces the correctly rows. However this workaround exposes another bug in
Access. If some other column is added to the query, and included in the Access
sort by row, the result is NOT sorted by that column. An example of the workaround
. again expressed in SQL, is: select b.b2, b.f from a, b where a.f = b.f
Hope this points you in the Bill Gates direction ...... !!
PK
> have an MS-Access front end using an ODBC link to an Informix backend and
> here is my problem:
>
> Attempting to sort an updateable linked table using MS access to Informix back
> end results in an ODBC error "[Informix][Odbc Informix Driver][Informix] ORDER
> BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc Informix
> Driver][Informix]A syntax error has occurred.".
>
> If the table is linked in non-updateable mode i.e. without a unique field,
> then you are able to sort. Filters work in either case.
>
> Has anyone seen this before? A can't believe this doesn't work!
>
> Here is are the details of my config:
> Backend:
> HP-UX helpful B.10.20 A 9000/877
> INFORMIX-OnLine Version 7.20.UC2
>
> ODBC:
> INFORMIX 2.80 32 BIT 2.80.0004
> Configuration:
> Data Source Name : Year 2000
> Database: y2k
> Server: Helpful
> Host: helpful
> service: sqlturbo
> Protocol: onsoctcp
>
> MS Access:
> Microsoft Access 97 SR-1
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Two other work-arounds that I've discovered are:
4) Ask access to only retrieve the top 100% of records
5) Upgrade your ODBC driver to v3.11 to enable you to use the following
workaround ... from
http://www.intersolv.com/datadirect/download/docs/odbc/Readme/95ntread.htm
WorkArounds=1073741824. Microsoft Access assumes that ORDER BY columns
do not have to be in the SELECT list. This workaround addresses that
mistaken assumption for data sources such as INFORMIX and OpenIngres.
Stephen.
Pradeep Kutty <pkutty@informix.com> wrote in message
news:36F65664.25DD83BE@informix.com...
> Hi,
> This is a Microsoft issue and they have to clear it out. The Informix
bug is
> 86030. The reason you get this error is because on a query in which the
user would
> select lname from customer order by lname, Access changes to selectcustomer_num
> from customer order by lname. The ODBC specification has a function named
> SQLGetInfo() that allows the program to gather information about the
database that
> it is connected to. One of the pieces of information that can be gathered
is
> whether or not the database server requires the column in the order by to
be in
> the select list.
> The arguement to SQLGetInfo for this information is SQLL_ORDER_BY_COLUMNS_
> IN_SELECT. Informix has confirmed that the correct value is returned in
this call.
> Therefore, the select statement that is being issued should be expected to
come
> back with a 309 error and this is expected behaviour as far as Informix
goes.
>
> Possible workarounds:
> 1. Write pass-through SQL in Access, i.e. hand code the statement.
> This works, but the end user loses almost all the benefits of using Access
> as a front end.
>
> 2. One of the messages cited below suggested using a win.ini parameter to
set
> SnapshotOnly to true. The modern equivalent is to edit the registry:
> HKEY: HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Jet\\3.5\\Engines\\ODBC
> KEY: SnapshotOnly
> KEY_TYPE REG_DWORD
> VALUE: 1
>
> However this DID NOT WORK.
>
> 3. Add the offending column to the select list.
> At first this seems to work, in that the query runs without getting the
error and
> produces the correctly rows. However this workaround exposes another bug
in
> Access. If some other column is added to the query, and included in the
Access
> sort by row, the result is NOT sorted by that column. An example of the
workaround
> . again expressed in SQL, is: select b.b2, b.f from a, b where a.f = b.f
>
> Hope this points you in the Bill Gates direction ...... !!
>
> PK
>
> > have an MS-Access front end using an ODBC link to an Informix backend
and
> > here is my problem:
> >
> > Attempting to sort an updateable linked table using MS access to
Informix back
> > end results in an ODBC error "[Informix][Odbc Informix Driver][Informix]
ORDER
> > BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc
Informix
> > Driver][Informix]A syntax error has occurred.".
> >
> > If the table is linked in non-updateable mode i.e. without a unique
field,
> > then you are able to sort. Filters work in either case.
> >
> > Has anyone seen this before? A can't believe this doesn't work!
> >
> > Here is are the details of my config:
> > Backend:
> > HP-UX helpful B.10.20 A 9000/877
> > INFORMIX-OnLine Version 7.20.UC2
> >
> > ODBC:
> > INFORMIX 2.80 32 BIT 2.80.0004
> > Configuration:
> > Data Source Name : Year 2000
> > Database: y2k
> > Server: Helpful
> > Host: helpful
> > service: sqlturbo
> > Protocol: onsoctcp
> >
> > MS Access:
> > Microsoft Access 97 SR-1
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Any one from Microsoft want to chime in here?........
In article <7d7r4q$pr$1@newsource.ihug.co.nz>,
"Stephen Denne" <spdenne@toyota.co.nz> wrote:
> Two other work-arounds that I've discovered are:
>
> 4) Ask access to only retrieve the top 100% of records
>
> 5) Upgrade your ODBC driver to v3.11 to enable you to use the following
> workaround ... from
> http://www.intersolv.com/datadirect/download/docs/odbc/Readme/95ntread.htm
>
> WorkArounds=1073741824. Microsoft Access assumes that ORDER BY columns
> do not have to be in the SELECT list. This workaround addresses that
> mistaken assumption for data sources such as INFORMIX and OpenIngres.
>
> Stephen.
>
> Pradeep Kutty <pkutty@informix.com> wrote in message
> news:36F65664.25DD83BE@informix.com...
> > Hi,
> > This is a Microsoft issue and they have to clear it out. The Informix
> bug is
> > 86030. The reason you get this error is because on a query in which the
> user would
> > select lname from customer order by lname, Access changes to select> customer_num
> > from customer order by lname. The ODBC specification has a function named
> > SQLGetInfo() that allows the program to gather information about the
> database that
> > it is connected to. One of the pieces of information that can be gathered
> is
> > whether or not the database server requires the column in the order by to
> be in
> > the select list.
> > The arguement to SQLGetInfo for this information is SQLL_ORDER_BY_COLUMNS_
> > IN_SELECT. Informix has confirmed that the correct value is returned in
> this call.
> > Therefore, the select statement that is being issued should be expected to
> come
> > back with a 309 error and this is expected behaviour as far as Informix
> goes.
> >
> > Possible workarounds:
> > 1. Write pass-through SQL in Access, i.e. hand code the statement.
> > This works, but the end user loses almost all the benefits of using Access
> > as a front end.
> >
> > 2. One of the messages cited below suggested using a win.ini parameter to
> set
> > SnapshotOnly to true. The modern equivalent is to edit the registry:
> > HKEY: HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Jet\\3.5\\Engines\\ODBC
> > KEY: SnapshotOnly
> > KEY_TYPE REG_DWORD
> > VALUE: 1
> >
> > However this DID NOT WORK.
> >
> > 3. Add the offending column to the select list.
> > At first this seems to work, in that the query runs without getting the
> error and
> > produces the correctly rows. However this workaround exposes another bug
> in
> > Access. If some other column is added to the query, and included in the
> Access
> > sort by row, the result is NOT sorted by that column. An example of the
> workaround
> > . again expressed in SQL, is: select b.b2, b.f from a, b where a.f = b.f
> >
> > Hope this points you in the Bill Gates direction ...... !!
> >
> > PK
> >
> > > have an MS-Access front end using an ODBC link to an Informix backend
> and
> > > here is my problem:
> > >
> > > Attempting to sort an updateable linked table using MS access to
> Informix back
> > > end results in an ODBC error "[Informix][Odbc Informix Driver][Informix]
> ORDER
> > > BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc
> Informix
> > > Driver][Informix]A syntax error has occurred.".
> > >
> > > If the table is linked in non-updateable mode i.e. without a unique
> field,
> > > then you are able to sort. Filters work in either case.
> > >
> > > Has anyone seen this before? A can't believe this doesn't work!
> > >
> > > Here is are the details of my config:
> > > Backend:
> > > HP-UX helpful B.10.20 A 9000/877
> > > INFORMIX-OnLine Version 7.20.UC2
> > >
> > > ODBC:
> > > INFORMIX 2.80 32 BIT 2.80.0004
> > > Configuration:
> > > Data Source Name : Year 2000
> > > Database: y2k
> > > Server: Helpful
> > > Host: helpful
> > > service: sqlturbo
> > > Protocol: onsoctcp
> > >
> > > MS Access:
> > > Microsoft Access 97 SR-1
> > >
> > > -----------== Posted via Deja News, The Discussion Network ==----------
> > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
>
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own