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

    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

        • 1. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
          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.

          • 2. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
            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.

            • 3. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
              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

              • 4. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                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.

                • 5. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                  cfang
                  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
                    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.

                    • 7. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                      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

                      • 8. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                        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

                        • 9. Re: JdbcItemReader->Writer fail after upgrage 1.1.0.Final to 1.3.0.Beta6
                          cfang

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

                           

                          Release Notes - JBoss Issue Tracker