• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SetCharachterStream seems not to work with MySQL

 
Ranch Hand
Posts: 538
Hibernate Eclipse IDE Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 538
Hibernate Eclipse IDE Tomcat Server
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic