Hi Jeff,
Many thanks for the suggestion. I will try using a Parameter Map instead of
inline parameters. Is there any specific thing that you do when specifying
the Parameter Maps so that the NULLs are transferred correctly?
In the meantime, I have only found one way to propagate the JDBC NULL values
correctly, which entails using <isNull> and <isNotNull>, but this is less
than ideal because:
1. iBATIS should do this transparently
2. For each parameter that is nillable, there has to be a <isNull> block
that inserts the JDBC NULL into the SQL statement and a <isNotNull> block
that inserts the value of the parameter into the SQL statement. iBATIS
should really support if-then statements...
Anyway, will get back to the mailing list tomorrow with my findings...
Raul.
Jeff Stahl wrote:
>
> We've actually found in the FEMA project I designed and built that using
> a parameter map for this (instead of inline parameters) helps a lot.
> Couldn't think of a way to manage nulls using the inline stuff.
>
> Jeff Stahl
>
> raulvk.soa wrote:
>> Hi guys,
>>
>> We are invoking a Stored Procedure on an Oracle Database which takes in a
>> lot of IN parameters. However, many of them can be NULL (and in our use
>> case
>> scenario MUST be null).
>>
>> We are using the following in our SqlMap.xml (reduced version of our
>> actual
>> code):
>>
>> <procedure
>> id="TheStoredProcedure"
>> parameterClass="org.mycompany.TheStoredProcedureInput"
>> resultMap="result">
>>
>> {call theStoredProcedure
>> (#a:VARCHAR#, #b:VARCHAR#, #c:VARCHAR#, #d:VARCHAR#)}
>>
>> </procedure>
>>
>>
>> In our object "TheStoredProcedureInput", the field "b" takes the value
>> null,
>> which makes iBATIS cause the following error:
>>
>>
>> --- The error occurred while applying a parameter map.
>> --- Check the TheStoredProcedure-InlineParameterMap.
>> --- Check the parameter mapping for the 'b' property.
>> --- Cause: java.sql.SQLException: Invalid column type
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:82)
>> at ... (....java:72)
>> Caused by: java.sql.SQLException: Invalid column type
>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
>> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
>> at
>> oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6541)
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1429)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:597)
>> at
>> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:70)
>> at $Proxy1.setNull(Unknown Source)
>> at
>> com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameter(ParameterMap.java:174)
>> at
>> com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameters(ParameterMap.java:126)
>> at
>> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:276)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:39)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
>> ... 6 more
>>
>>
>> What are we doing wrong? Can iBATISIs not interpret NULL Java values? Is
>> it
>> necessary to use the nullValue mechanism to have iBATIS convert a
>> specific
>> value to a JDBC NULL?
>>
>> Many thanks!
>>
>>
>>
>
>
--
View this message in context:
http://www.nabble.com/Passing-NULL-as-a-parameter-to-a-Stored-Procedure-tp20566537p20569247.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
