Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert a clob data into database.

Posted on 2006-10-30
4
Medium Priority
?
963 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 400 total points
ID: 17832316
0
 
LVL 5

Accepted Solution

by:
kannan_ekanath earned 1600 total points
ID: 17832357
0
 
LVL 8

Expert Comment

by:redpipe
ID: 17832484
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
ID: 17834206
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This video teaches viewers about errors in exception handling.
Suggested Courses

927 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