Tuesday, February 8, 2011

Passing XMLType to JDBC

We're currently working on adding XMLType support to OrindaBuild. We've found that there are a couple of 'gotchas' anyone working in this area would want to watch for.



JDBC doesn't have a direct representation of XMLType. Instead we use the wonderfully obscure OPAQUE data type to represent it internally. Oracle doesn't provide a vast array of documentation. The most useful ones would be:





Passing XMLType in...


In order to pass an XMLType in you have to bind it as an Opaque. Now oracle.xdb.xmltype extends oracle.sql.OPAQUE so either setOPAQUE or setObject should work fine. In practice this is only true until you try passing a null XMLType. You then hit a problem. setOPAQUE doesn't work for a null value - you a message which says:


ORA-17004 java.sql.SQLException:Invalid column type: sqlType=2007


This is because OPAQUE has some very odd null semantics - setNull only works if you say what kind of null it is:


theOraclePreparedStatement.setNull(i

                         , OracleTypes.OPAQUE

                         , myOpaqueThing);


Getting XMLType out...


 
If dealing with a cursor you can use getObject:



XMLType myXml= (XMLType)myResultSet.getObject(1);




Otherwise you need to use getOPAQUE and specify what kind of OPAQUE you're expecting:


theCallableStatement.registerOutParameter

(i+1,parameterTypeArray[i],"SYS.XMLTYPE");


No comments: