Solved

How to insert a clob in Java using getCharacterStream?

Posted on 2001-06-26
6
849 Views
Last Modified: 2007-12-19
I have a JSP Page that allows the user to enter a description.
If the Description is > 4000 characters then the insert to database fails.  Thereofore, if its >4000 chars then I want to use this new function
writeStringToClob() that writes the CharcterStream.



I looked up Java site they have the following code-

  /**
  * Retrieve the character data from the input CLOB, save in a
  * StringBuffer and display the StringBuffer contents in GUI
  **/
  void writeCLOB(CLOB p_clob, String p_airPCode) {
    try {
      // Open a stream to read CLOB data
      Reader l_clobStream = p_clob.getCharacterStream();

      // Holds the CLOB data when the CLOB stream is being read
      StringBuffer l_suggestions = new StringBuffer();

      // Read from the CLOB stream and write to the stringbuffer
      int l_nchars = 0; // Number of chanracters read
      char[] l_buffer = new char[10];  //  Buffer holding characters being transferred
      while ((l_nchars = l_clobStream.read(l_buffer)) != -1) // Read from CLOB
        l_suggestions.append(l_buffer,0,l_nchars); // Write to StringBuffer

      l_clobStream.close();  // Close the CLOB input stream
      m_GUI.m_sugArea.append(new String(l_suggestions)); // Display in GUI
    } catch (Exception ex) { // Trap SQL and IO errors
      m_GUI.putStatus("Error in getting and drawing CLOB for the airport, "+p_airPCode+":");
      m_GUI.appendStatus(ex.toString());
    }
  }
---

I need to do the reverse of this...
I want to check if >4000 characters are entered then
a subroutine similar to above called
writeStringToClob (Clob p_clob, String p_descr)

This should first Select that Row and update it
like this -
Clob notes = rs.getClob("NOTES");
  PreparedStatement pstmt = con.prepareStatement(
          "UPDATE DESCR SET COMMENTS = ? WHERE DESCR > 4000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();


Could someone please help me ?

Thanks.
0
Comment
Question by:sdesar
[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
6 Comments
 
LVL 35

Accepted Solution

by:
TimYates earned 100 total points
ID: 6230669
I guess you are using oracle...

If so, you set clobs thusly:

      connection.setAutoCommit( false );
      statement = connection.prepareStatement("INSERT INTO CALENDAR (CALDATE, XML) VALUES ( ?, empty_clob() )");
      statement.setTimestamp(1, caldate);
      if (statement.executeUpdate() != 1)
      {
        throw new CreateException("Error adding row");
      }
      statement.close();
      statement = null;

      // Ok, now we have added the row, we can set the clob data....
      // 1: lock the row...
      statement = connection.prepareStatement( "SELECT XML FROM CALENDAR WHERE CALDATE = ? FOR UPDATE" ) ;
      statement.setTimestamp( 1, caldate );
      rslt = statement.executeQuery() ;
      if( rslt.next() )
      {
        CLOB cl = ((OracleResultSet)rslt).getCLOB( 1 ) ;
        wr = cl.getCharacterOutputStream() ;
        wr.write( xml );
        wr.close();
      }
      rslt.close();
      rslt = null ;
      statement.close() ;
      statement = null ;

(I have missed out the bits where you set up your connection, and the finally block which ensures all your connections, resultsets and statements are closed...but htis should point you in the right direction)

have a look here

http://wamoz.com/JDBC_and_Oracle_LOB.asp

and this http://www.classicity.com/oracle/htdocs/forums/ClsyForumID124/3.html uses BLOBs in much the same way...

Good Luck!!

Tim.

0
 

Author Comment

by:sdesar
ID: 6233398
Thanks for the above comments.
Here are some more details of what I am doing-
I am using ORACLE and I want to use the generic JDBC API.

Currently,
I have a JSP Page that lets a user enter a DESCRIPTION in the textarea.
If the user enters 4000 chars then the INSERT succeeds.
The DESCRIPTION field is a CLOB.

If its >4000 then insert fails due to oracle's string literal limitation of 4K.

So, if the user enter > 4.
I want to insert the 4k as usual the rest of the data should be inserted by doing an UPDATE-

here's what I currently do to getStringFromClob-
/**
 * Get clob from Oracle as Unicode stream.  Uses 8.1.6 JDBC 2.0 driver,
 *
 *
 * @return the string in the Clob, or null on error or empty clob
 */
public static String getStringFromClob( Clob clob ) {
    Reader       chr_instream;                // Unicode clob reader
    char[]       chr_buffer = {};                  // Clob buffer

    if ( null == clob ) {
        return null;
    }
    // Now get as a unicode stream.
    try {
        if (clob.length() <= 0) {
            return null;
        }
        chr_buffer = new char[(int)clob.length()];
        chr_instream = clob.getCharacterStream();
        chr_instream.read( chr_buffer );
        chr_instream.close();
    } catch (Exception e) {
        //cdebug.println ( "getStringFromCLob caught: " + e );
        return e.toString ();
    }

    return new String(chr_buffer);
}


And then I just do an INSERT.


But now if >4000 chars...
I am stuck!

here's what I was thinking  and wanted to know how to do it..

If <4000 then the INSERT should occur as normal.
else >4000 call writeStringToClob()

INSERT the first 4000 chars as normal the rest should call the method writeStringToClob ( I am not sure how to write this )... here's the pseudocode-

String s_description = (String) ox_params.getParameter ( ox_params.DESCRIPTION );
int len = s_description.length();
if (len > 4000){
   String s_desc = s_description.substring(4000);
   writeStringToClob(s_desc);


}


public String writeStringToClob(String p_descr) {

    try {
           Clob p_clob;

           StringBuffer l_descr = new StringBuffer();


           PreparedStatement pstmt = con.prepareStatement
               ( "UPDATE descr from issueTable (clob) VALUE (?)  where descr.length > 4000 ");

           pstmt.setClob(1, p_clob);
           pstmt.excuteUpdate();
           }
        } catch (Exception ex) {
           cebdug.println ( "NewIssue writeStringToClob caught error in getting the String: " + p_descr);
           cdebug.println ("NewIssue caught error: " + ex.toString() );

        }
}

Thanks!
Awaiting a response!
0
 

Author Comment

by:sdesar
ID: 6270740
I figured it out !

Thanks anyways!
0
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!

 
LVL 6

Expert Comment

by:Mindphaser
ID: 7047540
sdesar

If you don't provide feedback to the experts how are they supposed to help you any further? And then
you just say: I fixed it myself ...

** Mindphaser - Community Support Moderator **
0
 

Author Comment

by:sdesar
ID: 7067697
MindPhaser - I have posted the code of what I did.  
0
 

Author Comment

by:sdesar
ID: 7067703
Here are the excellent points!
Thanks
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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

630 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