java work

Oracle, JDBC, Java and accented characters

I've been refering to Ascii Table - ASCII character codes and html, octal, hex and decimal chartsa lot today as I was trying to solve my character encoding problems...

It's very strange : I export data from a database using SQL queries. To do so, I have a DAO which requests a connection if it needs one (it's cached per thread using a ThreadLocal variable... very nice trick I read at developerWorks by the way!). This connection comes from a weblogic instance's JNDI tree. The thing is, the string I get from the DAO inside the J2EE app has question marks !

As Laurent had suggested in his comment of my previous blog entry, I tried to reproduce the bg in a JUnit test case. I used the same ExportDAO outside of Weblogic. But it still used the connection from the weblogic JNDI tree. At first, I was getting non-accented characters (ie é became e, à became a, etc...) ! Of course, I had to figure out that the encoding for my java files had to be Latin-1 in eclipse rather than ANSI_X3.4-1968 ! After getting our DBA to work on the issue, he determined that oracle's NLS was set to WE81508859P1 (looks familiar) but that another character set setting was ASCII7 !! After he did some magic on the database, I could all of a sudden insert accents into the database using DbUnit and, most of all, read them in the java test case.

But... I still get question marks inside the J2EE app... on that same row where it works in the test case. So I've not been able to reproduce the bug/feature outside of the test-production environment... :(

I did notice, however that extended characters were being encoded as two bytes in the DB's columns. It turns that the production environment is UTF8/UTF8 and our developpement and test databases are setup using oracle exports&imports so I guess the import process doesn't do character conversions ! That would definatly explain question marks but... I can't update the column and remove the two bytes and make them one...
So tomorrow we'll create yet another database, set it up like the production one this time and try again...

Talking to people here, I discovered that they never were able to get accents to work correctly in the test environment. Now we're paying for it...

PS: The M5 build for eclipse 2.1 is pretty cool. I especially like the "useless imports" feature... It's amazing how many import statements we can delete here !

Posted by pgirolami76 at February 18, 2003 06:37 PM | TrackBack