Java Database Connectivity (JDBC)

When inserting to 9i database, setString throws a SQLException (Data size bigger than max size for this type: 4037) even though the string is less than 4000 characters. My guess at this point is that some character conversion is causing the string to exceed 4000 characters.

I think I need insight from someone who has access to the jdbc driver code who can tell me how I might resolve this. I've searched around and hit upon a couple of potential causes, but I think they are dead ends. I may still be on the wrong trail, but this is where I am now.

TIA! -- Maggie


PROBLEM: java.sql.SQLException: Data size bigger than max size for this type: 4037
Oracle 9i (9.2.0.1.0) with ojdbc14.jar (9.2.0.1). character set is WE8MSWIN1252.
[not a problem with Oracle 8i (8.1.7.2.1) with ojdbc14.jar (9.2.0.1). character set is WE8ISO8859P1]

Program breaks on insertStmt.setString(4, colval)

The length of the colval string is 3933. The maximum size of the column being inserted to is 4000.

------
I extracted the problem string from the XML file to a text editor (it was 4025 characters long) and thought I was facing a truncation problem with the 8i insert. However, once I tested on the 8i database, I extracted the inserted string from the database. It was complete and only 3933 characters long. The additional characters were from escaping the HTML.

Even escaping all the HTML doesn't bring the length up to 4037. So I ruled that out because that just seemed crazy--my parser is taking care of the HTML. Therefore there are 104 characters not accounted for (or characters that are taking up 2 bytes instead of 1).

There are several accented and odd characters:

8 occurrences of à
67 occurrences of é
11 occurrences of ê
11 occurrences of è
2 occurrences of î
2 occurrences of ô
2 occurrences of ù
1 occurrence of ±
----
104 characters...

There are apostrophes and other puntuation marks, but I don't think they pose a problem either.

The dba tells me the database character set is WE8MSWIN1252, and the NCHAR characterset is AL16UTF16.


Code Snippet:
...
PreparedStatement insertStmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
...
// check for clob
int colLength = colVal.length();
if (colLength > 4000) {
clobVal = colVal; // should be assigned first
colVal = colVal.substring(0,3999);
clobInd = "Y";
}
else {
clobVal = null;
clobInd = "N";
}

...

try {
detailColID = getNextColSeq();

insertStmt.setLong(1, detailColID);
insertStmt.setLong(2, detailID);
insertStmt.setString(3, colName);
insertStmt.setString(4, colVal);
insertStmt.setString(5, clobInd);
insertStmt.setString(6, displayVal);
insertStmt.setTimestamp(7, null); //orig_col_mod_date
insertStmt.setString(8, "N"); // confirm_required
insertStmt.setString(9, null); // import_note
insertStmt.setString(10, "N"); // accept_ind
insertStmt.setString(11, fkInd);
insertStmt.setLong(12, -1); // link_id
_importEngine.addBatch(insertStmt);
if (clobInd.equals("Y")) {
flushBatch();
insertColClob(detailColID, clobVal);
}
...

STACK TRACE:
java.sql.SQLException: Data size bigger than max size for this type: 4037
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)
at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.jav
a:2461)
at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStat
ement.java:1155)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedSt
atement.java:1572)
at org.abi.hdms.load.ImportDetailLoader.insertDetailCol(Unknown Source)
at org.abi.hdms.load.ImportDetailLoader.insertRecord(Unknown Source)
at org.abi.hdms.load.ImportEngine.insertRecord(Unknown Source)
at org.abi.hdms.load.ImportLoadContentHandler.endElement(Unknown Source)

at org.apache.xerces.parsers.SAXParser.endElement(SAXParser.java:1398)
at org.apache.xerces.validators.common.XMLValidator.callEndElement(XMLVa
lidator.java:1002)
at org.apache.xerces.framework.XMLDocumentScanner$ContentDispatcher.disp
atch(XMLDocumentScanner.java:1200)
at org.apache.xerces.framework.XMLDocumentScanner.parseSome(XMLDocumentS
canner.java:380)
at org.apache.xerces.framework.XMLParser.parse(XMLParser.java:900)
at org.abi.hdms.load.ImportEngine.load(Unknown Source)
at org.abi.hdms.load.ImportEngine.run(Unknown Source)
at java.lang.Thread.run(Thread.java:536)


ADDITIONAL INFO:
Same code works using same driver (9.2.0.1) and ORacle 8.1.7.4.1. (character set is WE8ISO8859P1).