ansaurus

Question

Answer 1

+1 A:

We had a similar problem in the past, with LONG columns instead of CLOBs. The problem was the JDBC driver, the one we use now and works fine is alt text

Lluis Martinez 2009-12-04 16:25:04

Thats the driver jar we are using.

Mark 2009-12-04 20:34:28

Answer 2

+1 A:

I think your problem might be that you are using Oracle 9i but Hibernate dialect is 10g. Make sure your driver,db version and dialect are all in sync because there is a 9i dialect as well org.hibernate.dialect.Oracle9iDialect

non sequitor 2009-12-04 20:15:09

Thanks, started with the 9i but to no avail. I went back and set it to 9i again to double check. Still getting the same exception.

Mark 2009-12-04 20:39:05

What about the drivers are u still using drivers for 10g when u are using 9i? Also can you post the sql generated?

non sequitor 2009-12-04 20:55:16

How can you tell which drivers are being used? I am using the latest version of ojbc14.jar from the oracle site. The driver class in the persitence.xml is oracle.jdbc.driver.OracleDriver . I'll dig out the sql being run and post it.

Mark 2009-12-04 21:06:29

This is the sql generated by hibernate:insert into pweb60.EA_COMMENTS (APPROVED, ARCHIVED, PATH, DELETED, COMMENT_DOC, FINISHED, MODERATED_BY, PARENT_COMMENT, TOPIC_ID, USER_ID, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Mark 2009-12-04 21:38:11

According to you, you are using "Oracle 10.2.x drivers" I'm not sure exactly what that is

non sequitor 2009-12-04 21:52:01

So I did, sorry about the confusion. I just downloaded the Oracle 9.2.0.1 drivers. Still the same issue. Sure had my hopes up for a second.

Mark 2009-12-04 22:03:06

This issue seems to be a recurring one in Hibernate and I can't believe it still exists on the Hibernate forum a users have created/extended a UserType. For myself I have used Spring and typeDef-ed my clob type like `@org.hibernate.annotations.TypeDef( name="clob", typeClass=org.springframework.orm.hibernate3.support.ClobStringType.class )` this has worked fine for me on Oracle.Even if you are not using Spring you can look at the ClobStringType source, write ur own class and use it similarly on `getDocument()` like this `@org.hibernate.annotations.Type(type="clob")`

non sequitor 2009-12-04 22:08:33

I have seen a lot of that and they are all dated around 2004. I have been trying to avoid a "hack" solution. Others claim they change the driver and all works well for them. Which leads me to believe I have a config issue either in my code or possibly the way the DBA set up the DB.

Mark 2009-12-04 22:40:30

Mark why don't you reload your Tomcat now that all your drivers,dialect etc are straight and let me know

non sequitor 2009-12-04 22:52:43

`Connection.setBigStringTryClob` is a 10g feature I believe, try removing it from your persistence.xml and then also verify that column type for `comment_doc` in the db is `clob`.

non sequitor 2009-12-04 23:06:16

Did all that and verified per your instructions with little change. With out the `setBigStringTryClob` I get a `No more data to read from socket` exception. Adding it back I started talking with our DBA and each time I run the query there is a protocol violation error dump on the DB server

Mark 2009-12-07 14:52:14

Thanks, for the help, I had to implement the user type and now it works. The man finally beat me down.

Mark 2009-12-07 20:28:05

Answer 3

+1 A:

Thanks to non sequitor for all the help. I have this working and figure I will put all the pieces here for future reference. Regardless of all the claims about upgrading the drivers and everything would work, non of that worked for me. In the end I had to implement a 'org.hibernate.usertype.UserType' I named it the same as all the examples on the web StringClobType. Save for some imports I used the example from Using Clobs/Blobs with Oracle and Hibernate. As far as I am concerned ignore the "beware" claim.

There was one change I had to make to get merges to work. Some of the methods were not implemented in the provided code sample. Eclipse fixed it for me by stubbing them out. Cool, but the replace method needs to be actually implemented or all merges will overwrite the data with a null. Here is my implementation:

public Object replace(Object newValue, Object existingValue, Object arg2)throws HibernateException {
 return newValue;
}

I will not duplicate the class implementation here go to the above link to see it. I used the code in the third gray box. Then at the top of the pojo class I wanted to use it in I added the following after the imports

...  
import org.hibernate.annotations.Type;  
import org.hibernate.annotations.TypeDefs;  
import org.hibernate.annotations.TypeDef;  

@TypeDefs({  
    @TypeDef(  
     name="clob",  
     typeClass = foo.StringClobType.class  
    )  
})  
@Entity  
@Table(name="EA_COMMENTS")  
public class Comment extends SWDataObject implements JSONString, Serializable {  
...  
}

Then to use the new UserType I added the annotation to my getter:

@Type(type="clob")
@Column(name="COMMENT_DOC")
public String getDocument(){
 return get("Document");
}

I did not need the @Lob annotation.
In my persistence.xml the persistence-unit declaration ended looking like:

<persistence-unit name="###" transaction-type="RESOURCE_LOCAL">
 <provider>org.hibernate.ejb.HibernatePersistence</provider>
 <properties>
  <property name="hibernate.archive.autodetection" value="class"/> 
  <property name="hibernate.connection.password" value="###" />
  <property name="hibernate.connection.username" value="###" />
  <property name="hibernate.connection.url" value="jdbc:oracle:thin:@server.something.com:1521:###"/>
  <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>
  <property name="hibernate.default_schema" value="###" />
  <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9iDialect" />
  <property name="hibernate.c3p0.min_size" value="5" />
  <property name="hibernate.c3p0.max_size" value="100" />
  <property name="hibernate.c3p0.timeout" value="300" />
  <property name="hibernate.c3p0.max_statements" value="50" />
  <property name="hibernate.c3p0.idle_test period" value="3000" />
  <property name="hibernate.c3p0.idle_connection_test_period" value="300" />
  <property name="show_sql" value="false" />
  <property name="format_sql" value="false" />
  <property name="use_sql_comments" value="false" />
  <property name="hibernate.jdbc.batch_size" value="0"/>
 </properties>
</persistence-unit>

The SetBigStringTryClob never worked for me and was not needed for this final implementation.

My lesson learned is in the end it is probably better to join then to fight. It would of saved me three days.

Mark 2009-12-07 20:52:44

Answer 4

+1 A:

It should be:

<property name="hibernate.connection.SetBigStringTryClob">true</property>
<property name="hibernate.jdbc.batch_size">0</property>

And not:

<property name="SetBigStringTryClob">true</property>

And use the right dialect for your database (org.hibernate.dialect.Oracle9iDialect).

Also make sure that you are using the latest Oracle 10g Release 2 thin driver (10.2.0.4) or later.

Pascal Thivent 2010-01-25 07:13:16

ansaurus

tags:

views:

answers:

Hibernate > CLOB > Oracle :(

related questions