Solved

How to insert a clob in Java using getCharacterStream?

Posted on 2001-06-26
6
838 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
Comment Utility
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
Comment Utility
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
Comment Utility
I figured it out !

Thanks anyways!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:Mindphaser
Comment Utility
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
Comment Utility
MindPhaser - I have posted the code of what I did.  
0
 

Author Comment

by:sdesar
Comment Utility
Here are the excellent points!
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
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:
This video teaches viewers about errors in exception handling.

762 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

11 Experts available now in Live!

Get 1:1 Help Now