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

[ticino]

Hallo,

I have a batch job with this reader :

<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 :

 ... 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 ?

[michael.lange]

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 :-)

[michael.lange]

can you please try a test, where you replace

                <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

                <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

[ticino]

Great !

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

 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 !