Using Oracle 10g CLOB with Grails 2.0.1

Question

I'm working on a project using Oracle 10g and Grails v2.0.1.

I'm trying to use a CLOB data type for a text input field in my Domain class, and it doesn't seem to be working. My first attempt was based on what I read here about GORM, where is says to use type: 'text', like this example:

class Address {
   String number
   String postCode
   static mapping = {
       postCode type: 'text'
   }
}

Grails mapped that to a LONG data type in my DB, which is not desirable

2nd attempt was to try type: 'clob'. That WAS effective in getting my DB datatype to be CLOB, but resulted in a class cast error because the property itself was defined as a string, i.e. String postCode. (Note that I've never seen type:'clob' in documentation, but I could deduce from the dialect class that clob might be a valid input there)

I subsequently tried defining the property as a java.sql.Clob, i.e. Clob postCode;, and that didn't work at all. No error messages, but nothing was getting persisted to the DB either.

I took a final step of keeping the Clob approach, but using a transient String property in which the getters/setters attempt to map the transient String value to the persistent Clob field. But I cannot figure out how to get my string value into the Clob. Grails doesn't throw an error, but the println() after my attempted assignment never prints. I've tried using myClob.setString(1, theString) to make an assignment, but with no success.

So to make a long story short, I can't seem to use a Clob in my scenario, and I'm wondering if anyone else has seen this and been able to overcome it. If so, can you tell me what I might be doing wrong?

OR... is there a way to override the datatype Grails chooses such that I could force it to map postCode type: 'text' to a CLOB? I'm not proficient with Hibernate, so I'm not sure how to go about that if there's a way.

Side note: prior to our upgrade from Grails 1.3.7 to 2.0.1, I'm pretty sure the type: 'text' did, in fact, map to a CLOB in Oracle. So this might be new to 2.0.1.

Answer 1

I think I found an answer tucked into the documentation on Custom Hibernate Types.

In that case, override the column's SQL type using the sqlType attribute

This appears to be working.

Looks like I'm able to use that to force my DB type to be CLOB while keeping the java type a String. In other words, maybe type chooses both a DB type and a Java type for handling the field? But sqlType gives a little more granularity to specify the DB type to use.

So the sample Domain class above should look like this in my case:

class Address {
    String number
    String postCode
    static mapping = {
        postCode sqlType: 'clob'
    }
} 

I gleaned this from another StackOverflow question on the topic (the question itself clued me in, whereas the accepted answer misled me!):

• How do I get Grails to map my String field to a Clob?

I spent a day trying to figure this all out, and it was incredibly frustrating. So maybe my notes on the topic here will help someone else avoid that experience!

---

And while I'm keeping notes here... this post proved somewhat useful in terms of troubleshooting how to get more specific in my mappings:

• http://omaha-seattle.blogspot.com/2010/02/grails-hibernate-custom-data-type.html

Interesting code from that is reproduced here:

//CONFIG.GROOVY (maps a custom SixDecimal type)
grails.gorm.default.mapping = {
    'user-type'( type: SixDecimalUserType, class: SixDecimal )
}