Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Convert large string to Clob

 
Alice Zhang
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
My environment is as following:
Oracle 9.0.1
JDBC 2.0
Oracle thin driver
Sun Solaris sparc, OS 9
JDK 1.2
The question is I can not update a Clob before saving it. The input from our web application users sometime can be large, from 100k bytes to MBs. I want to save user's input into CLOB datatype in our Oracle database. Since, I am using JDK1.2, I do not have access to oracle.sql.clob. Do you know how to convert a string to a Clob in Java program and how to prepare a statement before executing a query. I used preparedStatement.setAsciiStream(int index, InputStream x, int length) to prepare my statement, and it doesn't work. I got an error thrown saying Io exception: End of TNS data channel.
Thank you very much for your help.
 
A Tavouille
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To create a Clob, use this instruction :
CLOB newClob = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
newClob.putString(1,value);
.... but don't work with Websphere Datasource
Another solution : insert a 'empty_clob()' in table, then select and update the clob :
Insert: "insert into mytable(primarykey, clob_col) values (?, empty_clob())";
Then select and lock this row : " select clob_col from mytable where primarykey = ? for update"
Get the cob : oracle.sql.CLOB clob=(CLOB)rs.getClob("clob_col")
And update the clob content : fillClob(clob,"xxxx xknsd")
With the fillClob method :
// Utility function to put data in a Clob
static void fillClob(CLOB clob, String value) throws Exception {
clob.trim(0);
Writer outstream = clob.getCharacterOutputStream();
outstream.write(value, 0, value.length());
outstream.close();
}
 
A Tavouille
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This method work nice
/**
* Creer un Clob contenant la valeur pass� en param�tre. Fonctionne uniquement avec Oracle.<br>
* <b><i>Important </i>: Penser � fermer le clob une fois utilis� (ie apres maj ou insert en base)</b>
* <i>Note </i>: Sur Oracle 10g, on peut utiliser la property SetBigStringTryClob=true<br> et utiliser directement ps.setString(i,value)<br>
* dans le Dao<br>
*
* @param conn
* @param value
* @return Clob
* @TODO gerer de facon non proprietaire 'Oracle' des que possible.
*/
protected static Clob createClob(Connection conn,String value) throws SQLException{
CallableStatement stmt = null;
try{
//Creation d'un Clob temporaire
stmt = conn.prepareCall("{ call DBMS_LOB.CREATETEMPORARY(?, TRUE)}");
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CLOB);
stmt.execute();
CLOB newClob = (CLOB)stmt.getObject(1);
newClob.putString(1,value);

return newClob;
}finally{
freeSql(stmt);
}
}
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64827
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"alexis alexis",
We're pleased to have you here with us in the JDBC forum, but there are a few rules that need to be followed, and one is that proper names are required. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.
In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.
Thanks!
bear
Forum Bartender
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic