CODEWITHSUNDEEP
javasqloracle-databasejdbcora-17004
L. Holanda
L. Holanda

Reputation: 4641

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

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.

Upvotes: 6

Views: 11351

Answers (2)

L. Holanda
L. Holanda

Reputation: 4641

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

Upvotes: 2

Flavio
Flavio

Reputation: 11977

I managed to make it work some time ago with the following incantations. These methods are an excerpt from a bigger class.

import com.mchange.v2.c3p0.C3P0ProxyStatement;
import oracle.jdbc.OraclePreparedStatement;

    static {
        try {
            SET_FORM_OF_USE_METHOD = OraclePreparedStatement.class.getDeclaredMethod("setFormOfUse", new Class[] { Integer.TYPE, Short.TYPE });
        } catch (NoSuchMethodException ex) {
            LOG.fatal("Can't find the setFormOfUse method", ex);
            throw new ExceptionInInitializerError(ex);
        }
    }


    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws SQLException {
        if (st instanceof OraclePreparedStatement) {
            ((OraclePreparedStatement)st).setFormOfUse(index, OraclePreparedStatement.FORM_NCHAR); 
        } else if (st instanceof C3P0ProxyStatement) {
            try {
                C3P0ProxyStatement c3p0St = (C3P0ProxyStatement) st;
                c3p0St.rawStatementOperation(SET_FORM_OF_USE_METHOD, C3P0ProxyStatement.RAW_STATEMENT, new Object[]{index, OraclePreparedStatement.FORM_NCHAR});
            } catch (IllegalAccessException ex) {
                throw new UnexpectedException("Error calling setFormOfUse through C3P0", ex);
            } catch (IllegalArgumentException ex) {
                throw new UnexpectedException("Error calling setFormOfUse through C3P0", ex);
            } catch (InvocationTargetException ex) {
                throw new UnexpectedException("Error calling setFormOfUse through C3P0", ex);
            }
        } else {
            throw new IllegalArgumentException("Unkown PreparedStatement implementation: " + st.getClass() + ". Maybe an unknown connection pool is hiding the OraclePreparedStatement?");
        }

        st.setString(index, (String) value);
    }

The nullSafeSet method is structured to work directly on OraclePreparedStatement instances, or on C3P0ProxyStatement in case you have the C3P0 connection pool; other options will have to be used in case of different pools.

This method worked with the ojdbc14.jar from Oracle 10.2.0.4.

Upvotes: 0

Related Questions