JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6

[mistrz_7]

Hello,

After upgrade from jberet-support from 1.1.0.Final to 1.3.0.Beta6 (but it doesn't work with 1.2.0 too) my old JdbcItemReader->Processor->JdbcIterWriter stopped working. This process is a little tricky, it looks like:

Reader:

select name, age, city

from person

Processor:

if city='Warsaw' then name='Adam'

Writer

insert into person_city(name, age) values(?, ?)

So the number of readed and writed columns is different, but one of result column depends on value on other column.

This solution work well on 1.1.0.Final version, but after upgrade I got error:

JBERET000007: Failed to run job pobierzZeZrodel, step_or, org.jberet.job.model.Chunk@1610a6a1: com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.

If I modify insert sql statement (but I have to add not necessary column to result table) to save 3 columns it works well.

How to solve this problem? Is it bug?

Regars

sw

[cfang]

can you post the complete stacktrace, especially from which line of JdbcItemWriter class the exception was thrown?

How do you configure JdbcItemWriter? (what batch properties under writer in job xml)

1.1.0 was released on  Apr 22, 2015 , so some changes after that date in either org.jberet.support.io.JdbcItemWriter or its superclass org.jberet.support.io.JdbcItemReaderWriterBase made the difference.

[cfang]

if you specified parameterNames batch property for jdbcItemWriter, then it should has a length of 2, and so is parameterTypes batch property, since your insert query contains 2 place holders.

if parameterNames has 3 elements, then jdbcItemWriter will attempt to set the 3rd sql parameter, which does not exist.

After looking at jdbcItemWriter source code, I guess this is the most likely cause.  Otherwise, please give more info I listed in the last comment.

[mistrz_7]

Hello,

My jbatch file looks like:

<step id="step_or" next="koniec">

              <chunk>

                              <reader ref="jdbcItemReader">

                                             <properties>

                                             <property name="dataSourceLookup" value="java:/myDb" />

                                              <property name="sql" value="select p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11

                                                                                              from t_my_table_in a"/>

                                                 <property name="beanType" value="java.util.List" />                                                                                                                                                                                     

                                                 <property name="resultSetProperties" value="fetchSize=10000" />

                                              </properties>

                                          </reader>

                              <processor ref="OrItemProcessor">

                              </processor>

                              <writer ref="jdbcItemWriter">

                                             <properties>

                                                            <property name="dataSourceLookup" value="java:/myDb" />

                                              <property name="sql" value="insert into t_my_tab_out(p1, p2, p3, p4, p5, p6, p7, p8, p9) values(?, ?, ?, ?, ?, ?, ?, ?, ?)" />

                                              <property name="beanType" value="java.util.List" />      

                                             </properties>

                              </writer>

               </chunk>                     

</step>

After switch to 1.3.0 Beta6 I got error:

2017-11-08 17:39:23,556 ERROR [org.jberet] (Batch Thread - 1) ProcessingInfo{count=10, timerExpired=false, itemState=RUNNING, chunkState=RUNNING, checkpointPosition=-1, readPosition=9, failurePoint=null}

2017-11-08 17:39:23,568 ERROR [org.jberet] (Batch Thread - 1) item-count=10, time-limit=0, skip-limit=-1, skipCount=0, retry-limit=-1, retryCount=0

2017-11-08 17:39:23,569 ERROR [org.jberet] (Batch Thread - 1) JBERET000007: Failed to run job pobierzZeZrodel, step_or, org.jberet.job.model.Chunk@3ff30cf7: com.microsoft.sqlserver.jdbc.SQLServerException: The index 10 is out of range.

               at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

               at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:714)

               at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:910)

               at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:935)

               at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:1064)

               at org.jberet.support.io.JdbcItemWriter.setParameter(JdbcItemWriter.java:216)

               at org.jberet.support.io.JdbcItemWriter.mapParameters(JdbcItemWriter.java:198)

               at org.jberet.support.io.JdbcItemWriter.writeItems(JdbcItemWriter.java:111)

               at org.jberet.runtime.runner.ChunkRunner.doCheckpoint(ChunkRunner.java:528)

               at org.jberet.runtime.runner.ChunkRunner.readProcessWriteItems(ChunkRunner.java:317)

               at org.jberet.runtime.runner.ChunkRunner.run(ChunkRunner.java:201)

               at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrChunk(StepExecutionRunner.java:226)

               at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:147)

               at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164)

               at org.jberet.runtime.runner.CompositeExecutionRunner.runJobElement(CompositeExecutionRunner.java:128)

               at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:203)

               at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164)

               at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHeadOrRestartPoint(CompositeExecutionRunner.java:88)

               at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutionRunner.java:60)

               at org.wildfly.extension.batch.jberet.impl.BatchEnvironmentService$WildFlyBatchEnvironment$1.run(BatchEnvironmentService.java:243)

               at org.wildfly.extension.requestcontroller.RequestController$QueuedTask$1.run(RequestController.java:497)

               at org.jberet.spi.JobExecutor$3.run(JobExecutor.java:161)

               at org.jberet.spi.JobExecutor$1.run(JobExecutor.java:99)

               at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

               at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

               at java.lang.Thread.run(Thread.java:745)

               at org.jboss.threads.JBossThread.run(JBossThread.java:320)

Regards

sw

[cfang]

The commit that made the difference in jdbcItemWriter when handling java.util.List type data item:

JBERET-169 JdbcItemWriter#open() produces ArrayIndexOutOfBoundsExcept… · jberet/jsr352@7d4f04b · GitHub

there a code block was added to skip determing insert sql parameters when java.util.List is used.

[cfang]

1. JBERET-363

JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6

[cfang]

as a workaround, you can use beanType = "java.util.Map" in both jdbcItemReader and jdbcItemWriter.  If the column field names are all the same between input table and output table, you don't need parameterNames property for jdbcItemWriter. 

Also consider adding a parameterTypes batch property to jdbcItemWriter in job.xml, so that more specific method (preparedStatement. setString(), setInt, etc as opposed to preparedStatement.setObject(val)) can be used to set sql parameter values.

JdbcItemWriter (jberet-support 1.3.0.Beta4 API)

I think java.util.Map beanType will also make it a bit easier to handle in your processor.

Anotehr workaround is, in your processor class, remove the unneeded element from the each list so that the length of data item list matches the number of sql paramters.

[cfang]

added a check for the number of sql parameters before looping thru the item fields:

JBERET-363 JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1… · jberet/jsr352@cf8599b · GitHub

[mistrz_7]

Hello,

Thank you for help and explanation. In my case I have decided to split app to two different apps. First one is using old 1.1.0 version and the newest one is using new 1.3.0B6 version.It's a strange soulution but  first application has 8 processors with complicated logic and I haven't time to migrate it to java.util.Map. A newest aplication is using jdbcBatchlet so i has to be 1.3.0B6 version.

As I understand my problem will be fixed in 1.3.0B7 version so I'm waiting to publish it on maven repository.

Regards

sw

[cfang]

JBeret 1.3.0.Beta7 has just been released (on 2017-12-10):

Release Notes - JBoss Issue Tracker