XMLType insertion problems

[berkgypsy]

Hello,

I'm having a difficult time inserting into an XMLType column. I'm using JBoss 4.0alpha and Oracle9i with a thin driver...

The problem is that the thin driver seems to be imposing CHAR and VARCHAR2 limits on my XMLType column. This code:

String stmt = "INSERT INTO " + product + "_DETAIL_XML " +
" ( participant_id, product_training_day, product_training_date, " +
exerciseShortName + "_XML, " + exerciseShortName + "_HASH ) " +
" VALUES ( ? , ? , sys.XMLType.createXML( ? ) , ? , ? ) ";

pstmt = conn.prepareStatement(stmt);


pstmt.setString(1, participant_id);
pstmt.setInt(2, product_training_day);
pstmt.setDate(3, new java.sql.Date(product_training_date.getTime()));
log.info("size of xml: "+ xml.length());
pstmt.setObject(4, xml);
pstmt.setInt(5, hash);


produces this error at runtime:

11:37:05,902 INFO [ProductDetail] size of xml: 5114
11:37:05,922 ERROR [STDERR] java.sql.SQLException: Data size bigger than max size for this type: 5114
11:37:05,922 ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2461)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1155)

11:37:05,922 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:157
2)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:276
2)
11:37:05,922 ERROR [STDERR] at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:301
5)
11:37:05,922 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedSta
tement.java:625)

I noticed here that setString is being called, so I tried creating the XMLType in my java code instead of doing it in the preparedstatement, but got the same error. Does anyone have an idea why the VARCHAR2 restrictions are being imposed on my XMLType column?

Thanks,
Emily

[berkgypsy]

sorry, the stmt looks like this

String stmt = "INSERT INTO " + product + "_DETAIL_XML " +
" ( participant_id, product_training_day, product_training_date, " +
exerciseShortName + "_XML, " + exerciseShortName + "_HASH ) " +
" VALUES ( ? , ? , ? , sys.XMLType.createXML(?) , ? ) ";


A case of bad copying and pasting. Any idea about why I am getting this error?
Thanks,
Emily

[joelvogt]

Can you post the section of your jdbc type mapping with xml type?

[berkgypsy]

Switching to an OCI driver and creating the XMLType object in my java code fixed my problem

[berkgypsy]

and for anyone that's wondering, because it wasn't that obvious, here's how to get the xmltype using the connection you get from

DataSource ds = (DataSource)initialContext.lookup("java:/OracleDS");
ds.getConnection("user", "pass");

...

XMLType xmltype = XMLType.createXML((Connection)((org.jboss.resource.adapter.jdbc.WrappedConnection)conn).getUnderlyingConnection(), xml);