You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "raulvk.soa" <ra...@gmail.com> on 2008/11/18 20:29:56 UTC
Passing NULL as a parameter to a Stored Procedure
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-tp20566537p20566537.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Passing NULL as a parameter to a Stored Procedure
Posted by Jeff Stahl <je...@earthlink.net>.
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!
>>>
>>>
>>>
>>>
>>
>
>
Re: Passing NULL as a parameter to a Stored Procedure
Posted by raulvk <ra...@atosorigin.com>.
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.
Re: Passing NULL as a parameter to a Stored Procedure
Posted by Jeff Stahl <je...@earthlink.net>.
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!
>
>
>