Locale support

[leosurban]

Hi,
I have trouble with our locales. We are using czech locale for Central Europe, CP1250 on Windows, with PC-LATIN-2 on DOS command window.

So my settings is:
SET DB_LOCALE=cs_CZ.cp1250
SET CLIENT_LOCALE=cs_CZ.pc-latin-2

This leads to correct result:

echo SELECT nazevmater FROM cenmat | dbaccess mydb
All characters are correctly displayed, including diactritic, see screenshot.

Same setting, same window:

node ixtest.js

var opts = {
  database : 'mydb@ixlur',
  username : 'informix',
  password : '***'
}; 
var informix = require( 'informix' )( opts );
informix
  .query( "select nazevmater from cenmat" )
  .then( function ( cursor ) 
  {
    return  cursor.fetchAll();
    
  } )
  .then( function ( results )
  {
     console.log( 'results:', results );
  })
  .catch( function ( err ) 
  {
    console.log( err );
  } );

It is working but diacritic characters are missing (? is displayed, see screenshot).

My database is encoded as CP1250, so DB_LOCALE is correct.
In our Windows application with CP1250 is all ok, in DBACCESS, with pc-latin-2, too.

With node-informix I tried CLIENT_LOCALE set to cs_CZ.cp1250, cs_CZ.pc-latin-2, cs_CZ.UTF8, result same - ? characters.

But your application (ESQL) read locales, because when I set wrong locale:
SET CLIENT_LOCALE=cs_CZ.bad
then error is displayed:

node ixtest.js
Error: [-23101] Unable to load locale categories.
at Error (native)

python -V
Python 2.7.13

node -v
v6.7.0

npm list informix
node-api@ C:\buffer\jsrest
`-- informix@0.5.4

[uatuko]

@leosurban, can you give a sample SQL script to create a DB and a table with some data for me to test this out (similar to test/support/test-db.sql)?

Most likely this is due to ASCII/UTF-8 conversion issues 😃.

[leosurban]

Command script for Windows (for dbimport, dbexport, dbaccess, ...) ixlur.cmd:
Important for LOCALE is DB_LOCALE and CLIENT_LOCALE.
Last command is setting console locale for switch window to PC-LATIN-2 (=code page 852).

set INFORMIXDIR=C:\PROGRA~1\IBMINF~1
set REGMACHINE=\\LUR-PC
set INFORMIXSERVER=ixlur
set ONCONFIG=onconfig.ixlur
set INFORMIXSQLHOSTS=C:\Program Files\IBM Informix Software Bundle\etc\sqlhosts.ixlur
set GL_USEGLU=1
set PATH=%INFORMIXDIR%\bin;%PATH%
set DB_LOCALE=cs_CZ.cp1250
set CLIENT_LOCALE=cs_CZ.pc-latin-2
set DBDATE=dmy4.
set DBMONEY=.Kc
mode con codepage select=852

LocaleTest.exp.zip

unzip LocaleTest.zip

dbimport LocaleTest

echo select somename from exampletable | dbaccess LocaleTest

Database selected.
somename  Pink earthworm
somename  Růžová žížala
somename  Beehive
somename  Včelí úl
4 row(s) retrieved.
Database closed.

To be sure I attached screenshot with correct result:

node ixtest.js

node ixtest.js

[ [ 1, 'Pink earthworm', 42783 ],
  [ 2, 'R??ov? ???ala', 42783 ],
  [ 3, 'Beehive', 42783 ],
  [ 4, 'V?el? ?l', 42783 ] ]

// note: I replaced invalid characters with ?, see screenshot.

Javascript Node/Code:

var opts = {
  database : 'LocaleTest@ixlur',
  username : 'informix',
  password : '***'
};
var informix = require( 'informix' )( opts );
informix
  .query( "select id, somename, somedate from exampletable" )
  .then( function ( cursor ) 
  {
    return  cursor.fetchAll();    
  } )
  .then( function ( results )
  {
   console.log(results);
  })
  .catch( function ( err ) 
  {
    console.log( err );
  } );

If you will have some problem to simulate it (I do not know if you are using Windows, if your computer has support for EastEurope codesets etc..) I can prepare IX server with this database, with communication port opened to internet. I can also prepare similar setting for client on Unix/Linux OS (ISO-LATIN-2 code set).

Thanks.

[leosurban]

And ODT file (Zipped) because with Unicode encoded contetent may be better way than UNL single-codeset file. See end of document for INSERT statements.

LocaleTest.odt.zip
.

[uatuko]

It seems to work OK with UTF-8.

Using dbaccess (data from LocaleTest.odt):

create table exampletable
(
  id integer not null,
 somename nvarchar(100) not null,
  somedate date not null,
  primary key(id)
  somename nvarchar(100) not null,
  somedate date not null,
  primary key(id)
> );

Table created.

insert into exampletable values(1,"Pink earthworm", today);

1 row(s) inserted.

insert into exampletable values(2,"Růžová žížala", today);

1 row(s) inserted.

insert into exampletable values(3,"Beehive", today);

1 row(s) inserted.

insert into exampletable values(4,"Včelí úl", today);

1 row(s) inserted.

> select * from exampletable;



id        1
somename  Pink earthworm
somedate  02/18/2017

id        2
somename  Růžová žížala
somedate  02/18/2017

id        3
somename  Beehive
somedate  02/18/2017

id        4
somename  Včelí úl
somedate  02/18/2017

4 row(s) retrieved.

> 

Using Node.js:

informix                                                                                                                                                                                                                                                                                                                      
    .query( 'select * from exampletable' )                                                                                                                                                                                                                                                                                    
    .then( ( cursor ) => {                                                                                                                                                                                                                                                                                                    
        return cursor.fetchAll( { close : true } );                                                                                                                                                                                                                                                                           
    } )                                                                                                                                                                                                                                                                                                                       
    .then( ( results ) => {                                                                                                                                                                                                                                                                                                   
        console.log( results );                                                                                                                                                                                                                                                                                               
    } )                                                                                                                                                                                                                                                                                                                       
    .catch( ( err ) => {                                                                                                                                                                                                                                                                                                      
        console.log( err );                                                                                                                                                                                                                                                                                                   
    } );   

$ node tmp/test.js 
[ [ 1, 'Pink earthworm', '2017-02-18T00:00:00.000Z' ],
  [ 2, 'Růžová žížala', '2017-02-18T00:00:00.000Z' ],
  [ 3, 'Beehive', '2017-02-18T00:00:00.000Z' ],
  [ 4, 'Včelí úl', '2017-02-18T00:00:00.000Z' ] ]

Here's a UTF-8 data unload for reference:
exampletable.txt

[uatuko]

@leosurban, can you set the windows command prompt and CLIENT_LOCAL to utf-8 and check whether it resolves the issue?

Although ESQL/C support CLIENT_LOCAL when retrieving data, within Node.js all strings are treated as utf-8 so CLIENT_LOCAL should be set to utf-8. But there's no conversion from utf-8 to PC-LATIN-2 which I believe is why you are seeing encoding issues on command prompt.

Either setting command prompt to utf-8 or using a conversion library for display purposes should resolve your issue.

[leosurban]

Sorry, no, it is not working.

mode con codepage select=65001
set CLIENT_LOCALE=cs_CZ.UTF8
echo select somename from exampletable | dbaccess LocaleTest
… correct result …

node ixtest.js
… bad result …

What is setting of your Linux (?) terminal when you receive this (correct) result?

[leosurban]

[uatuko]

I'm using Linux UTF-8. A quick search seem to suggest you'll need Lucida Console font as well for UTF-8 support on windows command prompt which I assume you are using?

…

On 18 Feb 2017, at 13:37, leosurban ***@***.***> wrote: Sorry, no, it is not working. mode con codepage select=65001 set CLIENT_LOCALE=cs_CZ.UTF8 echo select somename from exampletable | dbaccess LocaleTest … correct result … node ixtest.js … bad result … What is setting of your Linux (?) terminal when you receive this (correct) result? — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub, or mute the thread.

[leosurban]

With Lucida Console font is the result the same. And because DBACCESS result with UTF-8 setting is correct (see screenshot), I mean that UTF8 is displayed correctly.

I tried also your exampletable.txt - correct.

Only result from ESQLC/JS is wrong :(

[uatuko]

Looks like there's a conversion issue somewhere on windows. Don't have access to a windows PC now to debug this but will try to take a look later this week.