String to CLOB with postgreSQL

sql postgresql clob
Ben Bracha picture Ben Bracha · Aug 27, 2012 · Viewed 10.3k times · Source

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

Answer

a_horse_with_no_name picture a_horse_with_no_name · Aug 27, 2012

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)

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More