iBatis, Date null values and Oracle

I better blog this before I forget what was the issue :-).

During daily run of unit tests, I started to receive this (very well explained) exception:

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [17004];
--- The error occurred while applying a parameter map.
--- Check the APPROVAL_TASK.insert-InlineParameterMap.
--- Check the parameter mapping for the 'scheduledDate' property.
--- Cause: java.sql.SQLException: Invalid column type; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the APPROVAL_TASK.insert-InlineParameterMap.
--- Check the parameter mapping for the 'scheduledDate' property.
--- Cause: java.sql.SQLException: Invalid column type
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:121)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
	at org.springframework.orm.ibatis.SqlMapClientTemplate.insert(SqlMapClientTemplate.java:397)
... 

Now the problem was that scheduledDate was null. Here is partial SQL map used:

<insert id="insert" parameterClass="com.zarlink.cdca2.domain.ApprovalTask" >
    insert into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:DATETIME#)
</insert> 

The createDate time jdbcType DATETIME was actually one of my changes. What Abator generated originally was this:

<insert id="insert" parameterClass="com.zarlink.cdca2.domain.ApprovalTask" >
    insert into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:DATE#)
</insert></pre>
<pre>

This map does not suffer by the Null value problem, but unfortunately does not store the time portion of the date – which was the main reason why I used DATETIME, unaware of the Null sensitivity.

There are three ways how to fix this. First is obvious – do use DATETIME and make sure that the field has value. This may be good enough as long as you do not need to save null values.

Second solution is to keep DATETIME and use iBatis magic with conditionals in map definition:

</pre>
<pre><insert id="insert" parameterClass="com.zarlink.cdca2.domain.ApprovalTask" >
    insert into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#,  	
                <isNull property="createDate">
			null
		</isNull>
		<isNotNull property="createDate">
			#createDate:DATETIME#
		</isNotNull>   )
</insert></pre>
<pre>

This deals with the null value differently and avoids “guessing” the column type, which caused the problem.Third solution may not work on other databases, but on Oracle works prefectly. The data type TIMESTAMP does both store the time portion as well as handles Null values without any problems. This is what I used at the end.

Final map:

</pre>
<pre><insert id="insert" parameterClass="com.zarlink.cdca2.domain.ApprovalTask" >
    insert into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:TIMESTAMP#)
</insert></pre>
<pre>
Advertisements

3 Responses to iBatis, Date null values and Oracle

  1. Bryan says:

    Thank you! I’m disturbed by this problem too.

  2. kitty says:

    I met the exactly same issue and I used the 3rd option. Saved me some time to figure this out on my own. Thanks!

  3. Steven says:

    this is for sql “insert”,but how to do with “select”???
    i’m was trubled by select nullValue,can you help me ?
    how to set when you select?

%d bloggers like this: