• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

String -> MySQL CLOB

 
Mufaddal Khumri
Greenhorn
Posts: 9
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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();
 
Artem Goncharov
Greenhorn
Posts: 1
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33691
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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
Marshal
Posts: 33691
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic