How to insert Oracle XMLTYPE using JdbcTemplate?

[benethridge]

Hi, everyone.

What's the trick to doing this?

I'm not finding a java.sql.Types for XML{anything}, nor an equivalent OracleTypes constant.

My code was inserting fine when it was a Types.CLOB and the database column was a CLOB, and I used the Spring LobHandler, but we changed the database column to be XMLTYPE.

The symptom is that JdbcTemplate inserts without complaint, but examining the database contents shows that the column contents is null.

Does anyone know how to work with these Oracle XMLTYPE columns using Spring?

Ben

[benethridge]

I figured this out googling around and tinkering with it. What's been throwing me off is that the jt.update succeeded and rtnVal was 1, but looking at the database contents showed <NULL>, which fooled me into thinking that the contents was actually null. (Ha-ha, got you again, Charlie Brown ) In case anyone else runs across this oddity, and yours really IS <NULL>, I'll post what I have working now (in effect):

    
    JdbcTemplate jt = this.getJdbcTemplate();
    String myXml = <your xml>;
    String insertStmt = "insert into mytable (  MY_ID,MY_XML,) values(?,XMLType(?))";
    int rtnVal = jt.update(insertStmt, new Object[] { new Long(0L), myXml }, new int[] { Types.NUMERIC, Types.VARCHAR });

I'm thinking that if the XML is big, it's going to need a CLOB or an OPAQUE or a LONGVARCHAR??... but I haven't tested that yet.

Has anyone else? Is there a better way to do this?

Ben

[wpoitras]

Can you get this working using native JDBC calls?

According to Oracle examples passing a CLOB to a XMLType(?) statement should work fine. But perhaps there is an issue with your driver and creating the XMLType locally would work better?

I find that solving problems with platform specific JDBC APIs is easier to solve writing native code and then figuring out how to map it Spring (or punt and use a PreparedStatementCallback or ConnectionCallback)

I haven't done specifically what you are trying to do. I've only used ARRAYs with Oracle, and I know the hoops you have to jump through to do special code.

[benethridge]

No, haven't tried that but again, this is working ok for me now using JdbcTemplate. Sorry, I may have thrown you off with all the question marks in prev post.

I'm just more curious now, as to how people are handling it if it's too big to fit in a VARCHAR. I'll tinker with it myself, and post the results, if no one else does.

Thanks for the tip, though.

Ben

[DavidHorton]

I had a similar issue a while back when I was storing XML responses from an external provider. It was fine using VARCHAR as long as the data was less than 4000 bytes. Anything over that was throwing an SQL exception.

It's very straightforward to change to use a CLOB to hold the XML type. The insert statement stays the same but you just bind in a CLOB instead of a VARCHAR2. The OracleLobHandler will handle the actual binding of the CLOB into the statement and storing in the db.

        String sql = "INSERT INTO cc_response
(request_id, provider_response, created_at)
values (?, XmlType(?), SYSDATE)";

        Object[] params = {
            new Integer(request.getRequestID()),
            new SqlLobValue(xmlResp, new OracleLobHandler())
        };

        int[] types = new int[] {
            Types.INTEGER,
            Types.CLOB
        };

        // Store the response
        getJdbcTemplate().update(sql, params, types);

[Cae29]

Is it possible to do this using a datasource that is retrieved through jndi lookup?

Because I have problems with the initialising the OracleLobHandler

See it in this thread

http://forum.springframework.org/sho...d.php?p=164892

[DavidHorton]

Yes, it is definitely possible to do this with JNDI datasources. The fact that the datasource comes from JNDI is pretty much irrelevant, once you've got the reference to it, it's no different to any other datasource.

Can you give some details on your configuration? From the linked thread it looks like you're using Weblogic, which version? Spring version?

The snippets below are from Spring 2.0.6 on Weblogic 8.1sp6. It's a slight variation on the version of my previous post above.

The below is from the applicationContext.xml file to configure the datasource. jdbc/myDataSource is configured as a datasource in the Weblogic console (driver class is 10g oracle.jdbc.OracleDriver from ojdbc14.jar)

  <bean id="requestDAO" class="com.mycompany.dao.OracleRequestDAO">
    <property name="dataSource" ref="dataSource" />
  </bean>

  <jee:jndi-lookup id="dataSource" jndi-name="jdbc/myDataSource" />

Here's the code from requestDAO to use a clob...

public class OracleRequestDAO extends JdbcDaoSupport implements RequestDAO {
    public final void saveRequest(final CCRequest request, final String xmlReq) {
        // Create the SQL statement to store the data.
        String sql = "UPDATE request SET z = XmlType(?), updated_at = SYSDATE WHERE x = ? AND y = ?";
        Object[] params = new Object[] {
            new SqlLobValue(xmlReq, new OracleLobHandler()),
            request.getX(),
            request.getY()
        };

        int[] types = new int[] {
            Types.CLOB,
            Types.VARCHAR,
            Types.VARCHAR
        };

        // Store the request
        getJdbcTemplate().update(sql, params, types);
    }
}

This is all I needed to get this working.

I didn't need to use a native jdbc extractor at all for this to work with WLS8 and Oracle 10.2.0.X.

Just guessing but could it be a classpath issue? Do you have the spring and/or Oracle jdbc driver libraries in the wrong place? Or in more than one place? In the application above, the ojdbc14.jar file is only in C:\bea\weblogic81\server\lib and the spring jar is packaged in the EAR file.

[Cae29]

I'm using Weblogic 9.2 and Spring 2.0.6.

I suspect it the classpath issue too. But i have checked ojdbc14.jar. It all of the new version.

Just 1 question, have u tried inserting a xml string that is more than 4000 char?

[DavidHorton]

Yes, see post above. I was initially using Types.VARCHAR for the XML type column but changed to CLOB/OracleLobHandler so I could cope with values longer than 4000 bytes.

[Cae29]

I tried to do what you do. Stripping down everything until a DAO is left. But i still gettting the same error message

Caused by: java.lang.NullPointerException
at org.spring.framework.jdbc.support.lob.OracleLobHandler.initOracleDriverClasses(OracleLobHandler.java:150)

I read through the sources code at here,

http://www.jdocs.com/spring/2.0.6/or...obHandler.html

and it pointed to the connection being a null.

Anyone got any idea on this??