Java Database Connectivity (JDBC)

When I try to INSERT a new (N)CLOB into an Oracle database, all is fine until I use a non-ASCII character, such as an accented roman letter, like the "�" (that's '\u00E9') in "caf�" or the Euro Currency symbol "?" (that's '\u20AC' as a Java character literal, just in case the display is corrupted here too). This doesn't happen with "setString", but does happen when streaming characters to the CLOB; however, as Oracle or the driver refuse strings larger than 4000 characters, and as I need to support all the above symbols (and many more), I'm stuck.

Here's the background to the problem (I've tried to be detailed, after a lot of looking around on the web, I've seen lots of people with similar problems, but no solutions: I've seen and been able to stream ASCII clobs, or add small NCHAR strings, but not stream NCLOBs...).

I'm using Oracle 9.2.0.1.0 with the "thin" JDBC driver, on a Windows box (XP Pro). My database instance is set up with AL32UTF8 as the database encoding, and UTF8 as the national character set.. I've created a simple user/schema, called LOBTEST, in which I created two tables (see below).

The basic problems are :

- with Oracle and JDBC, you can't set the value of a CLOB or NCLOB with PreparedStatement's setString or setCharacterStream methods (as it throws an exception when you send more than 4000 characters)

- with Oracle, you can only have one LONG VARCHAR-type field per table (according to their documentation) and you MUST read all columns in a set order (amongst other limitations).

- with a SQL INSERT command, there's no way to set the value of a parameter that's a CLOB (implementations of the CLOB interface can only be obtained by performing a SELECT.... but obviously, when I'm inserting, the record doesn't exist yet...). Workarounds include (possibly) JDBC 4 (doesn't exist yet...) or doing the following Oracle-specific stuff :

INSERT INTO MyTable (theID,theCLOB) VALUES (1, empty_clob());
SELECT * FROM MyTable WHERE theId = 1;

...and getting the empty CLOB back (via a ResultSet), and populating it. I have a very large application, that's deployed for many of our customers using SapDB and MySQL without a hitch, with "one-step" INSERTS; I can't feasibly change the application into "three-step INSERT-SELECT-UPDATE" just for Oracle, and I shouldn't need to!!!

The final workaround is to use Oracle-specific classes, described in:
http://download-east.oracle.com/otn_hosted_doc/jdeveloper/904preview/jdbc-javadoc/index.html
...such as CLOB (see my example). This works fine until I add some non-ASCII characters, at which point, irrespective of whether the CLOB data is 2 characters or 2 million characters, it throws the same exception:

java.io.IOException: Il n'y a plus de donn?es ? lire dans le socket
	at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:716)
	at oracle.jdbc.driver.OracleClobWriter.flushBuffer(OracleClobWriter.java:270)
	at oracle.jdbc.driver.OracleClobWriter.flush(OracleClobWriter.java:204)
	at scratchpad.InsertOracleClobExample.main(InsertOracleClobExample.java:61)

...where the error message in English is "No more data to read from socket". I need the Oracle-specific "setFormOfUse" method to force it to correctly use the encoding of the NCLOB field, without it, even plain ASCII data is rejected with an exception indicating that the character set is inappropriate. With a plain CLOB, I don't need it, but the plain CLOB refuses my non-ASCII data anyway.

So, many many thanks in advance for any advice. The remainder of my post includes my code example and a simple SQL script to create the table(s). You can mess around with the source code to test various combinations.

Thanks,
Chris B.

CREATE TABLE NCLOBTEST (
	ID         INTEGER NOT NULL,
	SOMESTRING NCLOB,
	PRIMARY KEY (ID)
);
CREATE TABLE CLOBTEST (
	ID         INTEGER NOT NULL,
	SOMESTRING CLOB,
	PRIMARY KEY (ID)
);

package scratchpad;

import java.io.Writer;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.sql.CLOB;

public class InsertOracleClobExample
{
	public static void main(String[] args)
	{
		Properties jdbcProperties = new Properties();
		jdbcProperties.setProperty( "user", "LOBTEST" );
		jdbcProperties.setProperty( "password", "LOBTEST" );
//		jdbcProperties.setProperty("oracle.jdbc.defaultNChar","true");

		Driver jdbcDriver = new OracleDriver();
		PreparedStatement pstmt = null;
		Connection connection = null;
		String tableName = "NCLOBTEST";
		CLOB clob = null;

		try
		{
			connection = jdbcDriver.connect("jdbc:oracle:thin:@terre:1521:orcl", jdbcProperties);

			pstmt = connection.prepareStatement("DELETE FROM NCLOBTEST");
			pstmt.executeUpdate();
			pstmt.close();

			pstmt = connection.prepareStatement(
				"INSERT INTO "+tableName+" (ID,SOMESTRING) VALUES (?,?);"
			);

			clob = CLOB.createTemporary(pstmt.getConnection(), true, CLOB.DURATION_SESSION);
			clob.open(CLOB.MODE_READWRITE);

			Writer clobWriter = clob.getCharacterOutputStream();
			clobWriter.write("Caf? 4,90? TTC");
			clobWriter.flush();
			clobWriter.close();

			clob.close();

			OraclePreparedStatement opstmt = (OraclePreparedStatement)pstmt;
			opstmt.setInt(1,1);
			opstmt.setFormOfUse(2, OraclePreparedStatement.FORM_NCHAR);
			opstmt.setCLOB(2, clob);
			System.err.println("Rows affected: "+opstmt.executeUpdate());
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			try	{
				clob.freeTemporary();
			} catch (SQLException e) {
				System.err.println("Cannot free temporary CLOB: "+e.getMessage());
			}
		}
		try { pstmt.close(); } catch(SQLException sqlex) {}
		try { connection.close(); } catch(SQLException sqlex) {}
	}
}