Solved

How to insert a clob in Java using getCharacterStream?

Posted on 2001-06-26
6
842 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PDFBox - convert image to PDF, PDF resolution 3 117
JDeveloper 12c for 32 bit 4 71
eclipse argument 14 53
custom annotations 9 32
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
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 will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to implement Singleton Design Pattern in Java.

920 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

13 Experts available now in Live!

Get 1:1 Help Now