Converting ifx 11.50 locales (Question)

Hi. I have big ifx databases based on en_US.819 collation. Database contains polish signs, which im unable to present on my web application (ASP.NET) properly (getting #@$@#%^ instead of proper signs). Does anyone know how to convert en_US.819 to pl_pl.8859-2 or other polish collation ? Tried to set db_locale and client_locale in connection_string but it seems it does nothing. READ BELOW !! Before answer, read this - https://www.ibm.com/developerworks/forums/thread.jspa?threadID=335994&tstart=0. This is my question and conversation at the other forum.

Can we know the following: - What is your Informix version? - What are the settings on your client (DB_LOCALE and CLIENT_LOCALE) - What WERE the settings on your client when you inserted the polish signs? - If your database has en_US.819 how did you insert the characters? I believe you're more or less "trapped" in a nasty situation due to old problems with the product (that allowed bad use). Probably the answer you got on DeveloperWorks forum is correct, but the above will help to clarify this. Regards. 2010/8/4 Michał Karasiński <mkarasinski86@gmail.com> > Hi. > I have big ifx databases based on en_US.819 collation. Database > contains polish signs, which im unable to present on my web > application (ASP.NET) properly (getting #@$@#%^ instead of proper > signs). Does anyone know how to convert en_US.819 to pl_pl.8859-2 or > other polish collation ? Tried to set db_locale and client_locale in > connection_string but it seems it does nothing. > > > READ BELOW !! > Before answer, read this - > https://www.ibm.com/developerworks/forums/thread.jspa?threadID=335994&tstart=0 > . > This is my question and conversation at the other forum. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

One suggestion, create a staging database with the Polish pl_pl.8859-2 locale. Then your web app can copy data from the live database that it needs to access into a temp table in the 'Polish' database and fetch it from there. A bit klunky, but it may serve for a while until you can convert the entire database to the Polish locale by exporting and re-importing it into a database with the correct locale. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. 2010/8/4 Michał Karasiński <mkarasinski86@gmail.com> > Hi. > I have big ifx databases based on en_US.819 collation. Database > contains polish signs, which im unable to present on my web > application (ASP.NET) properly (getting #@$@#%^ instead of proper > signs). Does anyone know how to convert en_US.819 to pl_pl.8859-2 or > other polish collation ? Tried to set db_locale and client_locale in > connection_string but it seems it does nothing. > > > READ BELOW !! > Before answer, read this - > https://www.ibm.com/developerworks/forums/thread.jspa?threadID=335994&tstart=0 > . > This is my question and conversation at the other forum. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

First of all, thanks for fast response. To Fernando : - What is your Informix version? My informix client is 3.50 tc6 and server has 11.50 version, but data were move to that version from older ifx databases (7.31) - What are the settings on your client (DB_LOCALE and CLIENT_LOCALE) ? DB_LOCALE and CLIENT_LOCALE are now en_US.819 cuz i cant find any conversion to pl_pl.8859-2. - What WERE the settings on your client when you inserted the polish signs? DB_LOCALE and CLIENT_LOCALE were pl_pl.8859-2 - If your database has en_US.819 how did you insert the characters? informations from previous databases were exported to new, one big database. Then someone choosed (while exporting) en_US.819 dbs_collate and boom. To Art Kagiel I was thinking about it, but it will be my last decision, i am still trying to find out, how to do that conversion. Problem is that database is so big that reexporting it will take way too much (can even last 1 month). If proper setting db_locale and client_locale does nothing (in fact i tried many conversions and i got same char result every time, maybe i set it wrong) i think about rewriting the conversion table at the client (informix/gls/lc11 has language definitions and informix/gls/ cv9 contains conversion table, maybe rewrite it manually can solve the problem) but its only my imagination, dont know if its possible, there are some librarys interested in that files too. I'd be thankful in ideas that can lead to success in other way than reexporting that database (it will eat many lives and money :>).

I forgot to ask what was the DB_LOCALE on the server before migration. Well... this should be in a FAQ. Typically, older server versions (until 10.00.FC3 I think and around 9.4.FC6?) would allow you to have DB_LOCALE=en_us.819 and the clients with DB_LOCALE=CLIENT_LOCALE=(!=DB_LOCALE on server).So what would happen was that the client would not do any codeset conversion (DB_LOCALE=CLIENT_LOCALE on the client side). And the characters using a polish codeset would be inserted into a en_us.819 codeset database. >From the application perspective everything would work... Then, around CSDK 2.90TC6? if got smarter and if you didn't choose the DB_LOCALE on the client side it would "ask" the database and use it. Then you'd get into problems because the code would see a character code that was invalid in the defined codeset. A workaround was to force the old situation and connect with the wrong DB_LOCALE. Than (!) on the server side something was implemented to prevent this kind of connections. The (goof) purpose was to prevent the initial problem. The side effect was that old systems where the problem happened got into trouble. Finally an undocumented variable was created to make the server allow the "wrong" connections. export IFMX_UNDOC_B168163=1 and start your engine... then, set DB_LOCALE=CLIENT_LOCALE=polish setting... try your application and see how it goes... If it works (it will depend if the assumptions above are right), take this as a temporary workaround. For all purposes your database is "corrupted" since you have characters using codes unavailable in your database codeset... Export/import is the solution, but this may gain you some time to do it. Regards. 2010/8/4 Michał Karasiński <mkarasinski86@gmail.com> > First of all, thanks for fast response. > > To Fernando : > > - What is your Informix version? > My informix client is 3.50 tc6 and server has 11.50 version, but data > were move to that version from older ifx databases (7.31) > > - What are the settings on your client (DB_LOCALE and CLIENT_LOCALE) ? > DB_LOCALE and CLIENT_LOCALE are now en_US.819 cuz i cant find any > conversion to pl_pl.8859-2. > > - What WERE the settings on your client when you inserted the polish > signs? > DB_LOCALE and CLIENT_LOCALE were pl_pl.8859-2 > > - If your database has en_US.819 how did you insert the characters? > informations from previous databases were exported to new, one big > database. Then someone choosed (while exporting) en_US.819 dbs_collate > and boom. > > To Art Kagiel > > I was thinking about it, but it will be my last decision, i am still > trying to find out, how to do that conversion. > > > Problem is that database is so big that reexporting it will take way > too much (can even last 1 month). > If proper setting db_locale and client_locale does nothing (in fact i > tried many conversions and i got same char result every time, maybe i > set it wrong) i think about rewriting the conversion table at the > client (informix/gls/lc11 has language definitions and informix/gls/ > cv9 contains conversion table, maybe rewrite it manually can solve the > problem) but its only my imagination, dont know if its possible, there > are some librarys interested in that files too. > > I'd be thankful in ideas that can lead to success in other way than > reexporting that database (it will eat many lives and money :>). > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Thanks very much for the response. We have been prepaired that reexport will occur sooner or later. Noone wanted it, but we just knew it. Tomorrow ill try that things u say. Tx once more.