[
https://issues.apache.org/jira/browse/IBATIS-397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12474626
]
Jan Vissers commented on IBATIS-397:
------------------------------------
Although the typehandler for plain SELECT statements could be simplified using
'OBJECT' instead of the more intensive coding around 'Oracle' specific - the
problem with CallableStatements remain. So indeed a 'fix' would be helpful. I'm
testing with the following settings:
-- Oracle function
CREATE OR REPLACE FUNCTION GET_XML(P_I IN VARCHAR2)
RETURN XMLTYPE IS
BEGIN
RETURN XMLTYPE('<'||P_I||'>Hello World!</'||P_I||'>');
END;
/
<!-- Mapping segment -->
<typeAlias alias="XMLTypeHdl"
type="com.cumquatit.examples.ibatis.xmltype.XMLTypeHandlerCallback" />
<procedure id="xmltype" parameterClass="map">{
#doc,jdbcType=OBJECT,handler=XMLTypeHdl,mode=OUT# = call
get_xml(#name,jdbcType=VARCHAR,mode=IN#) } </procedure>
Runnin the tester results in:
Exception in thread "main" com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/cumquatit/examples/ibatis/xmltype/XmltypeData.xml.
--- The error occurred while executing query procedure.
--- Check the { ? = call get_xml(?) }.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: Invalid column type
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:566)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:541)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:83)
at
com.cumquatit.examples.ibatis.xmltype.XMLTypeDataTester.main(XMLTypeDataTester.java:37)
Caused by: java.sql.SQLException: Invalid column type
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at
oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3432)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:125)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:268)
at
oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:348)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.registerOutputParameters(SqlExecutor.java:428)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:275)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 6 more
> Support for Oracle "OPAQUE"
> ---------------------------
>
> Key: IBATIS-397
> URL: https://issues.apache.org/jira/browse/IBATIS-397
> Project: iBatis for Java
> Issue Type: Improvement
> Components: SQL Maps
> Affects Versions: 2.2.0, 2.3.0
> Reporter: Jan Vissers
>
> Based on the custom type handler described in:
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/XMLTypeHandlerCallback.java
> we tried to use this handler not only for "regular select" statements but
> also for "callable statements". As it turns out the current iBatis codebase
> doesn't allow us to use and/or extend the type handler to fully support
> Oracle's RDBMS XMLType datatype.
> I've experimented a bit and suggest the following solution:
> 1) Modify the JdbcTypeRegistry class in order for it to contain the
> ORACLEOPAQUE (much like the ORACLECURSOR already specified)
> setType("ORACLEOPAQUE", 2007);
> 2) Modify the CallableStatementResultSet class to have it expose its
> decorated "CallableStatement" instance:
> public CallableStatement getCs() {
> return cs;
> }
> With these two modifications we can declare the mapping as follows:
> <parameter property="doc" jdbcType="ORACLEOPAQUE" mode="OUT"
> typeHandler="XMLTypeHandlerCallback" typeName="SYS.XMLTYPE"/>
> We have to change the XMLTypeHandlerCallback (referenced at the top) in order
> for the getResult() to determine whether to perform work for a
> CallableStatementResultSet or an OracleResultSet, like so:
> //The handler getResult
> public Object getResult(ResultGetter getter) throws SQLException {
> if (getter.getResultSet() instanceof CallableStatementResultSet) {
> if (((CallableStatementResultSet) getter.getResultSet()).getCs()
> instanceof OracleCallableStatement) {
> OracleCallableStatement ocsmt = (OracleCallableStatement)
> ((CallableStatementResultSet) getter.getResultSet()).getCs();
> // go to work
> } else {
> throw new UnsupportedOperationException("XMLType mapping only
> supported for Oracle RDBMS");
> }
> } else
> if (getter.getResultSet() instanceof OracleResultSet) {
> // go to work
> } else {
> throw new UnsupportedOperationException("XMLType mapping only
> supported for Oracle RDBMS");
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
