Of JNDI, DBCP and Oracle

In continuation with the earlier post, I was pretty close to hitting my head against a big, indifferent wall as I struggled with JNDI connections and Oracle Prepared Statement to dump in Chinese characters into a database column.

So here's the step by step account:

• Make sure your database column type is NVARCHAR2. This is just like VARCHAR2 but allows you to insert pretty much everything into it.
• Query up your national character set: select value from v$nls_parameters where PARAMETER = 'NLS_NCHAR_CHARACTERSET'. This should return AL16UTF16
• Now the first option is to use the unistr function. So my statement would look something like insert into table_name (col1, col2, col3...,coln) values (?,?,?,...unistr(?)).Now use a standard setString API call, but make sure that the input string is of the form \[four digit hex code] for each character.
• Second option is to call the setFormOfUse API on the OraclePreparedStatement class as ps.setFormOfUse([index], OraclePreparedStatement.FORM_NCHAR) and set the string in a standard fashion. However, the string here needs to be of the form \u[four digit hex code] for each character.

However, on the last step I kept getting a class cast exception whenever I tried getting the OraclePreparedStatement object from the standard PreparedStatement. This is what you will expect to work normally:

DelegatingPreparedStatement dps = (DelegatingPreparedStatement) ps;

OraclepreparedStatement ops = (OraclePreparedStatement) dps.getInnermostDelegate();

ops.setFormOfUse(...);

But inspite of the runtime class of the object returned by the getInnerMostDelegate() method is OraclePreparedStatement, it kept giving me a class cast exception (I still don't know why!). However, a reflective call like the one below worked!

DelegatingPreparedStatement dps = (DelegatingPreparedStatement) ps;

Statement stmt = ps.getInnermostDelegate();

Method setUsageMethod = stmt.getMethod("setFormOfUse", int.class, short.class);

setUsageMethod.invoke(stmt, [args]);

Now I need to revisit the search implementation on the nvarchar column and then post this to other databases. Hmmph!