• 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
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

String -> MySQL CLOB

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
After reading a lot of posts in this forum and else where I am doing the following:
I want to store a huge java String as a CLOB in the MYSQL Database.
I have a table defined like this:
CREATE TABLE CLOBTEST (ID INTEGER NOT NULL AUTO_INCREMENT, DESCRIPTION TEXT, PRIMARY KEY (ID)) TYPE = innoDB;
Next I do this:
PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO CLOBTEST " +"(ID, DESCRIPTION) VALUES (?, empty_clob())");
pstmt1.setInt(1, 2);
pstmt1.executeUpdate();
pstmt1.close();
After this I do this to get the clob objects:

Statement stmt = con.createStatement();

ResultSet rs1 = stmt.executeQuery("SELECT DESCRIPTION from CLOBTEST");
Vector v = new Vector();
while(rs1.next())
v.addElement(rs1.getClob("DESCRIPTION"));
rs1.close();
stmt.close();
Once i have got the clob object, i want to write some data in the clob object from a java string and store the clob to the database
PreparedStatement pstmt = con.prepareStatement("INSERT INTO CLOBTEST " +
"(DESCRIPTION) VALUES (?)");
if(v != null && v.isEmpty() == false)
{
System.out.println("Inside if statement.");

Clob c = (Clob)v.elementAt(0);
c.setString(0, " Hello World ");
pstmt.setClob(2, c);
pstmt.executeUpdate();
}
pstmt.close();
When I run the above code I get the following exception:
java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '())' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1697)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1083)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1142)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1876)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1590)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1653)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1492)
at ClobTest.main(ClobTest.java:21)
I am guessing "empty_clob()" might be wrong since i think it is specific for oracle ??? If so , what do i do to get a clob object from mysql ? I need to store a java String inside a Clob object.
Any help on this is greatly appreciated.
 
Mufaddal Khumri
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A few corrections to the code i pasted earlier:
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root");

PreparedStatement pstmt1 = con.prepareStatement("INSERT INTO CLOBTEST " +
"(ID) VALUES (?)");
pstmt1.setInt(1, 2);

pstmt1.executeUpdate();
pstmt1.close();

Statement stmt = con.createStatement();

ResultSet rs1 = stmt.executeQuery("SELECT DESCRIPTION from CLOBTEST");
Vector v = new Vector();
while(rs1.next())
v.addElement(rs1.getClob("DESCRIPTION"));
rs1.close();
stmt.close();
PreparedStatement pstmt = con.prepareStatement("UPDATE CLOBTEST " +
"SET DESCRIPTION = ? WHERE ID = 2");
if(v != null && v.isEmpty() == false)
{
System.out.println("Inside if statement.");

Clob c = (Clob)v.elementAt(0);
c.setString(1, " Hello World ");

pstmt.setClob(1, c);
pstmt.executeUpdate();
pstmt.close();
}
pstmt.close();

con.close();
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello!
Can I ask You, how You work with Clobs? I don't know, where can I find emptyCLob() method.
 
author & internet detective
Posts: 42162
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Artem,
Welcome to JavaRanch! In the future, please start a new thread for a new question. People will be more likely to see the question that way.

The JDBC tutorial shows how to work with Blobs. Clobs work the same way.
 
Jeanne Boyarsky
author & internet detective
Posts: 42162
937
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From the code in the first post, it looks like empty_clob() is a database function to insert a default non-null value in the CLOB field.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic