Problem with clob field

[rcgeorge23]

Hi Chaps,

I'm having some problems using an entity that has a field with a CLOB data type.

I've annotated the field @Lob, roo has helpfully converted the web scaffolding so that the field now uses a textarea, however if I try to insert a new record I get the following error -

2011-12-15 12:01:13,063 [tomcat-http--19] ERROR org.hibernate.util.JDBCExceptionReporter - Large Objects may not be used in auto-commit mode.

Is there a way around this? I've tried turning off auto commit by adding the following line to persistence.xml

<property name="hibernate.connection.autocommit" value="false"/>

but this doesn't seem to have helped. Are clobs fully supported in Roo? Is there something I'm missing?

I'm using postgresql / hibernate / Roo 1.1.5.

Any help would be much appreciated!

Cheers.

[rcgeorge23]

Well it looks like it's actually a problem with postgresql!

Here's another forum post that explains it... -

http://forum.springsource.org/showth...to-commit-mode

[delgad9]

@rcgeorge23,

Review article in the following link... http://viralpatel.net/blogs/2011/02/...-tutorial.html. I am CRUDing similar data type without any problem or issue.


I hope it helps you.

Roogards
jD

[rcgeorge23]

So, after a bit more investigation, it seems I can use a clob just fine with a persistent HSQLDB database.

If I use postgresql, hibernate will create the tables but I run into the auto-commit error when I try to persist stuff.

If I use MySQL then, on app start up, hibernate fails to create the table that contains the clob.

Has anyone else had problems like this with MySQL or postgres?

[rcgeorge23]

@delgad9, thanks for your reply by the way. I will try to create a new project with the script you provide in your blog...

[delgad9]

@rcgeorge23,

1) I believe MySql doesn't have the referred issue...
2) On the other hand: an effective way to avoid this time consuming issues from the RDBM legacy implementations is going straight to NOSQL. If interested review my article at http://pragmatikroo.blogspot.com/201...-based-on.html.


3) B. Roogards
jD

[jbbarquero]

Hello rcgeorge23

[...]
If I use postgresql, hibernate will create the tables but I run into the auto-commit error when I try to persist stuff.

If I use MySQL then, on app start up, hibernate fails to create the table that contains the clob.

Has anyone else had problems like this with MySQL or postgres?

Hello rcgeorge23.

Yes, I have had similar problems.

Actually, I have no problems with MySQL. I've followed the delgad9's tutorial, that uses a byte[] for storing the BLOB column.

But with PostgreSQL the problem arises.

If you have the JPA transaction manager (the one that Roo creates by default) you only have to ensure that you're calling the Entity's methods in a transaction.

Just use the @Transaction annotation. The integration tests do that. But take care of your own code.

However, I'm trying with JTA (using Atomikos) and I can't solve the problem (it implies that I'm doing something wrong with JTA, but it's frustrating)

[rcgeorge23]

Thanks @jbbarquero and @delgad9...

It's been a while since I worked on the project, but I also eventually managed to get it working with MySQL.

I've had a lot of problems using postgres in the past, which is a shame because I continually hear about what a great database it is. Just not that great for me!

[jbbarquero]

Worst than that is that I'm trying MySQL because PostgreSQL doesn't work well with BLOBs because Oracle 8 (the actual database in production) had this problem:

Caused by: java.sql.SQLException: operación no permitida: streams type cannot be used in batching
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
	at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:3775)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:175)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch(DelegatingPreparedStatement.java:175)
	at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:53)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2434)
	... 47 more

And I wanted to know whether my code was wrong or there's any bug with JPA/Hibernate/Oracle 8.

Thanks WORA (sarcasm here)