anyone using oracle 11.x and xmltype?

[Rick R]

DBAs are attempting to convert some xml clob fields to Oracle's xmltype.

We're using 11.2.0.3 and I'm using ojdbc6 from the oracle download page along with xdb6 http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

On my inserts I saw in some docs to wrap the call with XMLType so I'm doing this in my mapper:

values (

XMLType(#{xmlDef,jdbcType=CLOB}),

XMLType(#{guiXML,jdbcType=CLOB}),

However upon insert I'll get an sql exception:

Caused by: java.sql.SQLException: Invalid column type: getCLOB not implemented for class oracle.jdbc.driver.T4CNamedTypeAccessor

Anyone else encounter this?

I'll probably start messing with some other drivers, but suggestions welcome.

--
Rick R

[AntPort]

Can you try this:

TypeHandler:

import java.sql.CallableStatement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.xdb.XMLType;

import org.apache.ibatis.type.JdbcType;

import org.apache.ibatis.type.TypeHandler;

public class XmlTypeHandler implements TypeHandler<String> {

/*

* (non-Javadoc)

* 

* @see org.apache.ibatis.type.TypeHandler#getResult(java.sql.ResultSet,

* java.lang.String)

*/

@Override

public String getResult(ResultSet paramResultSet, String paramString)

throws SQLException {

// TODO Auto-generated method stub

return null;

}

/*

* (non-Javadoc)

* 

* @see org.apache.ibatis.type.TypeHandler#getResult(java.sql.ResultSet,

* int)

*/

@Override

public String getResult(ResultSet paramResultSet, int paramInt)

throws SQLException {

// TODO Auto-generated method stub

return null;

}

/*

* (non-Javadoc)

* 

* @see

* org.apache.ibatis.type.TypeHandler#getResult(java.sql.CallableStatement,

* int)

*/

@Override

public String getResult(CallableStatement paramCallableStatement,

int paramInt) throws SQLException {

// TODO Auto-generated method stub

return null;

}

/*

* (non-Javadoc)

* 

* @see

* org.apache.ibatis.type.TypeHandler#setParameter(java.sql.PreparedStatement

* , int, java.lang.Object, org.apache.ibatis.type.JdbcType)

*/

@Override

public void setParameter(PreparedStatement paramPreparedStatement,

int paramInt, String paramT, JdbcType paramJdbcType)

throws SQLException {

XMLType data = XMLType.createXML(

paramPreparedStatement.getConnection(), paramT);

paramPreparedStatement.setObject(paramInt, data);

}

}

Mapper file:

<insert id="insertXMLType" parameterType="path.to.pojo.XMLPojo"

flushCache="true">

INSERT INTO

XML_TABLE

<trim prefix="(" suffix=")" suffixOverrides=",">

<if test="xmlColumn != null">

XML_COLUMN,

      </if>

</trim>

<trim prefix="VALUES (" suffix=")" suffixOverrides=",">

<if test="xmlColumn != null">

#{xmlColumn,jdbcType=VARCHAR,typeHandler=path.to.handler.XmlTypeHandler},

      </if>

</trim>

</insert>

I'm not sure this is the best solution, but it works...

Be careful when you create XMLType, one of the constructor parameters is Connection. In some environments (like WebSphere), you will not be able to get connection via paramPreparedStatement.getConnection(),because the Connection object is wrapped inside some other object (for security reasons)

Also, you'll need to add xmlparserv2.jar and xdb6.jar to your application classpath. 

[Rick R]

Thank you so much for providing this! 

(I'll report back in a few days since can't get to this until later on this week.)

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Rick R

[Rick R]

Thanks Dana! This below worked perfectly.

Note, xdb6.jar seemed to be all I needed (alongside the oracle driver of course.) I didn't need a dependency to xmlparserv2.jar  

Thanks again!

On Tue, Jan 21, 2014 at 7:11 AM, AntPort <antonio...@gmail.com> wrote:

--

You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Rick R

[Rick R]

Actually you do need the xmlparserv2.jar  , however this opened all the can of worms in section 4 in stackoverflow http://stackoverflow.com/questions/11578697/using-oracle-xmltype-column-in-hibernate/18282094#18282094  (Royal pain in the ...)

Fortunately I'm able to bypass the mess now since on the fetches I'm just using to_clob(xmlCol) and on the inserts updates the dba created a function to call.

--
Rick R

[Rick R]

Turns out I had to go back to using a mybatis type handler since the function I was provided had issues with large xml being passed to it. Got things working, but wow what a b*eotch. I'm posting all things I ended up doing. ..

1) The relevant portion of my type handler had to look like the following. I couldn't just use the simple 

XMLType data = XMLType.createXML(paramPreparedStatement.getConnection(), paramT); since I was getting errors as described here

http://stackoverflow.com/questions/6489514/apache-commons-dbcp-connection-object-problem-thread-classcastexception-in-org

So I ended up with:

@Override

public void setParameter(PreparedStatement paramPreparedStatement,

int paramInt, String paramT, JdbcType paramJdbcType)

throws SQLException { 

oracle.jdbc.OracleConnection delConn = (oracle.jdbc.OracleConnection)((org.apache.tomcat.dbcp.dbcp.DelegatingConnection)paramPreparedStatement.getConnection()).getInnermostDelegate();

XMLType data = ( paramT != null ) ? data = XMLType.createXML(delConn, paramT):null;

paramPreparedStatement.setObject(paramInt, data);

 

}

2) Related to the above I needed  to also set the tomcat datasource property "accessToUnderlyingConnectionAllowed" to true.

3) I replaced apache commons pool I was using with tomcat's 

<dependency>

<groupId>org.apache.tomcat</groupId>

<artifactId>tomcat-dbcp</artifactId>

<version>7.0.52</version>

</dependency>

4) Couldn't find "xmlparserv2" online in a repo anywhere, so had our Oracle guy send it to me from the distribution and I added this to our local maven repo. It was needed.

5) "xdb6" was also needed as a dependency. I can't recall where I found it, but I had to add that to our repository also.

 

6) Ran into the issues here under step 4. http://stackoverflow.com/questions/11578697/using-oracle-xmltype-column-in-hibernate/18282094#18282094

I ended up going with the solution 2.. created a META-INF/services dir in my mybatis services jar and added the three files mentioned and on each line placed the default xml processor/builder as described.

 

Hope that helps someone else, so their day isn't as wasted as mine was.

--
Rick R