0

We are using the standard Spring Batch JdbcBatchItemWriter to insert data into MS SQL Server. The data to be inserted (the output from the item processor) is contained in a Map<String,String>, which holds the column names and values.

When the map contains null values (represented as an empty string "") for columns which are defined as Decimal (to be more specific, (decimal(19,5),null), the following exception is thrown:

java.sql.BatchUpdateException: Error converting data type nvarchar to numeric

Numeric fields which are not decimal work fine.

The driver is: net.sourceforge.jtds.jdbc.Driver

The following dll is used to connect to SQL Server: ntlmauth.dll

The Spring Batch item writer configuration is similar to the following:

<bean id="itemWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
   <property name="dataSource" ref="..." />
   <property name="sql" value="${...}" />
   <property name="itemPreparedStatementSetter">
      <bean class="org.springframework.batch.item.database.support.ColumnMapItemPreparedStatementSetter" />
   </property>
   <property name="itemSqlParameterSourceProvider">
            <bean class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
   </property>
</bean>

Here is the full stack trace:

]; Error converting data type nvarchar to numeric.; nested exception is java.sql.BatchUpdateException: Error converting data type nvarchar to numeric.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:303)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:298)
    at org.springframework.batch.item.database.JdbcBatchItemWriter.write(JdbcBatchItemWriter.java:175)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.writeItems(SimpleChunkProcessor.java:175)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.doWrite(SimpleChunkProcessor.java:151)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.write(SimpleChunkProcessor.java:274)
    at org.springframework.batch.core.step.item.SimpleChunkProcessor.process(SimpleChunkProcessor.java:199)
    at org.springframework.batch.core.step.item.ChunkOrientedTasklet.execute(ChunkOrientedTasklet.java:75)
    at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:395)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
    at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:267)
    at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:77)
    at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:368)
    at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
    at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
    at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:253)
    at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:195)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:141)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:60)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:151)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:130)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:135)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:134)
    at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:48)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:127)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:64)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:53)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.BatchUpdateException: Error converting data type nvarchar to numeric.
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:944)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)

Any assistance is greatly appreciated.

user1052610
  • 4,440
  • 13
  • 50
  • 101

1 Answers1

0

I have not used the Spring framework, but I can say that an empty string does not equate to a NULL when converting from a string to a number type. Just try these two queries:

SELECT CONVERT(INT, '');
SELECT CONVERT(DECIMAL(19, 5), '');

The first (i.e. CONVERT(INT)) returns 0, not NULL. The second fails with the same error that you are seeing. You need to change the mapping somehow to pass in a value that can equate to NULL. If you can submit an expression instead of a literal, something like the following might work:

CASE WHEN '{my value}' = '' THEN NULL ELSE '{my value}' END
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171