wood burning stoves 2.0*
The moose likes JDBC and the fly likes Converting a String to Clob for insert into DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Converting a String to Clob for insert into DB" Watch "Converting a String to Clob for insert into DB" New topic
Author

Converting a String to Clob for insert into DB

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
I have a web based application that allows people
to enter biographical info. The users should also
be able to access their bio information and make
changes. In order to present the data it must be
a String.

I am using a database table that defines a bio field
as a CLOB data type (Oracle 8i release 8.1.5). I
retrieve the value from the database using the
ResultSet method getClob storing it a Clob object.
I then convert it to a String object using the Clob
method getSubString(). No problem!
The problem comes when people try to create
new records (INSERT) into the CLOB column
and the length > 4000 characters.
I am currently just trying to insert the String value
into the CLOB column. This works fine if the length
< 4000, but if it is longer then an SQL exception is
generated and the insert fails.
How can I convert the String object into a Clob
object that can be written to the database?
bill bozeman
Ranch Hand

Joined: Jun 30, 2000
Posts: 1070
I am not sure of the answer, but just as a suggestion, you may want to store these as text files instead of in the database. I did something similar once and at first I was keeping them in the DB, but ran into too many problems including performance. So I changed it so I had a field in the db that pointed to where the text file existed (or just have the text file be named after the primary key in the table) and then called the text file.
Don't know if that is what you want to do, but it worked for me.
Bill
prabhat kumar
Ranch Hand

Joined: Apr 11, 2001
Posts: 114
First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:
// Select LOB locator into standard result set.
ResultSet rs =
stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{
// Get LOB locators into Java wrapper classes.
java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);
(...process...)
}
If you have an OracleCallableStatement object ocs and a CLOB named my_clob, then input the CLOB to the stored procedure proc as follows:
OracleCallableStatement ocs =
(OracleCallableStatement)conn.prepareCall("{call proc(?))}");
ocs.setClob(1, my_clob);
ocs.execute();
To read from a CLOB, use the getAsciiStream() or getCharacterStream() method of an oracle.sql.CLOB object to retrieve the entire CLOB as an input stream. The getAsciiStream() method returns an ASCII input stream in a java.io.InputStream object. The getCharacterStream() method returns a Unicode input stream in a java.io.Reader object.
As with any InputStream or Reader object, use one of the overloaded read() methods to read the LOB data, and use the close() method when you finish.
You can also use the getSubString() method of oracle.sql.CLOB object to retrieve a subset of the CLOB as a character string of type java.lang.String.
To write to a CLOB, use the getAsciiOutputStream() or getCharacterOutputStream() method of an oracle.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. The getAsciiOutputStream() method returns an ASCII output stream in a java.io.OutputStream object. The getCharacterOutputStream() method returns a Unicode output stream in a java.io.Writer object.
As with any OutputStream or Writer object, use one of the overloaded write() methods to update the LOB data, and use the flush() and close() methods when you finish.
The following example uses the getCharacterStream() method to read CLOB data into a Unicode character stream. It then reads the character stream into a character array (returning the number of characters read, as well).
// Read CLOB data from CLOB locator into Reader char stream.
Reader char_stream = my_clob.getCharacterStream();
char [] char_array = new char [10];
int chars_read = char_stream.read (char_array, 0, 10);
...

The next example uses the getAsciiStream() method of the oracle.sql.CLOB class to read CLOB data into an ASCII character stream. It then reads the ASCII stream into a byte array (returning the number of bytes read, as well).
// Read CLOB data from CLOB locator into Input ASCII character stream
Inputstream asciiChar_stream = my_clob.getAsciiStream();
byte[] asciiChar_array = new byte[10];
int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);
Writing CLOB Data
Use the getCharacterOutputStream() method or the getAsciiOutputStream() method to write data to a CLOB. The getCharacterOutputStream() method returns a Unicode output stream; the getAsciiOutputStream() method returns an ASCII output stream.
The following example reads a vector of data into a character array, then uses the getCharacterOutputStream() method to write the array of character data to a CLOB. The getCharacterOutputStream() method returns a java.io.Writer instance in an oracle.sql.CLOB object, not a java.sql.Clob object.
java.io.Writer writer;
// read data into a character array
char[] data = {'0','1','2','3','4','5','6','7','8','9'};
// write the array of character data to a CLOB
writer = ((CLOB)my_clob).getCharacterOutputStream();
writer.write(data);
writer.flush();
writer.close();
...
this example reads a vector of data into a byte array, then uses the getAsciiOutputStream() method to write the array of ASCII data to a CLOB. Because getAsciiOutputStream() returns an ASCII output stream, you must cast the output to a oracle.sql.CLOB datatype.
java.io.OutputStream out;
// read data into a byte array
byte[] data = {'0','1','2','3','4','5','6','7','8','9'};
// write the array of ascii data to a CLOB
out = ((CLOB)clob).getAsciiOutputStream();
out.write(data);
out.flush();
out.close();
hope it will be of some help

Prabhat kumar
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
I try to use something, like you describe, for inserting ORACLE CLOB but Oracle tell me that the update can not be done because the row must be lock. so i try a select .... where ....for update
but oracle jdbc driver do not reconize the syntax but if i use the same query on sqlPlus it works . So can you help me!

------------------
Benjamin l´┐Żonard
evisor
Mauro Velasco
Greenhorn

Joined: May 13, 2003
Posts: 7
you know how make a string to clob?
Regards
Mauro
Mauro Velasco
Greenhorn

Joined: May 13, 2003
Posts: 7
you know how make a string to clob? with jdk 1.3
Regards
Mauro
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Converting a String to Clob for insert into DB
 
Similar Threads
ORA-01704: String Literal Too Long
difficulty in inserting value into oracle "long" datatype column
CLOB in Hibernate
use of CLOB,help me.
Problem while writing clob coulmn