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 Tue, 18 Nov 2008 19:50:20 GMT
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