ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From raulvk <raul.kripal...@atosorigin.com>
Subject Re: Passing NULL as a parameter to a Stored Procedure
Date Tue, 18 Nov 2008 22:53:16 GMT

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.


Mime
View raw message