Solved

How to insert a clob in Java using getCharacterStream?

Posted on 2001-06-26
6
848 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Industry Leaders: 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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
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…
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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

734 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