GLS: collating problems
'Hola' everybody. I'm having trouble with GLS databases: 1- Some characters in the codeset are not included in the collating sequence (eg. 0x20 and 0xAA in ISO8859-1). They are simply ignored, thus distorting the ordering and confusing my users. 2- The collating sequence is hardwired. But sometimes I need lowercase mixed with uppercase ('a' after 'A') and sometimes at the end ('a' after 'Z'). The solution, I guess, would be to change some .lc file. But, as far as I know, there is no way to compile a .lc file into a .lco Any suggestion? Thanks in advance. -- --------------------------------------------------------------------------- J.Miguel Signes (signes_jmi@gva.es). Tf: (9638) 68635 Servei de Coordinaci' de Sistemes d'Informaci' de la Xarxa Sanit'ria. Conselleria de Sanitat. Generalitat Valenciana. ---------------------------------------------------------------------------
"J.Miguel Signes" wrote:
>
> 'Hola' everybody.
> I'm having trouble with GLS databases:
First question: What is your informix(IDS) version ?
GLS is available beginning with version 7.2 of IDS.
>
> 1- Some characters in the codeset are not included in the collating sequence
> (eg. 0x20 and 0xAA in ISO8859-1). They are simply ignored, thus
> distorting the ordering and confusing my users.
Is the database created with the correct collating sequence ?:
export DB_LOCALE=es_es.8859-1dbaccess - <<EOF
CREATE DATABASE your_database;EOF
>
> 2- The collating sequence is hardwired. But sometimes I need lowercase
> mixed with uppercase ('a' after 'A') and sometimes at the end
> ('a' after 'Z').
Are the character and varchar fields NCHAR and NVARCHAR ?
If not, the collating sequence is standard en_us (7 bit).
regards
Markus
>
> The solution, I guess, would be to change some .lc file.
> But, as far as I know, there is no way to compile a .lc file into a .lco
>
> Any suggestion?
>
> Thanks in advance.
> --
> ---------------------------------------------------------------------------
> J.Miguel Signes (signes_jmi@gva.es). Tf: (9638) 68635
> Servei de Coordinació de Sistemes d'Informació de la Xarxa Sanitària.
> Conselleria de Sanitat. Generalitat Valenciana.
> ---------------------------------------------------------------------------
In short:
IDS version... OK (it is 7.24)
LOCALE variables... OK
DB creation procedure... OK
Column datatypes... OK
But this is what I get after a "SELECT name FROM table ORDER BY name":
...
M' AMPARO
MERCEDES
M' LUISA
MONICA
M' PETRA
...
All other characters specific for the spanish locale (eg. ', ', ', ' ', ' '
and so on ) are correctly ordered.
Thanks for your attention.
--
---------------------------------------------------------------------------
J.Miguel Signes (signes_jmi@gva.es). Tf: (9638) 68635
Servei de Coordinaci' de Sistemes d'Informaci' de la Xarxa Sanit'ria.
Conselleria de Sanitat. Generalitat Valenciana.
---------------------------------------------------------------------------
Markus Holzbauer <markus.holzbauer@mch.siemens.de> escribi' en el mensaje de
noticias 38980A23.B0FE0FF7@mch.siemens.de...
"J.Miguel Signes" wrote:
>
> 'Hola' everybody.
> I'm having trouble with GLS databases:
First question: What is your informix(IDS) version ?
GLS is available beginning with version 7.2 of IDS.
>
> 1- Some characters in the codeset are not included in the collating
sequence
> (eg. 0x20 and 0xAA in ISO8859-1). They are simply ignored, thus
> distorting the ordering and confusing my users.
Is the database created with the correct collating sequence ?:
export DB_LOCALE=es_es.8859-1dbaccess - <<EOF
CREATE DATABASE your_database;EOF
>
> 2- The collating sequence is hardwired. But sometimes I need lowercase
> mixed with uppercase ('a' after 'A') and sometimes at the end
> ('a' after 'Z').
Are the character and varchar fields NCHAR and NVARCHAR ?
If not, the collating sequence is standard en_us (7 bit).
regards
Markus
>
> The solution, I guess, would be to change some .lc file.
> But, as far as I know, there is no way to compile a .lc file into a .lco
>
> Any suggestion?
>
> Thanks in advance.
> --
"J.Miguel Signes" wrote:
>
> In short:
> IDS version... OK (it is 7.24)
> LOCALE variables... OK
> DB creation procedure... OK
> Column datatypes... OK
>
> But this is what I get after a "SELECT name FROM table ORDER BY name":
> ...
>
> MERCEDES
> Mª LUISA
> MONICA
> Mª PETRA
> ...
When i create a database (spain) with DB_LOCALE=es_es.8859-1
and a table (spain_locale) with one NCHAR field (f1) and load it
with your data i get the following results with IDS 7.24.UC8 :
unset DB_LOCALE CLIENT_LOCALE SERVER_LOCALE DBLANG
export DB_LOCALE=es_es.8859-1dbaccess - <<EOF
CREATE DATABASE spain;EOF
dbaccess spain <<EOF
CREATE TABLE spain_locale
(
f1 NCHAR(50)
);
INSERT INTO spain_locale VALUES ("Mª AMPARO");
INSERT INTO spain_locale VALUES ("MERCEDES");
INSERT INTO spain_locale VALUES ("Mª LUISA");
INSERT INTO spain_locale VALUES ("MONICA");
INSERT INTO spain_locale VALUES ("Mª PETRA");EOF
dbaccess spain <<EOF
SELECT * FROM spain_locale;EOF
Mª AMPARO
MERCEDES
Mª LUISA
MONICA
Mª PETRA
dbaccess spain <<EOF
SELECT * FROM spain_locale ORDER BY 1;EOF
Mª AMPARO
Mª LUISA
Mª PETRA
MERCEDES
MONICA
dbaccess spain <<EOF
SELECT f1 FROM spain_locale ORDER BY 1;EOF
Mª AMPARO
Mª LUISA
Mª PETRA
MERCEDES
MONICA
Regards
Markus
>
> All other characters specific for the spanish locale (eg. ñ, Ñ, á, é í, ó ú
> and so on ) are correctly ordered.
>
> Thanks for your attention.
> --
> ---------------------------------------------------------------------------
> J.Miguel Signes (signes_jmi@gva.es). Tf: (9638) 68635
> Servei de Coordinació de Sistemes d'Informació de la Xarxa Sanitària.
> Conselleria de Sanitat. Generalitat Valenciana.
> ---------------------------------------------------------------------------
> Markus Holzbauer <markus.holzbauer@mch.siemens.de> escribió en el mensaje de
> noticias 38980A23.B0FE0FF7@mch.siemens.de...
> "J.Miguel Signes" wrote:
> >
> > 'Hola' everybody.
> > I'm having trouble with GLS databases:
>
> First question: What is your informix(IDS) version ?
> GLS is available beginning with version 7.2 of IDS.
>
> >
> > 1- Some characters in the codeset are not included in the collating
> sequence
> > (eg. 0x20 and 0xAA in ISO8859-1). They are simply ignored, thus
> > distorting the ordering and confusing my users.
>
> Is the database created with the correct collating sequence ?:
>
> export DB_LOCALE=es_es.8859-1> dbaccess - <<EOF
> CREATE DATABASE your_database;> EOF
>
> >
> > 2- The collating sequence is hardwired. But sometimes I need lowercase
> > mixed with uppercase ('a' after 'A') and sometimes at the end
> > ('a' after 'Z').
>
> Are the character and varchar fields NCHAR and NVARCHAR ?
> If not, the collating sequence is standard en_us (7 bit).
>
> regards
> Markus
> >
> > The solution, I guess, would be to change some .lc file.
> > But, as far as I know, there is no way to compile a .lc file into a .lco
> >
> > Any suggestion?
> >
> > Thanks in advance.
> > --
Thanks a lot Markus. I made a mistake: in the script for creating the database I assigned to DB_LANG the same value I had for $LANG. They are very close but not exactly the same. By the way, last week I faced exactly the same problem with the operating system. When sorting a file with 'sort', some characters where ignored. Then, the solution was to modify the equivalents of the .lc and .lco files. Fortunately, the DG/UX system provides information about the structure of the source file and the tools to compile it. Best regards. -- --------------------------------------------------------------------------- J.Miguel Signes (signes_jmi@gva.es). Tf: (9638) 68635 Servei de Coordinaci' de Sistemes d'Informaci' de la Xarxa Sanit'ria. Conselleria de Sanitat. Generalitat Valenciana. ---------------------------------------------------------------------------