CLIENT_LOCALE has no effect

Hi all, may be I am wrong, but it seems thta the environment-variable CLIENT_LOCALE has no effect for the ORDER BY clause of a SELECT-Statement. I created a database using DB_LOCALE=en_us.8859-1 and CLIENT_LOCALE=en_us.8859-1 as follows: create database test; create table test(name1 CHAR(150), name2 NCHAR(150)); insert into test VALUES("A", "A"); insert into test VALUES("'", "'"); insert into test VALUES("Z", "Z"); SELECT * FROM test order by name2; <returns> name1 A name2 A name1 Z name2 Z name1 ' name2 ' This result is OK, because CLIENT_LOCALE was set to en_us.8859-1. But setting the CLIENT_LOCALE to de_de.8895-1, I got the same result. In german order, the result should be A - ' - Z. When creating the database with DB_LOCALE=de_de.8895-1, I got name1 A name2 A name1 ' name2 ' name1 Z name2 Z This is the correct german order. It seems that only the DB_LOCALE, set when creating a database, affects the ORDER. Isn't it possible, to get different orders when using different CLIENT_LOCALE's Thanks in advance! Bye Markus

CLIENT_LOCALE dos not influence ORDER BY clause! It is merely responsible for proper translation of result and server does not know anything about the client locale. It is the DB_LOCALE that defines ordering or better yet locale under which database was created. Gorazd "Markus Bschorer" <mb@worxbox.com> wrote in message news:bl1acq$ksq$07$1@news.t-online.com... > Hi all, > > may be I am wrong, but it seems thta the environment-variable CLIENT_LOCALE > has no effect for the ORDER BY clause of a SELECT-Statement. > > I created a database using DB_LOCALE=en_us.8859-1 and > CLIENT_LOCALE=en_us.8859-1 as follows:> > create database test;> > create table test(name1 CHAR(150), name2 NCHAR(150));> > insert into test VALUES("A", "A"); > insert into test VALUES("'", "'"); > insert into test VALUES("Z", "Z");> > SELECT * FROM test > order by name2;> > <returns> > > name1 A > name2 A > > name1 Z > name2 Z > > name1 ' > name2 ' > > This result is OK, because CLIENT_LOCALE was set to en_us.8859-1. > But setting the CLIENT_LOCALE to de_de.8895-1, I got the same result. In > german order, the result should be A - ' - Z. > > When creating the database with DB_LOCALE=de_de.8895-1, I got > > name1 A > name2 A > > name1 ' > name2 ' > > name1 Z > name2 Z > > This is the correct german order. It seems that only the DB_LOCALE, set when > creating a database, affects the ORDER. > > Isn't it possible, to get different orders when using different > CLIENT_LOCALE's > > Thanks in advance! > > Bye > Markus > > > >

Hi Gorazd, thanks for your answer. Does that mean, that it is not possible to setup a Informix-Database with different sort-orders for different clients? Markus "Gorazd Hribar Rajteric" <REMOVE_gorazd.hribar@telekom.si> schrieb im Newsbeitrag news:leWcb.3950$2B6.685916@news.siol.net... > CLIENT_LOCALE dos not influence ORDER BY clause! It is merely responsible > for proper translation of result and server does not know anything about the > client locale. It is the DB_LOCALE that defines ordering or better yet > locale under which database was created. > > Gorazd > > "Markus Bschorer" <mb@worxbox.com> wrote in message > news:bl1acq$ksq$07$1@news.t-online.com... > > Hi all, > > > > may be I am wrong, but it seems thta the environment-variable > CLIENT_LOCALE > > has no effect for the ORDER BY clause of a SELECT-Statement. > > > > I created a database using DB_LOCALE=en_us.8859-1 and > > CLIENT_LOCALE=en_us.8859-1 as follows:> > > > create database test;> > > > create table test(name1 CHAR(150), name2 NCHAR(150));> > > > insert into test VALUES("A", "A"); > > insert into test VALUES("'", "'"); > > insert into test VALUES("Z", "Z");> > > > SELECT * FROM test > > order by name2;> > > > <returns> > > > > name1 A > > name2 A > > > > name1 Z > > name2 Z > > > > name1 ' > > name2 ' > > > > This result is OK, because CLIENT_LOCALE was set to en_us.8859-1. > > But setting the CLIENT_LOCALE to de_de.8895-1, I got the same result. In > > german order, the result should be A - ' - Z. > > > > When creating the database with DB_LOCALE=de_de.8895-1, I got > > > > name1 A > > name2 A > > > > name1 ' > > name2 ' > > > > name1 Z > > name2 Z > > > > This is the correct german order. It seems that only the DB_LOCALE, set > when > > creating a database, affects the ORDER. > > > > Isn't it possible, to get different orders when using different > > CLIENT_LOCALE's > > > > Thanks in advance! > > > > Bye > > Markus > > > > > > > > >

I have been wrong for a couple of times, but I think that is correct. Note that I only have experience with IDS 7.2x and 7.3x and I don't know for other versions! Gorazd "Markus Bschorer" <mb@worxbox.com> wrote in message news:bl1cgo$qvj$01$1@news.t-online.com... > Hi Gorazd, > > thanks for your answer. > Does that mean, that it is not possible to setup a Informix-Database with > different sort-orders for different clients? > > Markus > > "Gorazd Hribar Rajteric" <REMOVE_gorazd.hribar@telekom.si> schrieb im > Newsbeitrag news:leWcb.3950$2B6.685916@news.siol.net... > > CLIENT_LOCALE dos not influence ORDER BY clause! It is merely responsible > > for proper translation of result and server does not know anything about > the > > client locale. It is the DB_LOCALE that defines ordering or better yet > > locale under which database was created. > > > > Gorazd > > > > "Markus Bschorer" <mb@worxbox.com> wrote in message > > news:bl1acq$ksq$07$1@news.t-online.com... > > > Hi all, > > > > > > may be I am wrong, but it seems thta the environment-variable > > CLIENT_LOCALE > > > has no effect for the ORDER BY clause of a SELECT-Statement. > > > > > > I created a database using DB_LOCALE=en_us.8859-1 and > > > CLIENT_LOCALE=en_us.8859-1 as follows:> > > > > > create database test;> > > > > > create table test(name1 CHAR(150), name2 NCHAR(150));> > > > > > insert into test VALUES("A", "A"); > > > insert into test VALUES("'", "'"); > > > insert into test VALUES("Z", "Z");> > > > > > SELECT * FROM test > > > order by name2;> > > > > > <returns> > > > > > > name1 A > > > name2 A > > > > > > name1 Z > > > name2 Z > > > > > > name1 ' > > > name2 ' > > > > > > This result is OK, because CLIENT_LOCALE was set to en_us.8859-1. > > > But setting the CLIENT_LOCALE to de_de.8895-1, I got the same result. In > > > german order, the result should be A - ' - Z. > > > > > > When creating the database with DB_LOCALE=de_de.8895-1, I got > > > > > > name1 A > > > name2 A > > > > > > name1 ' > > > name2 ' > > > > > > name1 Z > > > name2 Z > > > > > > This is the correct german order. It seems that only the DB_LOCALE, set > > when > > > creating a database, affects the ORDER. > > > > > > Isn't it possible, to get different orders when using different > > > CLIENT_LOCALE's > > > > > > Thanks in advance! > > > > > > Bye > > > Markus > > > > > > > > > > > > > > > >

9.40 has "SET COLLATION" at session level. But it only applies to NVARCHAR and NCHAR and it has some (obvious) limitations. Regards. Gorazd Hribar Rajteric wrote: > I have been wrong for a couple of times, but I think that is correct. Note > that I only have experience with IDS 7.2x and 7.3x and I don't know for > other versions! > > Gorazd > > "Markus Bschorer" <mb@worxbox.com> wrote in message > news:bl1cgo$qvj$01$1@news.t-online.com... > >>Hi Gorazd, >> >>thanks for your answer. >>Does that mean, that it is not possible to setup a Informix-Database with >>different sort-orders for different clients? >> >>Markus >> >>"Gorazd Hribar Rajteric" <REMOVE_gorazd.hribar@telekom.si> schrieb im >>Newsbeitrag news:leWcb.3950$2B6.685916@news.siol.net... >> >>>CLIENT_LOCALE dos not influence ORDER BY clause! It is merely > > responsible > >>>for proper translation of result and server does not know anything about >> >>the >> >>>client locale. It is the DB_LOCALE that defines ordering or better yet >>>locale under which database was created. >>> >>>Gorazd >>> >>>"Markus Bschorer" <mb@worxbox.com> wrote in message >>>news:bl1acq$ksq$07$1@news.t-online.com... >>> >>>>Hi all, >>>> >>>>may be I am wrong, but it seems thta the environment-variable >>> >>>CLIENT_LOCALE >>> >>>>has no effect for the ORDER BY clause of a SELECT-Statement. >>>> >>>>I created a database using DB_LOCALE=en_us.8859-1 and >>>>CLIENT_LOCALE=en_us.8859-1 as follows:>>>> >>>>create database test;>>>> >>>>create table test(name1 CHAR(150), name2 NCHAR(150));>>>> >>>>insert into test VALUES("A", "A"); >>>>insert into test VALUES("'", "'"); >>>>insert into test VALUES("Z", "Z");>>>> >>>>SELECT * FROM test >>>>order by name2;>>>> >>>><returns> >>>> >>>>name1 A >>>>name2 A >>>> >>>>name1 Z >>>>name2 Z >>>> >>>>name1 ' >>>>name2 ' >>>> >>>>This result is OK, because CLIENT_LOCALE was set to en_us.8859-1. >>>>But setting the CLIENT_LOCALE to de_de.8895-1, I got the same result. > > In > >>>>german order, the result should be A - ' - Z. >>>> >>>>When creating the database with DB_LOCALE=de_de.8895-1, I got >>>> >>>>name1 A >>>>name2 A >>>> >>>>name1 ' >>>>name2 ' >>>> >>>>name1 Z >>>>name2 Z >>>> >>>>This is the correct german order. It seems that only the DB_LOCALE, > > set > >>>when >>> >>>>creating a database, affects the ORDER. >>>> >>>>Isn't it possible, to get different orders when using different >>>>CLIENT_LOCALE's >>>> >>>>Thanks in advance! >>>> >>>>Bye >>>>Markus >>>> >>>> >>>> >>>> >>> >> >

Markus Bschorer wrote: > Hi Gorazd, > > thanks for your answer. > Does that mean, that it is not possible to setup a Informix-Database with > different sort-orders for different clients? Gorazd responded accurately for IDS 7.31; indeed, also for any version of IDS prior to IDS 9.40. With IDS 9.40, you can choose different collating sequences (via locale names) at run time. There are implications for optimization and indexes - RTF(abulous)M. > "Gorazd Hribar Rajteric" <REMOVE_gorazd.hribar@telekom.si> schrieb: >> CLIENT_LOCALE dos not influence ORDER BY clause! It is merely >> responsible for proper translation of result and server does not >> know anything about the client locale. It is the DB_LOCALE that >> defines ordering or better yet locale under which database was >> created. >> >>Gorazd >> >>"Markus Bschorer" <mb@worxbox.com> wrote: >>> may be I am wrong, but it seems thta the environment-variable >>> CLIENT_LOCALE has no effect for the ORDER BY clause of a >>> SELECT-Statement. ... >>> This is the correct german order. It seems that only the >>> DB_LOCALE, set when creating a database, affects the ORDER. >>> Isn't it possible, to get different orders when using different >>> CLIENT_LOCALE's -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/