9 Replies Latest reply on Dec 11, 2017 9:30 AM by Cheng Fang

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

    Sławomir Wojciechowski Newbie

      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

        • 1. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
          Cheng Fang Master

          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.

          • 2. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
            Cheng Fang Master

            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.

            • 3. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
              Sławomir Wojciechowski Newbie

              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

              • 4. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                Cheng Fang Master

                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.

                • 5. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                  Cheng Fang Master
                  1. JBERET-363

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

                  • 6. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                    Cheng Fang Master

                    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.

                    • 8. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                      Sławomir Wojciechowski Newbie

                      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