How to insert clob data??

Can any one tell me in details how to populate clob column in a table using sql??I tried simple sql statements but found out that simple sql statements won't allow more than 4000 characters but I need to insert more than 4000 characters.I have heard that clob fields can hold 4 gb of data and need pl/sql to avail that feature.Can any one tell me how to create pl/sql blocks and statements.I want to insert value of a textarea into clob column of a table.So please some one tell me what should I do??

View Answers

April 12, 2012 at 1:50 PM

No it is not the way I actually want.I need the pl/sql code.Actually I am fetching the data from a form page.I can't use a file to fetch the data.All I need is to get the data from text area and then insert the data into table through pl/sql code.

April 12, 2012 at 12:52 PM

In MySql database,TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT can be taken as CLOB type. Here we have used longtext datatype.

1)create a table article in database.

              CREATE TABLE `article` (                 
               `ID` int(11) NOT NULL auto_increment,  
               `Subject` varchar(256) NOT NULL,       
               `Body` longtext,                       
               PRIMARY KEY  (`ID`)                    
             )

2)Here is the java code:

import java.sql.*;
import java.io.*;
import java.net.*;
class  InsertClobData
{
    public static void main(String[] args){
        Connection con = null;

  String fileName = "c:/data.dat";
  try {
  Class.forName("com.mysql.jdbc.Driver");
  con =DriverManager.getConnection ("jdbc:mysql://localhost:3306/test", "root", "root");
  String clobData = getClobDataAsString(fileName);
  PreparedStatement ps = con.prepareStatement("insert into article(subject, body)values (?, ?)");
  ps.setString(1, fileName);
  ps.setString(2, clobData);
  ps.executeUpdate();
  System.out.println("Clob data is instered successfully");
  }
  catch (Exception e) {
  e.printStackTrace();
   }
  }

  public static String getClobDataAsString(String filename) throws Exception {
    InputStream is = null;
    try {
      is = new FileInputStream(filename);
      int BUFFER_SIZE = 1024;
      ByteArrayOutputStream output = new ByteArrayOutputStream();
      int length;
      byte[] buffer = new byte[BUFFER_SIZE];
      while ((length = is.read(buffer)) != -1) {
        output.write(buffer, 0, length);
      }
      return new String(output.toByteArray());
    } finally {
      is.close();
    }
    }
}