Solved

Insert a clob data into database.

Posted on 2006-10-30
4
849 Views
Last Modified: 2008-02-01
Hello,
I have a <b>empty</b> table with some fields that one of them has Clob type in Oracle.
How can I insert a new row in it and fill its clob type field with a given string?
I must prepare a clob object and fill my given string to it. But I couldn't create clob object.

Thanks
-FA
0
Comment
Question by:Farzad Akbarnejad
4 Comments
 
LVL 92

Assisted Solution

by:objects
objects earned 100 total points
Comment Utility
0
 
LVL 5

Accepted Solution

by:
kannan_ekanath earned 400 total points
Comment Utility
0
 
LVL 8

Expert Comment

by:redpipe
Comment Utility
Can be done in many ways. The code beneath shows how I do it. Mind you that I have not included initialization of the java.sql.Connection object...
public void SetClob(String msg) throws Exception {
  Connection                            conn                  = null;
  String                                sqlTekst              = null;
  Statement                             stmt1                 = null;
  Statement                             stmt2                 = null;
  ResultSet                             rs                    = null;
  Clob                                  msgClob               = null;

  try{
    sqlTekst = "INSERT INTO myTable (col_id, myClobColumn) VALUES (1, EMPTY_CLOB())";
    stmt1 = conn.createStatement(sqlTekst);
    numRows = stmt1.executeUpdate(sqlTekst);

    sqlTekst = "SELECT myClobColumn FROM myTable WHERE col_id = 1 FOR UPDATE";
    stmt2 = conn.createStatement();
    rs = stmt2.executeQuery(sqlTekst);
    while (rs.next()) {  
      msgClob = rs.getClob("myClobColumn");
    }

    java.io.Writer wrXml = ((weblogic.jdbc.common.OracleClob) msgClob).getCharacterOutputStream();
    char[] xmlChars = msg.toCharArray();
    wrXml.write(xmlChars);
    wrXml.flush();
    wrXml.close();


private String GetClob(){
  Connection                            conn                  = null;
  String                                sqlTekst              = null;
  Statement                             stmt                  = null;
  ResultSet                             rs                    = null;
 
  stmt = conn.createStatement();
  rs = stmt.executeQuery("SELECT myClobColumn FROM myTable");

  if(rs.next()){
    // Retrieve the value of the designated column as a Clob object
    java.sql.Clob msgClob = rs.getClob("myClobColumn");  
    // Holds the Clob data when the Clob stream is being read
    StringBuffer sb = new StringBuffer();
    // Open a stream to read Clob data
    java.io.Reader clobStream = msgClob.getCharacterStream();
    // Read from the Clob stream and write to the stringbuffer
    int numChars = 0;
    // Buffer holding characters being transferred
    char[] buffer = new char[10];
    while((numChars = clobStream.read(buffer)) != -1){  // Read from Clob
      sb.append(buffer, 0, numChars);                   // Write to StringBuffer
    }
    clobStream.close();                                 // Close the Clob input stream
    return sb.toString();
  } else {
    return null;
  }
}
0
 
LVL 14

Author Comment

by:Farzad Akbarnejad
Comment Utility
redpip,
Using your code for me reason is hard because I want to fill data of some oracle tables from SQL Server tables with same name and data types. I want to fill club fields with text type in sql server.

I used kannan_ekanath  solution but the object's link is great so I split points between you.

-FA

0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This video teaches viewers about errors in exception handling.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now