How to insert CLOB from a Java program via JDBC

skcchan
skcchan used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
PreparedStatement methods have 4k limitation on CLOB.
Better use oracle.sql.CLOB class.
Examples in $ORACLE_HOME/jdbc/demo/samples/generic/LobExample.java

Best regards,
LW

Author

Commented:
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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial