JdbcPagingItemReader, sort key is TIMESTAMP

[KGSodie]

I'm trying to set up a batch job using JdbcPagingItemReader connected to DB2. The sort key column I configure is of type TIMESTAMP. The initial pagesize worth of rows is fed into my ItemProcessor normally, but when the second page fetch is attempted, I get this error:

SEVERE: Encountered an error executing the step: class org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT client_number, sub_type, ts, xml_record FROM bgadmin.event WHERE DATE(TS) = current date - 56 day AND TYPE IN ('Page','BusinessService') AND SYSTEM = 'EBPP' AND TS > ? ORDER BY TS ASC FETCH FIRST 10 ROWS ONLY]; SQL state [null]; error code [-99999]; [ibm][db2][jcc][1083][10395] Illegal conversion.; nested exception is com.ibm.db2.jcc.b.SqlException: [ibm][db2][jcc][1083][10395] Illegal conversion.

I suspect it has to do with the TIMESTAMP column type. Specifically, I think the parameter being substituted by springbatch in the "TS > ?" part of the where clause is the problem, but I'm only guessing.

Ideas?

[KGSodie]

I have found a 'workaround' for this, though I haven't tracked it down yet in the documentation or in the source code.

The part of the WHERE clause being added by springbatch, 'AND sortkey > ?' was being supplied with the value from the last row of the previous page result set's first column. I would think it would use the value from the defined sort key column, but this doesn't appear to be the case. The workaround was simply to reorder by SELECT columns so that the column I configure as the sort key is the first column, but this is subject to breaking if I change the config property for the sort key without making a matching change to the selectClause property.

Bug, or did I miss something in the documentation?

[Dave Syer]

There was a bug in some of the 2.0.* releases. 2.1.0 should have fixed it (maybe 2.0.4, I don't have the reference handy).

[KGSodie]

Thanks, Dave. Upgrading is on the radar anyway.