Re: some problem with CLOB

From:

"relogout@gmail.com" <relogout@gmail.com>

Newsgroups:

comp.lang.java.programmer

Date:

Thu, 11 Sep 2008 18:24:06 -0700 (PDT)

Message-ID:

<45f68f03-c02c-4206-ae34-45cf6e7ccc02@s20g2000prd.googlegroups.com>

On Sep 10, 4:22 pm, Thomas Kellerer <YQDHXVLMU...@spammotel.com>
wrote:

relog...@gmail.com, 10.09.2008 04:35:

I have a oracle table with a CLOB field named NEWS_CONTETN, and I want
put some news information which may be larger than 4k, I have searched
for some example and take following steps:

1. insert an empty clob to the table
sql1="insert into TAB1(id,COL_CLOB) values(1,empty_clob())";

2. initialize a CLOB variable and select the clob field for update

sql2="select COL_CLOB from TAB1 where id=1 for update";
oracle.sql.CLOB clob=null;
if(rs.next()){
clob=(oracle.sql.CLOB)rs.getClob("COL_CLOB");
}

3. put the news information to the CLOB variable
===================
HERE IS MY PROBLEM
in step 3, i have tried 3 ways, but it didn't work :(
===================
(1) clob.putChars(1,newsStr.toString().toCharArray()); //newsStr is
a string
(2) java.io.OutputStream out = clob.setAsciiStream(0);
(3) Writer outStream = clob.getCharacterOutputStream();

4. update the table
sql3="update TAB1 set COL_CLOB=? where id='1'";

I can't use any method of oracle.sql.CLOB, and get an exception null
what should i do? pls anyone help me, thanks in advance!

First of all: make sure you use an Oracle 10.x driver. The Oracle 9.x dri=

vers do not support LOBs in a decent fashion.

1) Insert (or update) a CLOB

String sql = "insert into tab1 (col_clob) values (?)";
String myLongClobContent = " ... whatever ...";
StringReader r = new StringReader(myLongClobContent);
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setCharacterStream(1, r, myLongClobContent.length());
stmt.executeUpdate();
connection.commit();

2) to read a CLOB

String sql = "SELECT col_clob FROM tyb1";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next())
{
  Reader in = rs.getCharacterStream(1);
  // read from the stream into a String variable

  // or simply use getString(1), I think that is supported by newer dri=

vers as well.

}

Again: make sure your driver version is at least 10.1

Thomas

Thank you very much, Thomas! I use setCharacterStream() instead, and
it works.