RE: JOIN databases of different charsets

Aleksander, When you unload the data, regardless of the character sets, your data is going to be a CSV file. That is, the character set strings are going to be in either a comma or pipe delimited format. If you want to do this in Java, you open the file and read each line in, specifying the character set. Now I'm going from memory, but I believe the jdbc driver picks up the character set used when connecting to a specific database? So you end up doing the following... Reading from file, get a row/record. For each row: Parse each field and then convert the field from character set A to UTF-8. Insert record (Its the exact same schema, right?) as UTF-8. So your only 'tricky' part is the character set conversion, which isn't too difficult. Actually I believe it is trivial in Java. See Java SE 1.6 String constructor : "String(byte[] bytes, Charset charset) Constructs a new String by decoding the specified array of bytes using the specified charset." The internal representation is UTF-16 which will get translated automatically to the correct format within the JDBC insert command. (Going from memory, which isn't always a good thing.) So you can write a simple JDBC java app that selects * from Table A, and then converts it and then writes out to Database.B.Table A. It may be easier than that... When you use Java's JDBC driver, what do you get when you select from a database? Is it already converted to UTF-16? If so, you don't need the special constructor. HTH -Mike > Subject: RE: JOIN databases of different charsets > Date: Wed, 23 Mar 2011 21:29:12 +0000 > From: aleksander.kamenik@krediidiinfo.ee > To: informix-list@iiug.org > > > From: Ian Michael Gumby [mailto:im_gumby@hotmail.com] > > > It looks like you already know the answer to your question. > > Different character sets will order/compare differently. Its not like > > you're writing your own comparable operator which would allow you to > > handle different character sets. > > Well theoretically it would be possible for informix first convert the ISO results to UTF and then do the JOIN. It's not a very popular feature request I guess. > > > At first I thought it would be a cool feature if you could specify the > > character sets on a per table basis, but then if you think about it... > > just go UTF-8 which should handle most if not all of your character set > > needs. (I haven't played with Kanji or other character sets.) > > That's why we're migrating to UTF. But it's not as simple as just a unload/load. > > > Since you're going to be converting from one character set to > > another... you could do it as you unload the table, or as you load the > > new database table. > > The real issue is that we are slowly migrating from one big iso database to several smaller ones in UTF. It's not just a charset change per table. New projects use new UTF databases, but they also need data from the old iso database while not everything has been migrated/updated. New tables with new schemas are created usually. > > It'll probably take more than a year for everything to migrate to UTF databases. Old projects are rewritten for UTF too, which don't need a schema changes, but it will still take time. Meanwhile not being able to make JOINs between new and old databases sucks a bit. > > > Thanks for confirming, > > Aleksander Kamenik > System Administrator > Krediidiinfo AS > an Experian Company > Phone: +372 665 9649 > Email: aleksander@krediidiinfo.ee > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list