Link to home
Start Free TrialLog in
Avatar of skcchan
skcchan

asked on

How to insert CLOB from a Java program via JDBC

Please help. Whenever I insert a clob > 4k, I get an error message listed below:

-----

Oracle 9i (Release 2)
Java 1.4.1
-----

CREATE TABLE mytab (c CLOB);
-----

String s = a_very_long_string_30k_bytes;
Reader reader = new StringReader(s);
PreparedStatement pstmt = conn.prepareStatement
  ("INSERT INTO mytab (c) VALUES (?)");
pstmt.setCharacterStream(1,reader,s.length());
pstmt.execute();
pstmt.close();
conn.close();

-----
Error Messages:

Io exception: Connection reset


java.sql.SQLException: Io exception: Broken pipe
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
        at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:648)
        at oracle.jdbc.driver.OraclePreparedStatement.privateClose(OraclePreparedStatement.java:485)
        at oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatement.java:393)
        at com.rawmart.itools.Import.main(Import.java:168)
ASKER CERTIFIED SOLUTION
Avatar of ypwitkow
ypwitkow

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skcchan
skcchan

ASKER

I tried to use

OraclePreparedStatement and oracle.sql.CLOB bu it does not help.

Can anyone give me an example that insert a CLOB without SELECT. Thanks

SKC
checkout http://orafaq.net/msgboard/java/messages/1290.htm that gives you a nice example!

Cheers, Stefan
Avatar of skcchan

ASKER

At last, I found out why my code does not work. Oracle said that if you want to use PreparedStatment.setCharacterStream for a LOB > 4k, you must use JDBC OCI driver. In other words, JDBC thin layer (type IV) does not work for any LOB that larger than 4k bytes when using pstmt.setCharacterStream.

BTW, thanks a lot for your comments.

skc
skc,

then ypwitkow in the first post was right, that was what he exactly told you.

... and in my link to  http://orafaq.net/msgboard/java/messages/1290.htm you could find the workarround and an example using the oracle java classes.


please close the thread ..

Cheers,
Stefan