Re: Order by (Values above 127 ASCII)

>From: natal@telepac.pt (Joao Natal) >Date: 18 Apr 1995 21:31:42 GMT >X-Informix-List-Id: <news.13141> > >Do you out there know how does Informix-4GL (or for that matter any other >Informix product) order values above 127 ASCII ? Such a deceptively simple question! Actually, it is a complex question with a multi-versioned answer... Let's deal with the easiest bits first -- not that they're particularly easy. In 4.1x I4GL with 4.1x Engines, the answer is that the characters are collated in native sorting order as defined by the C routine strcmp(). This takes the native characters and does the comparison, so depending on the platform, the characters above 127 could be regarded as signed or unsigned, leading to different collating sequences. I don't think there is any attempt to ensure that the comparison is of unsigned characters, though on most machines, the characters are treated as unsigned. The 5.0x Engines use the same comparison techniques, so the result is the same when 5.0x Engines are used with 4.1x I4GL. Now let's get trickier and consider 6.0x I4GL with 6.0 or 7.x Engines. The products are NLS-aware. That means that they pay attention to the LANG and LC_COLLATE environment variables, but only if the DBNLS and COLLCHAR environment variables are set appropriately. And then I start getting lost, because there are 'open NLS', 'implicit NLS' and 'explicit NLS' modes, and there are NCHAR/CHAR and NVARCHAR/VARCHAR columns to consider. And DBLANG is also a factor. This means I am not going to provide a definitive answer in this e-mail -- you will need to obtain the reference manuals where it is all discussed in extenso to do that. However, if you have the environment set correctly, I4GL 6.0x will work in open or implicit NLS modes. I think that in open NLS mode, it will behave essentially as it did in 4.1x using basic, native string comparison routines. In implicit NLS mode, it will use the NLS-aware strcoll() routine to do the comparisons. The Engines will also support these two modes, and use the same comparison algorithms. If the environment is set for explicit NLS mode, then the Engine may use a different algorithm (locale) from I4GL. OK, and what happens if you try mixing 4.1x I4GL with 6.0 or later Engines? Well, the front-end is not NLS-aware, so any comparisons it does will be using the non-NLS strcmp() routines, and will produce ordering A, whereas any comparisons done by the Engines will be done using the NLS-aware routines (subject to the the environment being set appropriately -- see above), and this may produce a different sorting sequence for the same data! Ugh, but unavoidable. And what of the future? Well, the ALS (Asian Language Support) versions of I4GL have separate comparison techniques for multi-byte (as opposed to wide) character sets appropriate for the Asian markets (Japan, China, ...). Some versions of the Engines will use GLS (Global Language Support), and will be similar to the ALS versions -- I think this is going to be version 8, but don't quote me on that as a definitive source of information. NewEra has a separate set of versions to consider, and I don't have the details available. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>