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 into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:DATETIME#)


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


    insert into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:DATE#)
</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 into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#,
                
			null
		
		
			#createDate:DATETIME#
		   )
</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 into APPROVAL_TASK (APPR_ID, STEP, TASK, EMAIL_TO, CREATE_DATE)
    values (#apprId:DECIMAL#, #step:DECIMAL#, #task:DECIMAL#, #emailTo:VARCHAR#, #createDate:TIMESTAMP#)
</pre>
<pre>
Advertisements
Explore posts in the same categories: Java, springframework

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: