aspose file tools*
The moose likes JDBC and the fly likes String -> MySQL CLOB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "String -> MySQL CLOB" Watch "String -> MySQL CLOB" New topic
Author

String -> MySQL CLOB

Mufaddal Khumri
Greenhorn

Joined: Jun 13, 2002
Posts: 9
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

Joined: Jun 13, 2002
Posts: 9
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

Joined: Dec 29, 2005
Posts: 1
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

Joined: May 26, 2003
Posts: 30913
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30913
    
158

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: String -> MySQL CLOB