7

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.

Codo
  • 75,595
  • 17
  • 168
  • 206
Bugalugs Nash
  • 492
  • 1
  • 6
  • 21
  • You can simply use `setString()` on the `PreparedStatement`. No need to create the intermediate `Clob` instance. –  Sep 22 '13 at 08:44
  • 1
    @BugalugsNash: You incorrectly assume that Clob.toString() returns the Clob contents. That's not the case. Your debug output therefore doesn't help diagnosing the problem. And I'm not sure you have a problem at all. – Codo Sep 22 '13 at 08:47
  • @a_horse_with_no_name I'm trying to insert a row into a table which has Clob as the data type, so I'm using a prepared statement with a variable '?', and then trying to bind a Clob to that variable. I don't understand what you're suggesting, but I assume it doesn't apply to me. – Bugalugs Nash Sep 22 '13 at 08:47
  • @Codo how do I access the clob contents? the toString() method is what google suggests, and it returns a string so I don't see the problem – Bugalugs Nash Sep 22 '13 at 08:49
  • As I said: there is no need to use the `Clob` instance. `setString()` **directly** on the statement will work just fine. See my answer. –  Sep 22 '13 at 08:55

3 Answers3

9

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();
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);
}
Vasil Lukach
  • 3,658
  • 3
  • 31
  • 40
2

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.

Al W
  • 89
  • 3