Solved

Insert a clob data into database.

Posted on 2006-10-30
4
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
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…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This video teaches viewers about errors in exception handling.
Suggested Courses

623 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