I am using hibernate 1.x with Oracle 9i. I need to store double byte data into the database and I prefer to use unicode. I have set the database the national character set to be "UTF8" and I defined several columns to have type: NVARCHAR2.

However, when I use hibernate to save the string, it ended with the "garbage" characters in the database. I saw some postings to set character encoding in the jdbc url, I did that and that did not solve the problem:

hibernate.connection.url=jdbc:oracle:thin:@csiceast:6660:sdd1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

I also did:

hibernate.connnection.charSet=UTF8

But that did not solve the problem either.

I am also aware that oracle jdbc drive has a method "setFormOfUse" for setting columns with NVARCHAR2 type. However, I don't know whether Hibernate implementation used it or somehow I can tell it to use it.

I would appreciate your help.

I suggest you figure out how to solve this problem by using direct JDBC, and then do the same in Hibernate. This problem has nothing at all to do with Hibernate. You should have no problem making internationalized strings work on Oracle.

I did the direct jdbc with the instruction from Oracle. I did two tests: if i specify "setFormOfUse", i get the correct content in the database. However, it i did not, the content is not correct unicode.

The question is: whether hibernate oracle dialect takes this into account? i look at the source code, I could not find any calls to this method. Thus, my guess is that it did not.

If that is the case, my next question is: any suggestions on how to handle this problem - maybe make code changes to hibernate.

Also, keep in mind that i have set the database the national character set to be "UTF8", however Character set to be USASCII7.

I'm quite confident you'll find a way to do this without changing Hibernate.

Can you be more specific? I am waiting for the suggestions.

Oracle suggestion is quite clear
http://otn.oracle.com/sample_code/tech/java/codesnippet/jdbc/nls/Globalization.html

I wonder how people can use NVARCHAR2 and Hibernate's StringType without problems...

hi there,

I've faced the same problem right now.

I'll try to describe the problem in brief terms:
*) Oracle has special datatypes for supporting Unicode Characters: NCHAR, NVARCHAR, NCLOB
*) To correctly use them in insert or update statements, you have to use:
oracle.jdbc.OraclePreparedStatement ps = (oracle.jdbc.OraclePreparedStatement)conn.prepareStatement("INSERT ....");
ps.setFormOfUse(5, oracle.jdbc.OraclePreparedStatement.FORM_NCHAR);
ps.setString(5, "unicodestring\u0100");

==> Of course a possible solution would be to retrieve the Connection and do these statements manually.

However, the main question is, whether there is some way to integrate this type of access into Hibernate, as this problem will come across any developer wanting to use the unicode datatypes of Oracle (9i+10g).

Suggestions are perfectly welcome,
Johannes

[quote="joe_the_quick"]
However, the main question is, whether there is some way to integrate this type of access into Hibernate, as this problem will come across any developer wanting to use the unicode datatypes of Oracle (9i+10g).

Suggestions are perfectly welcome,
Johannes[/quote]

public class UTFStringType extends StringType{
public void set(PreparedStatement st, Object value, int index) throws SQLException {
if (st instanceof OraclePreparedStatement) {
((OraclePreparedStatement)st).setFormOfUse(index, OraclePreparedStatement.FORM_NCHAR);
}
super.set(st, value, index);
}
}

Use it instead of built-in string type.

dear furman,

thank you very much for the really detailed response (I was on holidays so I didn't answer earlier)!!

what is the best way to register this custom type to hibernate to use it in the xml file?
(to be honest I haven't worked much with custom types yet).

thank you very much,
johannes

The solution works smoothly, thank you very much!
I put it to work as follows:

<property name="ntitle" type="UTFStringType" >
<column name="NTITLE" sql-type="nvarchar(100)" not-null="true"/>
</property>

thx again
johannes

Works as long as you are not using any connection pool behind the scenes... In this case the connection returned by the pool is unlikely to be an OraclePreparedStatement but a proxy instantiated by the pool.

I've tried to use a JNDI datasource with Hibernate, but this has raised a ClassCastException as you've already thought of.

hibernate.properties:
hibernate.connection.datasource java:/XAOracleDS




After using the datasource, the cast of the PreparedStatement fails with a ClassCastException.
Any ideas or does it mean I cannot use a DataSource with Hibernate?

thx alot
johannes

It means you can use DataSource without any problem but you cannot cast to the OracleStatement... Just because you are not supposed to make such assumption... and you are likely to have the same problem with nearly any kind of DataSource implementation or ConnectionPool mechanism.

Except maybe if you try the very latest version of Proxool (from CVS). The Connections and Statements served by Proxool should implement the vendor specific interfaces - you should then be able to cast it as you expect.

thank you for the fast response.

however, I need this assumption to allow for correct storage of Unicode-data into the Oracle database NVARCHAR field (otherwise the UTF8-data will not be stored correctly).

Is there a "general" way to use vendor-specific extensions (like OraclePreparedStatement.setFormOfUse) in Hibernate?
I thought the User-Datatypes have been created to serve exactly this need.

To be honest, I wouldn't like to use a pool implementation of a specific vendor (except if this is the only way to do it).
Would reflection be a technique of use here (I fear not, because it seems the Pool doesn't know it has been an OraclePreparedStatement anymore).

thx alot for your ideas,
johannes

User your debugger, take a look at the DataSource object, probably there is a way to get the original, wrapped statement from it.