UTF8 with Informix
Hello, I am relativly new to informix and must make a migration from the typical 8859 charset to UTF8. My database with the UTF8-locale is working, BUT because of multibyte chars (and char/varchar - columns store bytes not chars) all of my field lengths are corrupt. So I found the parameter sql_logical_char which should help. But sql_logical_char just multiplies the field length, so char(2) becomes char(6) This killed some of my varchar-fields, because of the (much too low) 255-byte limit. So I tried to switch to lvarchar. Of course I have a problem with all kind of client programs which rely on the field length, which they read from the database. And (of course) I have about twenty different ODBC-programs which all have an issue with these field length. Please anybody has experience and can tell me, what I can do, that Informix gives me the correct field length and can store multibyte data? Is it only a problem in the ODBC-driver configuration? Is this an issue for other databases too? Thanks for your answers. Klaus
Hello Klaus, I fiddled with UTF 8 too, just because of the Euro sign some month back. I moved back to 8859-15... The only general answer to all your questions is: "It depends..." 1st problem: Field sizes in Informix, as you noticed, but this is fixable 2nd problem: your application form fields, because there are still some tools running around not capable of multibyte or interpreting UTF8 multibyte characters in some strange way (some interpret UTF8 multibyte as char=2 bytes so 4 chars equal 8 bytes, some interpret only real multibyte stuff as two bytes, but standard ascii chars as single byte so you can end up with 4 chars, one a utf 8 with 5 bytes instead of 8 ...). All fixable too, but loads of work 3rd Problem: sorting order, that is sometimes also a big mixup, depending if sql sort, some listbox sorting, or array sorting in the app... Best practice: stick with the DB (or APP only) sort order 4th problem: Users... Some people have really strange ideas if they want some special char to fit in your app. And those stuff always bites back while data conversation to UTF, that was especially so with data input from older Windows apps and Informix Drivers before 2.90. Other databases: I personally wouldn´t trust any SQL Server (no matter which company or product) that a move to UTF 8 will be totally simple or painless. By now it should be so, but even inside the M$ family (from DB to .NET Dev) are still some small printed exceptions. So: Everything has to be checked and checked and rechecked during conversation. Sometimes the converted data itself has to be fixed. That's why I still stick to 8859-15... Regards, Joerg Volz ----------------------------------------------------------------------------- -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KLAUS GLAHN Sent: Thursday, November 25, 2010 7:01 PM To: ids@iiug.org Subject: UTF8 with Informix [22052] Hello, I am relativly new to informix and must make a migration from the typical 8859 charset to UTF8. My database with the UTF8-locale is working, BUT because of multibyte chars (and char/varchar - columns store bytes not chars) all of my field lengths are corrupt. So I found the parameter sql_logical_char which should help. But sql_logical_char just multiplies the field length, so char(2) becomes char(6) This killed some of my varchar-fields, because of the (much too low) 255-byte limit. So I tried to switch to lvarchar. Of course I have a problem with all kind of client programs which rely on the field length, which they read from the database. And (of course) I have about twenty different ODBC-programs which all have an issue with these field length. Please anybody has experience and can tell me, what I can do, that Informix gives me the correct field length and can store multibyte data? Is it only a problem in the ODBC-driver configuration? Is this an issue for other databases too? Thanks for your answers. Klaus ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. IT Handel und Beratung Jörg Volz Bernhard-Früh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de
On Thu, Nov 25, 2010 at 5:46 PM, KLAUS GLAHN <glahn@berlinale.de> wrote: > Hello, > > I am relativly new to informix and must make a migration from the typical > 8859 > charset to UTF8. My database with the UTF8-locale is working, BUT because > of > multibyte chars (and char/varchar - columns store bytes not chars) all of > my > field lengths are corrupt. So I found the parameter sql_logical_char which > should help. > > But sql_logical_char just multiplies the field length, so > > char(2) becomes char(6) > > This killed some of my varchar-fields, because of the (much too low) > 255-byte > limit. So I tried to switch to lvarchar. > > Of course I have a problem with all kind of client programs which rely on > the > field length, which they read from the database. > > And (of course) I have about twenty different ODBC-programs which all have > an > issue with these field length. > > Please anybody has experience and can tell me, what I can do, that Informix > gives me the correct field length and can store multibyte data? > > Is it only a problem in the ODBC-driver configuration? > > Is this an issue for other databases too? > > Thanks for your answers. > Klaus > > > Don't know about other databases... but... What is, in your view, "the correct field length" in UTF-8? There is not fixed length... You can store chars that take 1 byte, 2 or more... So how can it give you the "correct" field length? Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd1e164a0d5d70495e76d5f