[
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-,
[EMAIL PROTECTED]
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
