10

I'm trying to read a clob from postgreSQL DB, change it, and write it back.

I was able to read the clob successfully using the following code:

PreparedStatement statement = connection.prepareStatement("SELECT clob_column from data where id = 1");
ResultSet executeQuery = statement.executeQuery();
executeQuery.next()
Clob fetchedClob = executeQuery.getClob("clob_column");

But when I'm trying to create a new clob with the new data using:

Clob newClob = connection.createClob();

I'm getting the following error:

java.lang.AbstractMethodError: com.mchange.v2.c3p0.impl.NewProxyConnection.createClob()Ljava/sql/Clob;

Moreover, If I try just to edit the fetched clob, using:

fetchedClob.setString(0, "new string");

I'm getting the following error:

Method org.postgresql.jdbc4.Jdbc4Clob.setString(long,str) is not yet implemented.

Any idea?

Update: here is the table definition

CREATE TABLE data ( id bigint NOT NULL, clob_column text, );

Thanks

Ben Bracha
  • 1,377
  • 2
  • 15
  • 28

1 Answers1

8

No need to use getClob().

ResultSet.getString() and setString() work perfectly fine on text columns (PostgreSQL does not have a clob datatype so I assume you are using text)

  • I am using text. When I tried to use getString I get back a number which I don't understand its meaning (maybe the actual data length?). Only when I do getClob and look at its character stream (getCharacterStream) I get the full XML which is saved in the DB. – Ben Bracha Aug 27 '12 at 16:08
  • @BenBracha: Then you are not showing us everything. `getString()` definitely works fine with `text`columns. Please show us your table definition (edit your question). –  Aug 27 '12 at 16:10
  • Updated above. I now also see that org.postgresql.jdbc3.AbstractJdbc3Clob which is used by my application doesn't implement all set* for Clob. What should I do? Also I should add we use Hibernate, and the matching entity for this table has it column_text field marked with @Lob – Ben Bracha Aug 27 '12 at 16:37
  • @BenBracha: I don't know Hibernate. But if you don't get the correct value using `getString()` then there is something wrong with your program. The JDBC driver works correctly (you **are** using a current version, are you?) –  Aug 27 '12 at 16:45
  • @BenBracha: I have seen several discussion regarding Hibernate and CLOB columns. Why don't you simply annotate it as `String` (if that exists)? Then Hibernate should use `getString()`. Another option might be to annotate it as `Clob`(if there is such a thing) –  Aug 27 '12 at 16:58
  • There is no such annotations I'm aware of. Can you FW me to those discussions? – Ben Bracha Aug 28 '12 at 08:00
  • @BenBracha: There is no `String` annotation?? How do you annotate regular `varchar` columns? –  Aug 28 '12 at 08:03
  • @a_horse_with_no_name Postgres does have a Clob data type now. – Tino M Thomas Apr 22 '18 at 06:59