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.

You can simply use setString() on the PreparedStatement. No need to create the intermediate Clob instance. — user330315
– user330315, Commented Sep 22, 2013 at 8:44
@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
– Codo, Commented Sep 22, 2013 at 8: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
– Bugalugs Nash, Commented Sep 22, 2013 at 8: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
– Bugalugs Nash, Commented Sep 22, 2013 at 8: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. — user330315
– user330315, Commented Sep 22, 2013 at 8:55

user330315 · Accepted Answer · 2013-09-22 08:55:07Z

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();

answered Sep 22, 2013 at 8:55

user330315

Sign up to request clarification or add additional context in comments.

Comments

Vasil Lukach · Accepted Answer · 2013-11-05 18:19:59Z

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);
}

answered Nov 5, 2013 at 18:19

Vasil Lukach

3,7383 gold badges36 silver badges42 bronze badges

Comments

Al W · Accepted Answer · 2018-11-16 13:59:13Z

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.

Collectives™ on Stack Overflow

Converting from String to Clob using setString() not working

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related