Converting from String to Clob using setString() not working

Question

I am trying to convert a String into a Clob to store in a database. I have the following code:

Clob clob = connection.createClob();
System.out.println("clob before setting: " + clob);
clob.setString(1,"Test string" );
System.out.println("clob after setting: " + clob);
System.out.println("clob back to string: " + clob.toString());

When I run this the Clob is not being set, the output is as follows:

clob before setting: org.apache.derby.impl.jdbc.EmbedClob@1f5483e
clob after setting: org.apache.derby.impl.jdbc.EmbedClob@1f5483e
clob back to string: org.apache.derby.impl.jdbc.EmbedClob@1f5483e

Everywhere I look says to use the setString method, I have no idea why this isn't working for me.

Answer 1

You don't need the intermediate Clob instance, just use setString() on the PreparedStatement:

PreparedStatement stmt = connection.prepareStatement("insert into clob_table (id, clob_column) values (?,?)";
stmt.setInt(1, 42);
stmt.setString(2, "This is the CLOB");
stmt.executeUpdate();
connection.commit();

Answer 2

Not sure if it works for derby, but for hibernate you can use:

public Clob createClob(org.hibernate.Session s, String text) {
    return s.getLobHelper().createClob(text);
}

Answer 3

What you are reading from your System.out.println statements are not indicative of what is actually happening in the Clob. You are simply reading out the default java.lang.Object.toString() method of the Clob, which in this case is outputting the instance ID, and that does not change no matter what you put in it.

You are using the Clob properly to load the String on to it, but not read the String value back. To read the String value from a clob use...

Clob clob = connection.createClob();
clob.setString(1,"Test string" );
String clobString = clob.getSubString(1, clob.length())
System.out.println(clobString);

BTW: if you are using large strings, you DO want to convert your String to a Clob, if the String is larger than the Oracle varchar2 limit and you send a simple String it could truncate the input or blow up. If the Oracle proc calls for a Clob, give it one.