List:MySQL and Java« Previous MessageNext Message »
From:Mufaddal Khumri Date:September 25 2003 5:43am
Subject:CLOB question ...
View as plain text  
Hi,

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:16 
53)
	at  
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:14 
92)
	at ClobTest.main(ClobTest.java:21)

I am guessing "empty_clob()" might be wrong ??? If so , what do i do to  
get a clob object ? I need to store a java String inside a Clob object.

Thanks.

Thread
performance questionJeff Mathis22 Aug
  • Re: performance questionMark Matthews22 Aug
    • CLOB question ...Mufaddal Khumri25 Sep
      • Re: CLOB question ...Mufaddal Khumri25 Sep
        • Second approach ... Re: CLOB question ...Mufaddal Khumri25 Sep
          • How does one ...Mufaddal Khumri9 Oct
            • Re: How does one ...Dirk Hillbrecht9 Oct
              • Re: How does one ...Mufaddal Khumri9 Oct
                • Re: How does one ...Dirk Hillbrecht9 Oct
  • Re: performance questionJeff Mathis22 Aug
  • Re: performance questionJeff Mathis30 Sep
RE: Second approach ... Re: CLOB question ...John Beveridge26 Sep
Re: How does one ...Fouche du Preez14 Oct