ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Stahl <jest...@earthlink.net>
Subject Re: Passing NULL as a parameter to a Stored Procedure
Date Wed, 19 Nov 2008 13:04:24 GMT
Frankly, we didn't do anything special.  Just defined the jdbcType and 
javaType for each parameter.  One thing that might have helped us was 
that we use stored functions and procedures exclusively here.  Haven't 
tested using direct SQL in the SQLMap, so this still may be a dead end 
for you.  I'd decided to use normal parameterMaps because I felt they 
gave me more flexibility than the inline variety as the obvious cost of 
greater complexity.

Sorry I'm not of better help here.

Jeff Stahl

raulvk wrote:
> 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!
>>>
>>>
>>>   
>>>       
>>     
>
>   

Mime
View raw message