Reading from a DB2 CLOB into a String with Groovy 1.56

Groovy makes everything easier, so I expected that there would be a simple, Groovy way to read text out of a CLOB field in my DB2 database. So far, however, I haven’t found any reference to one.

The java.sql.Clob API Guide describes three different methods that can be used to read from a CLOB: getAsciiStream(), getCharacterStream() and getSubString(). I worked out a few different uses from Sun’s suggestions:

Using getAsciiStream():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
String valueFromClob = clob.getAsciiStream().getText()
}

Using getSubString():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
String valueFromClob = clob.getSubString(1, clob.length().intValue())
}

Using getCharacterStream():

sql.eachRow("select clob_field from table_x") {row ->
java.sql.Clob clob = (java.sql.Clob) row["clob_field"]
List lines = clob.getCharacterStream().readLines()
StringBuffer contents = new StringBuffer()
lines.each {
contents.append(it)
}
String valueFromClob = contents.toString()
}

I worked those into a handy utility class:


import java.sql.Clob
import org.apache.log4j.Logger

public class ClobUtil {

static String getAsString(Object rsField) {
Logger log = Logger.getLogger("ClobUtil")
int tooBig = (64 * 1024 * 1024)

Clob clob = (Clob) rsField
/*
* In practice, we have found that the JVM has a 64K limit on Strings and will
* throw a java.lang.ClassFormatError when trying to process a longer String
* value. See http://jira.codehaus.org/browse/GROOVY-2382.
*/
int contentSize = clob.length().intValue()
if (clob.length() > tooBig) {
log.warn("Truncating contents at 64K characters. Use getAsCharArray() to read larger values.")
contentSize = tooBig
}

return clob.getSubString(1, contentSize)
}

static char[] getAsCharArray(Object rsField) {
Clob clob = (Clob) rsField
Reader reader = clob.getCharacterStream()
List contents = []
for (i in 1..clob.length().intValue()) {
contents << reader.read()
}
return (char[]) contents.toArray()
}
}

Advertisements

#clob, #db2, #groovy, #groovy-sql