6

I have the following "translation" table in an oracle 10g database:

ID  VARCHAR2(100 BYTE)
LANGUAGE    CHAR(2 BYTE)
COUNTRY CHAR(2 BYTE)
TRANSLATION NVARCHAR2(2000 CHAR)
TRACK_TIMESTAMP DATE
TRACK_USER  VARCHAR2(2000 BYTE)

When I try to do this:

update translation set translation = 'œ' where id = 'MY_ID' And language = 'fr';

Then I run this:

select * from translation where id = 'MY_ID' and language = 'fr';

and the translation column shows: S instead of œ and I have no idea why.

Due to legacy issues I cannot convert the whole database to use UTF-8, are there any other options?

Currently the national character set is AL16UTF16. The ordinary character set is WE8ISO8859P1.

I am currently using java 1.6

The above is a simplified example. Here is what the query looks like in my actual application:

UPDATE TRANSLATION SET TRANSLATION=? WHERE TRANSLATION.COUNTRY=? and TRANSLATION.ID=? and TRANSLATION.LANGUAGE=? 1=1,800 - 2,500 œufs par heure 2=CA 3=3_XT_FE_ECS18 4=fr

The problem here is instead of adding œufs it adds ¿ufs

testing123
  • 11,367
  • 10
  • 47
  • 61
  • 1
    What is the national character set of the database? Are you really generating UPDATE statements that use hard-coded string literals? Or are you using bind variables? If you are using bind variables, is the application identifying the data type of the data being passed in as NVARCHAR2 rather than just VARCHAR2? – Justin Cave Jun 06 '11 at 22:05
  • AL16UTF16. The ordinary character set is WE8ISO8859P1. No I am not generating update statements that use hard-coded string literals. I tried to simplify the example as much as possible. We currently use s2j and I also tried this with springs jdbcTemplate and got: '¿ufs'. Im not sure if s2j was recognizing it as a nvarchar2, but it changed its return type from a string to an object so I dont think its treating it as a varchar2. Thank you for your help let me know if I am missing any other information. – testing123 Jun 07 '11 at 14:30

1 Answers1

1

Since you are using bind variables rather than hard-coded literals, you should be able to pass Unicode strings to your UPDATE statement.

If you were using straight JDBC to write to the database, there is an example in the JDBC Developer's Guide on writing data to a NVARCHAR2 column. If you are using a 1.5 JVM, it is necessary to use the OraclePreparedStatement.setFormOfUse call for each NVARCHAR2 column. In a 1.6 JVM, life gets easier because JDBC 4.0 added NCHAR and NVARCHAR2 types. If you are using a 1.5 JVM, getting an ORM framework like Spring to use the Oracle extensions to JDBC may be a non-trivial undertaking. I'm not familiar enough with Spring to know what steps would be necessary for that to happen.

Potentially, you may be able to modify the connection string to specify defaultNChar=true. That will force the driver to treat all character columns using the national character set. That may be enough to resolve your problem without getting Spring to use the OraclePreparedStatement extensions.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you for your response. I added additional information to the original post. After reading your post I have been digging into s2j and I think the problem is the fact that I am using the ojdbc14.jar, I will get the latest and comment with results. Again thank you for the information! – testing123 Jun 07 '11 at 16:56
  • Ok, now I'm confused again, I updated to the ojdbc6.jar and still the nvarchar2 column is returning a type 1111 (OTHER in java.sql.Types) when I look at the metadata even though I would think it should map to -9 (the NVARCHAR variable found in the java.sql.Types class). Am I missing something here? – testing123 Jun 07 '11 at 17:22
  • What version of the JVM are you using? What request are you issuing to return the metadata? Are you using the ResultSetMetaData class? Or something else? – Justin Cave Jun 07 '11 at 17:28
  • 1. JVM 1.6. 2. java.sql.DatabaseMetaData retrieved by calling: java.sql.Connection getMetaData(). 3. No, should I be? 4. Yes, java.sql.DatabaseMetaData. – testing123 Jun 07 '11 at 19:51
  • Also I noticed there are now methods like getNString(). Should I be using that instead of getString(). Sorry for being such a noob. – testing123 Jun 07 '11 at 20:02
  • @testing123 - Yes, if you are using a 1.6 JVM, you should be using the new JDBC 4.0 calls like getNString and setNString. – Justin Cave Jun 07 '11 at 20:08
  • 1
    So is it still ok to use the DatabaseMetaData? Its giving me a type of 1111 for the NVARCHAR2 column. Shouldn't it be giving me -9? – testing123 Jun 07 '11 at 20:27
  • @Justin I tried using ResultSetMetaData class and it gives me the same value for all the datatypes. Not sure whats going on there. As for the DatabaseMetaData it appears to do everything I expect except for returning 1111 for my nvarchar2 column. Any thoughts? – testing123 Jun 08 '11 at 17:09