Solved

How to insert a clob in Java using getCharacterStream?

Posted on 2001-06-26
6
845 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This video teaches viewers about errors in exception handling.

839 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