[jira] Commented: (IBATIS-145) oracle's user-defined objects are not supported as store procedure out parameters

["ING IKRS (JIRA)" <ib...@incubator.apache.org>]

    [ http://issues.apache.org/jira/browse/IBATIS-145?page=comments#action_12331807 ] 

ING IKRS commented on IBATIS-145:
---------------------------------

I was able to use user-defined types with stored procedures after making a minor  modification (I made JdbcTypeRegistry.setType public) to the iBatis source downloaded beginning of September'05.
Below I include full description of my test code.

hi,

I was able to use iBatis to call stored procedures with array arguments, e.g. PS_VARCHAR_ARRAY_TYPE, after making only a minimal hack to the iBatis code.

My test stored procedure was:

/* prefixes prefix to all items in p_str_a and returns them in o_str_a */
PROCEDURE prefix( p_prefix IN VARCHAR2,
p_str_a IN PS_VARCHAR2_ARRAY_TYPE,
o_str_a OUT PS_VARCHAR2_ARRAY_TYPE);


The sql mapping in xml file was

<parameterMap id="prefixStringsParams" class="map" >
<parameter property="prefix" jdbcType="VARCHAR2" javaType="java.lang.String" mode="IN"/>
<parameter property="stringArray" jdbcType="PS_VARCHAR2_ARRAY_TYPE" typeName="PS_VARCHAR2_ARRAY_TYPE" typeHandler="StringArrayHandler" mode="IN"/>
<parameter property="prefixedStringArray" jdbcType="PS_VARCHAR2_ARRAY_TYPE" typeName="PS_VARCHAR2_ARRAY_TYPE" typeHandler="StringArrayHandler" mode="OUT"/>
</parameterMap>

<procedure id="prefixStrings" parameterMap="prefixStringsParams" >
{call TEST_IBATIS.prefix (?, ?, ?)}
</procedure>

In order to handle PS_VARCHAR2_ARRAY_TYPE I had to implement Custom TypeHandler

public class StringArrayTypeHandlerCallback implements TypeHandlerCallback {

static{
JdbcTypeRegistry.setType("PS_VARCHAR2_ARRAY_TYPE", Types.ARRAY); <<== here is where the hack was needed -- the setType method was private in JdbcTypeRegistry and I had to make it public in order to tell it that PS_VARCHAR2_ARRAY_TYPE corresponds to Types.ARRAY, otherwise I would get an exception
};

public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
String[] strArr = (String[])parameter;
setter.getPreparedStatement().getConnection();
Connection conn = setter.getPreparedStatement().getConnection();
ArrayDescriptor stringArrayDescriptor = ArrayDescriptor.createDescriptor("PS_VARCHAR2_ARRAY_TYPE", conn);
ARRAY valArray;
try {
valArray = new ARRAY(stringArrayDescriptor, conn, strArr);
} catch (SQLException e) {
throw e;
}
setter.setArray(valArray);

}

public Object getResult(ResultGetter getter) throws SQLException {
Array arr = getter.getArray();
if(arr==null)
return null;
else
return (String[])arr.getArray();
}

public Object valueOf(String arg0) {
String[] r = new String[1];
r[0] = arg0;
return r;
}
}

The handler had to be registered in the main ibatis config file:

<typeAlias alias="StringArrayHandler" type="com.ingenuity.dbcommon.ibatis.StringArrayTypeHandlerCallback" />
<typeHandler javaType="[Ljava.lang.String;" callback="StringArrayHandler" />

The test code was this:

String resource = "com/ingenuity/dbcommon/ibatis/SqlMapConfig.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
Map dto = new HashMap();
dto.put("prefix", "pre-");
dto.put("stringArray", new String[]{"abc","cde"});
dto.put("prefixedStringArray", new String[]{});
printDto(dto);
sqlMap.update("prefixStrings", dto);
printDto(dto);

And the output was

prefix -> pre-
stringArray -> [abc, cde]
prefixedStringArray -> []
08:58:38.441 [main] DEBUG SimpleDataSource: Created connection 597230.
08:58:38.441 [main] DEBUG Connection: {conn-100000} Connection
08:58:38.631 [main] DEBUG PreparedStatement: {pstm-100001} PreparedStatement: {call TEST_IBATIS.prefix (?, ?, ?)}
08:58:38.641 [main] DEBUG PreparedStatement: {pstm-100001} Parameters: [pre-, oracle.sql.ARRAY@e0b6f5]
08:58:38.641 [main] DEBUG PreparedStatement: {pstm-100001} Types: [java.lang.String, oracle.sql.ARRAY]
08:58:38.661 [main] DEBUG SimpleDataSource: Returned connection 597230 to pool.
prefix -> pre-
stringArray -> [abc, cde]
prefixedStringArray -> [pre-abc, pre-cde]

As you can see the strings indeed got prefixed.

> oracle's user-defined objects are not supported as store procedure out parameters
> ---------------------------------------------------------------------------------
>
>          Key: IBATIS-145
>          URL: http://issues.apache.org/jira/browse/IBATIS-145
>      Project: iBatis for Java
>         Type: Improvement
>   Components: SQL Maps
>     Versions: 2.1.0
>  Environment: oracle and maybe any others db that support user defined data types  
>     Reporter: ppz4j
>     Assignee: Brandon Goodin

>
> we have to call legacy oracle store procedure with named array types as output parameters. When I try it I get an invalid parameter type exception
> This problem arises because to use these params the SQLExecutor class should call the ?callableStatement.registerOutputParameters?  method version with the typeName param. In fact the java doc of this method suggests "This version of the method registerOutParameter  should be used for a user-defined or REF output parameter" and "To be portable, however, applications should always provide these values for user-defined and REF parameters. Although it is intended for user-defined and REF parameters, this method may be used to register a parameter of any JDBC type. If the parameter does not have a user-defined or REF type, the typeName parameter is ignored "  
> To handle this problem I have made some changes in source code and dtd file, but I hope that in a next version this problem will be resolved.   
>   
> Following the changes that I have made to add this enhancement:
> Added property typeName to bean BasicParameterMapping, with set and get methods. 
> Added code that load typeName param from xml in SqlMapParser and code that save it as property of the current instance of mapping.  
> Changed the SQLExecutor, now before call the CallableStatement.registerOutputParameter the registerOutputParameters method checks if the typeName of the given map parameter is not null. If it so it calls the  overloaded method registerOutParameter(int paramIndex,int sqlType,String typeName) instead of registerOutParameter(int paramIndex,int sqlType.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira