Wednesday, May 21, 2008

Mapping a document from byte[] to BLOB in Grails on Oracle

For CDR (http://code.google.com/p/configuration-data-repository/) I had to upload a document of any sort into an oracle database. Using a byte[] type worked just fine with the embedded HSQL database but when I deployed it to Oracle 9i or 11g (that's our platforms) it failed horribly. This was apparently due to hibernate creating in Oracle a 'RAW' object with a maximum size of 255. I'm not an Oracle DBA so I don't know if that is appropriate but it wasn't working.

My second thought was to change the object type in my domain object to blob:

static mapping = {
document type: 'blob'
}


But that didn't work either because it couldn't automatically convert a byte[] to a BLOB. I was able to find others who had had similar issues on the Grails nabble and a blog posting by a gentleman named Ryan using hibernate to convert byte[] to BLOB were old (by Grails standards) and I wanted to see if Grails could handle this using the new ORM DSL instead of hibernate mapping files. This is what I came up with:


import java.sql.Blob
import java.sql.SQLException
import org.hibernate.Hibernate

class Documentation extends ConfigurationItem {
int docVersion
byte[] document
Blob documentBlob
String fileType
String fileName
String fileSize
String title
String abstraction
DocumentationType documentationType

Date dateCreated
Date lastUpdated

static transients = ["document"]
static belongsTo = DocumentationType
static constraints = {
docVersion(nullable: true)
document(nullable: true)
documentBlob(nullable: true)
fileType(nullable: true)
fileName(nullable: true)
fileSize(nullable: true)
title(nullable: true)
abstraction(nullable: true)
documentationType(nullable: true)
}

static mapping = {
documentBlob type: 'blob'
}

def getDocument() {
if (documentBlob == null)
return null;
return toByteArray(getDocumentBlob());
}

def setDocument(document) {
setDocumentBlob(Hibernate.createBlob(document));
}

boolean equals(obj) {
if (this == obj) return true;
if (!obj || obj.class != this.class) return false;
return id?.equals(obj.id)
}

int hashCode() {
return id ? id.hashCode() : super.hashCode()
}

private byte[] toByteArray(Blob fromBlob) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
return toByteArrayImpl(fromBlob, baos);
} catch (SQLException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
if (baos != null) {
try {
baos.close();
} catch (IOException ex) {
}
}
}
}

private byte[] toByteArrayImpl(Blob fromBlob, ByteArrayOutputStream baos) {
byte[] buf = new byte[4000];
InputStream is = fromBlob.getBinaryStream();
try {
while (true) {
int dataSize = is.read(buf);

if (dataSize == -1)
break;
baos.write(buf, 0, dataSize);
}
} finally {
if (is != null) {
try {
is.close();
} catch (IOException ex) {
throw ex;
}
}
}
return baos.toByteArray();
}

String toString() {
return "${name}"
}
}

Notice all I had to do was create an ORM mapping for documentBlob. So far so good! It seems to be working in our testing in both HSQL and Oracle 9i. Has anyone else ran into this issue?

7 comments:

Mark Lee said...

I tried using this method on Grails 1.1.1 with Oracle 8i database. At first I tried setting the MultipartFile name to be the same as the Document (byte[]) but it complained that it cannot find a method setDocument with attributes MultipartFile. Then I renamed the field in the GSP and in the Controller I extracted the file and the inputStream and did instance.document = inputStream. Now it complains with SQLException - could not reset reader. How am I suppose to use this method of mapping byte[] to BLOBs ?

iamsteveholmes said...

Hi Mark!
I haven't tried it yet with Grails 1.1.1. If you don't mind, is it possible to post your code up here? I'm going to be creating a document upload in a day or two here using Grails 1.1.1 and maybe we can compare notes. Hopefully we'll find a solution.
-Steve

Philippe said...

I'm trying this method with Grails 1.1 and Oracle 10g, but when I create my domain object, it complains at this line :
setDocumentBlob(Hibernate.createBlob(document.bytes))

The error I have is :
ERROR util.JDBCExceptionReporter - ORA-01460: unimplemented or unreasonable conversion requested

Any ideas ?

iamsteveholmes said...

Hi Philippe!
Would you mind sending me the source of your domain object? I'm using 1.1.1 and no problems at all. I believe we're on Oracle 10g but not sure. I also recall that the version of your oracle thin driver can matter quite a bit but Hibernate should manage that.
-Steve

Michael Schuenck said...

I don't know about Grails 1.1.1 and previous versions, but I had to fix the method setDocument(document) in order to make it work with Grails 1.2.1. I wrote like this:

setDocumentBlob(Hibernate.createBlob(document.getBytes()));

luciadelaiglesia said...

Mark,
just update Oracle driver to ojdbc6.jar and the mapping byte[] to BLOBs works fine.
Good luck!

iamsteveholmes said...

Lucia, did you use the same method I did here? I haven't used the 6 driver yet but oracles Drivers seem to vary greatly version to version.

Monkey Search