MS Access to Informix via ODBC - sorting problem

Greetings! I am a newcomer to the list. I have encountered a problem with Informix with which I would appreciate your help. I am running Informix 7.2 se on a RedHat Linux 5.2 platform and am using Informix Client SDK V2.20 TC1-1 on Windows 95/98. I am attempting to write an application using Microsoft Access and accessing the Informix database via the ODBC drivers in the the SDK package. Both the SE and the SDK were obtained from the Informix Web page and downloaded in their try-and-buy offer. I have been using Informix for a number of years on SCO Unix, but have not yet upgraded on the SCO platform to a version supporting the current connectivity methods. My connection protocol is 'sesoctcp'. I have examined the FAQ webpage that I obtained from this list and have found nothing so far. I did not find a FAQ for the Linux-Informix list. I am able to connect and access the tables with no problem. The problem occurs when I try to do a sort on any field that is not part of the primary key. I get an 'ODBC failed' with an error message stating that the field that I am trying to sort on must be included in the 'select list'. I have tried this with both the query design and using the sort icon in the toolbar, On examining the SQL in the query, it is obvious that the 'order by' field is indeed included in the 'select list'. I have tried this on indexed (on the Informix side) fields as well as un-indexed. 1) Is this an actual, permanent limitation on the use of ODBC? 2) Are there problems with the ODBC drivers. 3) Is a problem with Linux? (Using SQL on the Linux platform and the identical protocol works perfectly) 4) Is it a problem with MS Access. (I have not yet tried this with Visual Basic). I would appreciate any help with this problem. You may respond to the list or personally to me at either: dew1550@dhr.state.ga.us or dwrighsr@alltel.net David Wright Programmer/Analyst N.W. Health District Georgia State Dept. of Public Health Dalton, GA 30720 Phone:706-272-2342 Fax: 706-272-2221

David Wright wrote: > > Greetings! I am a newcomer to the list. I have encountered a problem with > Informix with which I would appreciate your help. I am running Informix 7.2 > se on a RedHat Linux 5.2 platform and am using Informix Client SDK V2.20 > TC1-1 on Windows 95/98. I am attempting to write an application using > Microsoft Access and accessing the Informix database via the ODBC drivers in > the the SDK package. Both the SE and the SDK were obtained from the Informix > Web page and downloaded in their try-and-buy offer. I have been using > Informix for a number of years on SCO Unix, but have not yet upgraded on the > SCO platform to a version supporting the current connectivity methods. > > My connection protocol is 'sesoctcp'. > > I have examined the FAQ webpage that I obtained from this list and have > found nothing so far. I did not find a FAQ for the Linux-Informix list. > > I am able to connect and access the tables with no problem. The problem > occurs when I try to do a sort on any field that is not part of the primary > key. I get an 'ODBC failed' with an error message stating that the field > that I am trying to sort on must be included in the 'select list'. That's a standard Informix limitation, permitted in SQL-89 (and, I believe, SQL-92 though I'm not so sure of that). If you wish to sort by the column, it must appear in the select list. Period. You can discard it, but you must select it. Some version of the Informix engines will drop that restriction; it won't be dropped in SE. The version might be 7.30, or it might be the Centaur release due out later this year, or it might be still later. Most other database allow you to get away with not selecting your sort columns. Incidentally, the theoretical reason for this restriction is that the output from the SELECT statement should be a relation, and relations have no essential ordering, meaning you can look at the data and determine how/why it was ordered thusly. If you sort by a non-selected column, you have essential ordering - the order depends on invisible information. (Yes, there are endless other breakages of the relational rules; ask Mr Date sometime). > I have > tried this with both the query design and using the sort icon in the > toolbar, On examining the SQL in the query, it is obvious that the 'order > by' field is indeed included in the 'select list'. I have tried this on > indexed (on the Informix side) fields as well as un-indexed. > > 1) Is this an actual, permanent limitation on the use of ODBC? Yes while you're using SE; the limitation is imposed by the database, though. > 2) Are there problems with the ODBC drivers. No. > 3) Is a problem with Linux? (Using SQL on the Linux platform and the > identical protocol works perfectly) No. > 4) Is it a problem with MS Access. (I have not yet tried this with Visual > Basic). Sort of, maybe. Mostly in the way the SQL was written, but did you write it or did Access do the writing for you. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

David Wright wrote in message <7ermon$10l$1@news.xmission.com>... > >Greetings! I am a newcomer to the list. I have encountered a problem with >Informix with which I would appreciate your help. I am running Informix 7.2 >se on a RedHat Linux 5.2 platform and am using Informix Client SDK V2.20 >TC1-1 on Windows 95/98. I am attempting to write an application using >Microsoft Access and accessing the Informix database via the ODBC drivers in >the the SDK package. Both the SE and the SDK were obtained from the Informix >Web page and downloaded in their try-and-buy offer. I have been using >Informix for a number of years on SCO Unix, but have not yet upgraded on the >SCO platform to a version supporting the current connectivity methods. > >My connection protocol is 'sesoctcp'. > >I have examined the FAQ webpage that I obtained from this list and have >found nothing so far. I did not find a FAQ for the Linux-Informix list. > >I am able to connect and access the tables with no problem. The problem >occurs when I try to do a sort on any field that is not part of the primary >key. I get an 'ODBC failed' with an error message stating that the field >that I am trying to sort on must be included in the 'select list'. I have >tried this with both the query design and using the sort icon in the >toolbar, On examining the SQL in the query, it is obvious that the 'order >by' field is indeed included in the 'select list'. I have tried this on >indexed (on the Informix side) fields as well as un-indexed. > >1) Is this an actual, permanent limitation on the use of ODBC? > >2) Are there problems with the ODBC drivers. > >3) Is a problem with Linux? (Using SQL on the Linux platform and the >identical protocol works perfectly) > >4) Is it a problem with MS Access. (I have not yet tried this with Visual >Basic). > >I would appreciate any help with this problem. You may respond to the list >or personally to me at either: > >dew1550@dhr.state.ga.us or >dwrighsr@alltel.net > >David Wright >Programmer/Analyst >N.W. Health District >Georgia State Dept. of Public Health >Dalton, GA 30720 >Phone:706-272-2342 >Fax: 706-272-2221 > Try reading this read.me (it was read.me for older intersolv odbc) and apply WorkArounds=1073741824 Best regards

I have noticed that the Merant Informix driver has a workaround for this problem. You can find this documented in the readme that comes with the driver. Toffie. In article <371173A3.11D9@earthlink.net>, jleffler@earthlink.net wrote: > David Wright wrote: > > > > Greetings! I am a newcomer to the list. I have encountered a problem with > > Informix with which I would appreciate your help. I am running Informix 7.2 > > se on a RedHat Linux 5.2 platform and am using Informix Client SDK V2.20 > > TC1-1 on Windows 95/98. I am attempting to write an application using > > Microsoft Access and accessing the Informix database via the ODBC drivers in > > the the SDK package. Both the SE and the SDK were obtained from the Informix > > Web page and downloaded in their try-and-buy offer. I have been using > > Informix for a number of years on SCO Unix, but have not yet upgraded on the > > SCO platform to a version supporting the current connectivity methods. > > > > My connection protocol is 'sesoctcp'. > > > > I have examined the FAQ webpage that I obtained from this list and have > > found nothing so far. I did not find a FAQ for the Linux-Informix list. > > > > I am able to connect and access the tables with no problem. The problem > > occurs when I try to do a sort on any field that is not part of the primary > > key. I get an 'ODBC failed' with an error message stating that the field > > that I am trying to sort on must be included in the 'select list'. > > That's a standard Informix limitation, permitted in SQL-89 (and, I > believe, > SQL-92 though I'm not so sure of that). If you wish to sort by the > column, > it must appear in the select list. Period. You can discard it, but you > must > select it.> > Some version of the Informix engines will drop that restriction; it > won't > be dropped in SE. The version might be 7.30, or it might be the Centaur > release due out later this year, or it might be still later. Most other > database allow you to get away with not selecting your sort columns. > > Incidentally, the theoretical reason for this restriction is that the > output from the SELECT statement should be a relation, and relations > have no essential ordering, meaning you can look at the data and > determine > how/why it was ordered thusly. If you sort by a non-selected column, > you > have essential ordering - the order depends on invisible information. > (Yes, there are endless other breakages of the relational rules; ask > Mr Date sometime). > > > I have > > tried this with both the query design and using the sort icon in the > > toolbar, On examining the SQL in the query, it is obvious that the 'order > > by' field is indeed included in the 'select list'. I have tried this on > > indexed (on the Informix side) fields as well as un-indexed. > > > > 1) Is this an actual, permanent limitation on the use of ODBC? > > Yes while you're using SE; the limitation is imposed by the database, > though. > > > 2) Are there problems with the ODBC drivers. > > No. > > > 3) Is a problem with Linux? (Using SQL on the Linux platform and the > > identical protocol works perfectly) > > No. > > > 4) Is it a problem with MS Access. (I have not yet tried this with Visual > > Basic). > > Sort of, maybe. Mostly in the way the SQL was written, but did you > write it > or did Access do the writing for you. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h> > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

I think after having created index on the column in Informix database will solve your problem. But you have to detach the table and attach them again to take effect. David Wright wrote in message <7ermon$10l$1@news.xmission.com>... > >Greetings! I am a newcomer to the list. I have encountered a problem with >Informix with which I would appreciate your help. I am running Informix 7.2 >se on a RedHat Linux 5.2 platform and am using Informix Client SDK V2.20 >TC1-1 on Windows 95/98. I am attempting to write an application using >Microsoft Access and accessing the Informix database via the ODBC drivers in >the the SDK package. Both the SE and the SDK were obtained from the Informix >Web page and downloaded in their try-and-buy offer. I have been using >Informix for a number of years on SCO Unix, but have not yet upgraded on the >SCO platform to a version supporting the current connectivity methods. > >My connection protocol is 'sesoctcp'. > >I have examined the FAQ webpage that I obtained from this list and have >found nothing so far. I did not find a FAQ for the Linux-Informix list. > >I am able to connect and access the tables with no problem. The problem >occurs when I try to do a sort on any field that is not part of the primary >key. I get an 'ODBC failed' with an error message stating that the field >that I am trying to sort on must be included in the 'select list'. I have >tried this with both the query design and using the sort icon in the >toolbar, On examining the SQL in the query, it is obvious that the 'order >by' field is indeed included in the 'select list'. I have tried this on >indexed (on the Informix side) fields as well as un-indexed. > >1) Is this an actual, permanent limitation on the use of ODBC? > >2) Are there problems with the ODBC drivers. > >3) Is a problem with Linux? (Using SQL on the Linux platform and the >identical protocol works perfectly) > >4) Is it a problem with MS Access. (I have not yet tried this with Visual >Basic). > >I would appreciate any help with this problem. You may respond to the list >or personally to me at either: > >dew1550@dhr.state.ga.us or >dwrighsr@alltel.net > >David Wright >Programmer/Analyst >N.W. Health District >Georgia State Dept. of Public Health >Dalton, GA 30720 >Phone:706-272-2342 >Fax: 706-272-2221 >