aspose file tools*
The moose likes JDBC and the fly likes SetCharachterStream seems not to work with MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SetCharachterStream seems not to work with MySQL" Watch "SetCharachterStream seems not to work with MySQL" New topic
Author

SetCharachterStream seems not to work with MySQL

Eric Lemaitre
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

Hi all,

I have an existing MySQL CLOB which works with a TEXT set, I want to append some text at the end like this:

Writer writer = clob.setCharacterStream(clob.length());
writer.write("AppendedString");
writer.flush();
writer.close();

But when I read the CLOB again I get the old value, String has not been appended.
This is very basic JDBC stuff and it should work with all DBs, at lead with Oracle it works, so has someone an idea of what is wring with MySQL?

TIA, best regards.


Eric LEMAITRE
CNAM IT Engineer, MS/CS (RHCE, RHCX, SCJA, SCJP, SCJD, SCWCD, SCBCD, SCEA, Net+)
Free Online Tutorials: http://www.free-tutorials-online.net/
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

when you say read again, do you mean read from the database, or just using java to stream over that variable again?

If it is from the database you don't show us the insert/update statement, and then the next select statement where you get the clob again.
Eric Lemaitre
Ranch Hand

Joined: Jul 03, 2004
Posts: 538

Hi Wendy,

Wendy Gibbons wrote:when you say read again, do you mean read from the database, or just using java to stream over that variable again?
If it is from the database you don't show us the insert/update statement, and then the next select statement where you get the clob again.


I mean read again from DB, by making a new PreparedStatement and selecting the very same CLOB again, so there was NO insert/update statement at all.

I found my issue, but I had to use a decompiler to find what was wrong.

So I have read a CLOB the "normal way" with a select and a ResultSet, and I make:

Clob clob = resultSet.getClob(1);
..........
Writer writer = clob.setCharacterStream(clob.length());
writer.write("AppendedString");
writer.flush();
writer.close();

With Oracle the CLOB is updated BOTH in memory & DB, but with MySQL BLOB is updated in memory only.
For MySQL flush() & close() do nothing, their code is empty.

So for MySQL to update the CLOB BOTH in memory & DB like Oracle does, you should have such code:

Clob clob = resultSet.getClob(1);
..........
Writer writer = clob.setCharacterStream(clob.length());
writer.write("AppendedString");
resultSet.updateBlob("clob_culumn", clob);
resultSet.updateRow();

Best regards.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SetCharachterStream seems not to work with MySQL