pts0 (2) [Avatar] Offline
#1
Hi,

I implemented something like on page 149 of MEAP, but i get some trouble with the property : <property name="assertUpdates" value="true" />
Job always fails with exception : EmptyResultDataAccessException: Item 0 of 10 did not update any rows
Actually DB row is deleted and jobs work well ...

Here is the relevant config:

<bean id="deleteMailEntries" >
class="org.springframework.batch.item.database.JdbcBatchItemWriter">
<property name="assertUpdates" value="true" />
<property name="itemSqlParameterSourceProvider">
<bean >
class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
</property>
<property name="sql" >
value="DELETE FROM ${schema.common}.mail WHERE mailid=:id" />
<property name="dataSource" ref="myDataSource" />
</bean>


and here is what i got in the log :

16:44:56,601 DEBUG jobLauncherTaskExecutor-1 database.JdbcPagingItemReader:195 - SQL used for reading first page: [SELECT mailid, sddview, reference, agent, activity, inserttime, status, mailtime, mailaddress FROM myschema.MAIL WHERE DATE(INSERTTIME) <= (select current date -180 D
AYS from sysibm.sysdummy1) ORDER BY INSERTTIME ASC FETCH FIRST 10 ROWS ONLY]
[...]

16:44:56,791 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=2
16:44:56,792 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=3
16:44:56,793 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=4
16:44:56,794 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=5
16:44:56,795 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=6
16:44:56,796 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=7
16:44:56,797 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=8
16:44:56,798 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=9
16:44:56,799 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:365 - Repeat operation about to start at count=10
16:44:56,800 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:436 - Repeat is complete according to policy and result value.
16:44:56,802 DEBUG jobLauncherTaskExecutor-1 database.JdbcBatchItemWriter:166 - Executing batch with 10 items.
16:44:56,810 DEBUG jobLauncherTaskExecutor-1 core.JdbcTemplate:880 - Executing SQL batch update [DELETE FROM myschema.tmail WHERE mailid=?]
16:44:56,812 DEBUG jobLauncherTaskExecutor-1 core.JdbcTemplate:569 - Executing prepared SQL statement [DELETE FROM myschema.mail WHERE mailid
=?]
16:44:56,828 DEBUG jobLauncherTaskExecutor-1 support.JdbcUtils:362 - JDBC driver supports batch updates
16:44:56,851 DEBUG jobLauncherTaskExecutor-1 tasklet.TaskletStep:402 - Applying contribution: [StepContribution: read=10, written=0, filtere
d=0, readSkips=0, writeSkips=0, processSkips=0, exitStatus=EXECUTING]
16:44:56,853 DEBUG jobLauncherTaskExecutor-1 tasklet.TaskletStep:436 - Rollback for RuntimeException: org.springframework.dao.EmptyResultDat
aAccessException: Item 0 of 10 did not update any rows: [mycompany.bo.Mail@159df6c]
16:44:56,854 DEBUG jobLauncherTaskExecutor-1 datasource.DataSourceUtils:332 - Returning JDBC Connection to DataSource
16:44:56,855 DEBUG jobLauncherTaskExecutor-1 datasource.DataSourceTransactionManager:843 - Initiating transaction rollback
16:44:56,857 DEBUG jobLauncherTaskExecutor-1 datasource.DataSourceTransactionManager:279 - Rolling back JDBC transaction on Connection [jdbc
:hsqldb:mem:testdb;sql.enforce_strict_size=true, UserName=SA, HSQL Database Engine Driver]
16:44:56,859 DEBUG jobLauncherTaskExecutor-1 datasource.DataSourceTransactionManager:322 - Releasing JDBC Connection [jdbc:hsqldb:mem:testdb
;sql.enforce_strict_size=true, UserName=SA, HSQL Database Engine Driver] after transaction
16:44:56,861 DEBUG jobLauncherTaskExecutor-1 datasource.DataSourceUtils:332 - Returning JDBC Connection to DataSource
16:44:56,862 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:291 - Handling exception: org.springframework.dao.EmptyResultDataAccessE
xception, caused by: org.springframework.dao.EmptyResultDataAccessException: Item 0 of 10 did not update any rows: [mycompany.bo.Mail@159df6c]
16:44:56,864 DEBUG jobLauncherTaskExecutor-1 support.RepeatTemplate:251 - Handling fatal exception explicitly (rethrowing first of 1): org.s
pringframework.dao.EmptyResultDataAccessException: Item 0 of 10 did not update any rows: [mycompany.bo.Mail@159df6c]

16:44:56,866 ERROR jobLauncherTaskExecutor-1 step.AbstractStep:212 - Encountered an error executing the step
org.springframework.dao.EmptyResultDataAccessException: Item 0 of 10 did not update any rows: [mycompany.bo.Mail@159
df6c]
at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:195)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:171)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:150)
at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:26smilie
at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:194)
at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:74)
at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:371)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:262)

Any idea why i alway get the messase update fail ? Is a problem of DB driver (DB2 hit ?).


Thanks for help !
arnaud.cogoluegnes (73) [Avatar] Offline
#2
Re: JdbcBatchItemWriter
it looks like the SQL order you send doesn't update anything. Are you sure you're hitting the correct schema (I see a placeholder for the schema) and the ID of the items you're reading match the ID of the rows in the mail table?
pts0 (2) [Avatar] Offline
#3
Re: JdbcBatchItemWriter
Hi Arnaud,

I m sure it delete the rows, because when I set assertUpdates to false job get trought and work fine.
I think i have some problem with rowmapper, I have two similar jobs on the same DB, with two different rowmapper. Somehow it use always the same one smilie.

I will double check the config ... and come back.

thanks

kim
arnaud.cogoluegnes (73) [Avatar] Offline
#4
Re: JdbcBatchItemWriter
ok, sounds weird. You can implement your own item writer to diagnose the issue. It's straightforward: just use a JdbcTemplate and check the result of the update method.

BTW, this is a forum about the "Spring Batch in Action" book and you should post such questions on the Spring Batch forum (http://forum.springsource.org/forumdisplay.php?41-Batch). I guess you would get support from more people (including me!).