Using Types.NVARCHAR with oracle JDBC driver to work with Cyrillic chars

Question

I am trying to use the "New Methods for National Character Set Type Data in JDK 1.6", to get a standard JDBC solution to handle cyrillic chars, but when the execution reaches any line with NVARCHAR type, for instance:

preparedSelect.setObject(3, "суббота", Types.NVARCHAR);

Then I get this exception:

java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7922)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7502)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:7975)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:222)

I also tried to use setNString() but I get an even more strange exception:

java.lang.AbstractMethodError: oracle.jdbc.driver.OraclePreparedStatementWrapper.setNString(ILjava/lang/String;)V

If I use java -Doracle.jdbc.defaultNChar=true myApplication with regular Types.VARCHAR, the Russian words are stored correctly. But using -Doracle.jdbc.defaultNChar=true is not an option since I'm working on a legacy application, I do not have control of running production environment, I'm just writing a component to it. Furthermore, this "Readme for NChar How-to" states that "This conversion has a substantial performance impact". So setting everything to NChar by default when only less than 1% of my tables needs this conversion in not a smart choice.

I'm using oracle thin driver and I have ojdbc6.jar and orai18n.jar in my classpath.

I'm looking for a standard JDBC solution. I can not use any methods or constants with "oracle" on them. OraclePreparedStatement is not an option for me.

I tried using Types.NVARCHAR with MSSQL Server and it runs fine.

Solution

I found the solution!

I was using ojdbc 11.2.0.1. When I switched to 11.2.0.2, I could get setNString() working properly. But I'm still getting the same java.sql.SQLException: Invalid column type if I use setObject() with Type.NVARCHAR. Shame on you Oracle...

Anyway, the solution: switch to ojdbc 11.2.0.2

Comments

[Aravind Yarram]

Did you try using setNString() and see if it works?

[L. Holanda]

I edited my question with an update about setNString()

[a_horse_with_no_name]

To rule out the obvious: did you try to call the good old setString()? I have successfully stored arabic characters with that.

[L. Holanda]

Yes I did. I only get upside-down question marks stored there ('¿¿¿¿¿'). This is because the default oracle charset is not UTF8. I have Oracle XE installed on my local machine and it stores fine using regular setString(). But I can't go asking more than 50 clients to change their default database charset, since they already have UTF16 for N-charset.

[L. Holanda]

The problem with this solution is because it is Oracle dependent. I am looking for something JDBC standard since our system works in both MSSQL Server and Oracle. Looks like Microsoft JDBC driver is more Java compliant than the Java "owner" itself.

[Flavio]

As far as ojdbc14.jar is implied, there probably is no standard jdbc way to do it. I'm not sure about the driver version you are using; anyway, if this works, you could use it for oracle, and revert to the standard way for everything else.

[L. Holanda]

As we can see in "New Methods for National Character Set Type Data in JDK 1.6" they say "use the setFormOfUse method... ...is discouraged because this method will be deprecated in future release" and they also say: "If the setObject method is used, then the target data type must be specified as Types.NCHAR, Types.NCLOB, Types.NVARCHAR, or Types.LONGNVARCHAR"