Re: GLS Databases

Vincent Birlouez wrote: > > The document I was talking about is "Guide to GLS functionality", page 3-23. > I understood from that document that the "IN('feron')" comparisons should > return feron and f+AfI-on (is e and +AaA-are considered are equivalent). > Any other clues ? The document your are talking about is correct in this case, and the engine's behavior is also correct. I will quote the mentioned section on page 3-28 to clarify things: ... IN Conditions An IN condition is satisfied when the expression to the left of the IN keyword is included in the parenthetical list of values to the right of the keyword. The following SELECT statement assumes a nondefault locale and uses an IN condition to retrieve only those rows in which the value of the nom column is any of the following: Azevedo, Llanero, or Oatfield. SELECT num+jG8-,nom,pr+i28-m FROM abonn+jE0- WHERE nom IN ('Azevedo', 'Llanero', 'Oatfield'); The query result depends on whether nom is a CHAR or NCHAR column. If nom is a CHAR column, the database server uses code-set order (see Figure 3-2 on page 3-22). The database server retrieves rows in which the value of nom is Azevedo, but not rows in which the value of nom is azevedo or +ATo-evedo because the characters A, a, and +AOA-are not equivalent in the code-set order (see Figure 3-2 on page 3-22). The query also returns rows with the nom values of Llanero and Oatfield. However, if nom is an NCHAR column, the database server uses localized order (see Figure 3-3 on page 3-23) to sort the rows. If the locale ^^^^^^^^^^^^^ defines A, a, and +AOA-as equivalent characters in the localized order, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the query returns rows in which the value of nom is Azevedo, azevedo, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ or +ATo-evedo. The same selection rule applies to the other names in the ^^^^^^^^^^^ parenthetical list that follows the IN keyword. ... And, what does it mean? I must quote another section from the same document, page A-6: ... The COLLATION Category The COLLATION category defines the localized order. When an Informix product needs to compare two strings, it first breaks up the strings into a series of collation elements. The database server compares each pair of collation elements according to the collation weights of each element. The COLLATION category provides support for the following capabilities: - Multicharacter collation elements define characters that the database server should collate as a single unit. For example, the localized order might treat the Spanish double-l (ll) as a single collation element instead of a pair of l+AJI-s. - Equivalence classes assign the same collation weight to different ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ collation elements. For example, the localized order might specify ^^^^^^^^^^^^^^^^^^ that a and A are an equivalence class (a and A are equivalent characters). ... If you take a look at your locale definitions for fr_be.8859-1, file INFORMIXDIR/gls/lc11/fr_be/0333.lc, you will see under section LC_COLLATE the following: ############# LC_COLLATE ############# ... <e> <E> <e-acute> <E-acute> <e-grave> <E-grave> <e-circumflex> <E-circumflex> <e-diaresis> <E-diaresis> ... That means that all of these characters are treated as different characters with different collation weights, so the engine's behavior is quite correct. AFAIK, none of the currently supplied standard GLS locales uses equivalence classes for collating. Probably because national languages treats all of their characters (including accented ones) as different. Informix only exploits this technique in GLS locales supplied to support old NLS databases build with OS LC_* locales to be handled by new engines. Take a look at *.lc files in your INFORMIXDIR/gls/lc11/os directory to see how collating-symbols with different collation weights are defined, and how those symbols are assigned to specific characters. But, if you want behavior like one described on the quoted page from the manual, you may order a special locale from Informix that will be build only for you. There is standard procedure for that. Contact your Informix representative and you will receive a questionnaire to fill with all needed information about your special locale. > > June Tong wrote in message <36071C14.58BBE465@hotmail.com>... > >Vincent Birlouez wrote: > > > >> I set up my database to support french sorting order (using DB_LOCALE and > >> CLIENT_LOCALE, I set them to fr_be.8859-1). I'm using informix 7.24 > >> > >> It work fine for sorting order (F+AfI-on is coming right after Feron). > >> > >> But, when I execute the following query : > >> select * from tab1 > >> where ncharcolumn in ('feron')> >> I only get the rows where ncharcolumn = feron. I was expecting > >> informix to return feron, f+AfI-on and Feron, as written in the GLS guide > >> (3-28). > > > >Hmmm. I don't know if I've ever seen the GLS guide, but I'm thinking I > might > >have to get my hands on one. I have been told by Informix development that > >what you are seeing is expected behavior; that is, that comparisons like: > > WHERE ncharcolumn = 'feron' > >*should* only return 'feron', and *not* 'f+AfI-on'. This was a really huge > >sticking point with me. I would really love to see documentation that > >contradicts this... (Clem... ?) Of course, they will probably just say > the > >documentation is incorrect. The documentation group follows what PD says, > >not (usually) the other way around. > > > >I am also very interested in the opinions of anyone whose native language > >includes accented letters, such as +Aaw- on whether they think it *should* > test > >true. If your users typed 'fe*' into a form, would they expect to get > >'f+AfI-on', or not? This only applies to languages where the accented letter > is > >equivalent to the unaccented letter, such as French or Spanish, not to > >languages which contain additional letters which are treated differently, > >e.g. the Swedish +AS4- I don't know, my users only uses plain Latin and Cyrillic characters :) > > > >June > >-- > >june_t@hotmail.com > >Lost in the wilds of Palo Alto, living on Mrs. Fields' chocolate chip > cookies > > > > > > HTH, Mladen -- ---------------------------------------------------------------------- Mladen Jovanovski E-mail: mladen@ultra.com.mk ULTRA Computing, Ltd. Phone/Fax: +-389 91 36 26 36 Partizanski odredi 70b P.O.Box 798 91000 Skopje Macedonia, Europe ----------------------------------------------------------------------