Announcement Announcement Module
Collapse
No announcement yet.
batch...JdbcPagingItemReader not passing starting index on second page Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • batch...JdbcPagingItemReader not passing starting index on second page

    Hallo,

    I have a batch job with this reader :

    Code:
    <bean id="collectionEntryReader" class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step">
            <property name="dataSource" ref="myDataSource" />
            <property name="queryProvider">
                <bean class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
                    <property name="dataSource" ref="myDataSource" />
                    <property name="databaseType" value="DB2" />
                    <property name="selectClause"
                        value="SELECT colid, status, foo, bar, guu" />
                    <property name="fromClause" value="FROM ${my_schema}.MYTABLE" />
                    <property name="whereClause"
                        value="WHERE status IN ('17','38') AND XMLCAST( XMLQUERY(... ) &lt;=(select current date from sysibm.sysdummy1)" />
                    <property name="sortKey" value="colid" />
                </bean>
            </property>
            <property name="pageSize" value="20" />
            <property name="rowMapper" ref="myMapper" />
        </bean>
    The first chunk of 20 item all works, but the second page fail with the error :

    Code:
     ... ate from sysibm.sysdummy1) AND colid > ? ORDER BY colid ASC FETCH FIRST 20 ROWS ONLY]; nested exception is java.sql.SQLException: [EXECUTE] Wrong nbr of host variables
    It look like placeholder in the query is not substituted.
    What i m missing here ?

  • #2
    just a quick pointer

    in spring-batch-samples there are 2 examples with SqlPagingQueryProviderFactoryBean (just search for the class name) in both examples namedParameters are used (look for the "whereClause")

    a look into the abstract parent AbstractSqlPagingQueryProvider makes me wonder if the implementation works if no parameter (named or not) is included in one of the user provided SQLs

    see
    • SqlWindowingPagingQueryProvider.generateFirstPageQ uery(...)
    • SqlWindowingPagingQueryProvider.generateRemainingP agesQuery(...)
    • AbstractSqlPagingQueryProvider.init(...)
    • AbstractSqlPagingQueryProvider.getSortKeyPlaceHold er()

    i will make some tests this weekend, but not today :-)

    Comment


    • #3
      can you please try a test, where you replace

      Code:
                      <property name="whereClause"
                          value="WHERE status IN ('17','38') AND XMLCAST( XMLQUERY(... ) &lt;=(select current date from sysibm.sysdummy1)" />
                      <property name="sortKey" value="colid" />
      with
      Code:
                      <property name="whereClause">
                          <value>
                              <![CDATA[
                                  WHERE status IN ('17','38') AND XMLCAST( XMLQUERY(... ) <=(select current date from sysibm.sysdummy1)
                              ]]>
                          </value>
                      </property>

      ?

      it's because under the hood SqlPagingQueryProviderFactoryBean uses JdbcParameterUtils and its countParameterPlaceholders method counts an & as an sign for a (starting) placeHolder
      Last edited by michael.lange; Sep 24th, 2011, 06:52 AM.

      Comment


      • #4
        Great !

        I used CDATA and it works. I had some more & in the xml query, but now work perfectly.

        Code:
         2011-09-25 10:09:05,983 DEBUG [org.springframework.batch.item.database.JdbcPagingItemReader] - <SQL used for reading remaining pages: [SELECT ... <=(select current date from sysibm.sysdummy1) AND colid > ? ORDER BY colid ASC FETCH FIRST 2 ROWS ONLY]>
        2011-09-25 10:09:05,983 DEBUG [org.springframework.batch.item.database.JdbcPagingItemReader] - <Using parameterList:[351207]>
        Thanks a lot !

        Comment

        Working...
        X