asked on

How to insert a clob in Java using getCharacterStream?

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.

ASKER CERTIFIED SOLUTION

TimYates

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sdesar

ASKER

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!

sdesar

ASKER

I figured it out !

Thanks anyways!

Mindphaser

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 **

sdesar

ASKER

MindPhaser - I have posted the code of what I did.

sdesar

ASKER

Here are the excellent points!
Thanks