4 Replies Latest reply on Dec 20, 2002 2:11 PM by Emily

    XMLType insertion problems

    Emily Newbie

      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