Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert a clob data into database.

Posted on 2006-10-30
4
Medium Priority
?
951 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
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…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

730 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