Solved

Insert a clob data into database.

Posted on 2006-10-30
4
880 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
ID: 17832316
0
 
LVL 5

Accepted Solution

by:
kannan_ekanath earned 400 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Java SE 8u111  Lot of stuff broke 11 68
xampp tool 12 47
java jdbc batch example 8 30
What's wrong with this code? 4 22
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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 …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

813 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

12 Experts available now in Live!

Get 1:1 Help Now