Extending JdbcCursorItemReader no longer reads db.

[philipk]

I am trying to extend the JdbcCursorItemReader so that I can dynamically set the SQL select statement.

Starting with a working batch job that retrieves data from an Oracle db using JdbcCursorItemReader I wrote a class to extend JdbcCursorItemReader and implemented my own setSql to set the query I generate at runtime.

When I run the batch no data is retrieved from the db, nor are any errors reported anywhere. I have logged the generated SQL and it is correct, and when run from sqldeveloper returns data. It rather looks as if the read is no longer being called.

I am using batch version 2.1.6.Release and Spring 3.0.5.Release
Can anyone help?

This is the reader configuration:

	<bean id="reader" scope="step"
		class="com.me.batch.MyItemReader">
	    <property name="serviceProvider" value="#{jobParameters['sp.id']}"/>
	    <property name="extractMonth" value="#{jobParameters['extract.month']}"/>
<!--
			class="org.springframework.batch.item.database.JdbcCursorItemReader">
-->
	    <property name="dataSource" ref="dataSource"/> 
		<property name="sql" value="select * from V_EXPORT"/>
		<property name="verifyCursorPosition" value="${batch.verify.cursor.position}"/>
		<property name="rowMapper">
			<bean class="com.me.batch.RechargeReadingRowMapper"/>
		</property>
	</bean>

As shown it runs fine but doesn't work, but using the commented class instead of my own it does work.

My class is defined like this (extract):

public class MyItemReader extends JdbcCursorItemReader<RechargeReading>  {

    public void setSql(String newsql)
	{
    	// The passed query is ignored. The set serves only as a trigger to create a new query from previously set data.
		log.debug("Parameter newsql = " + newsql);
		query = "select * from V_EXPORT where START_TIME between to_date ('" + extractMonth + "01', 'yyyymmdd') AND to_date ('" + extractMonth + lastDay + "', 'yyyymmdd')";
		super.setSql(query);
		log.info("Actual query sql = " + query);
	}

[arno]

I don't see where the problem is, but why don't you inject a PreparedStatementSetter? looks like it's meant to do what you want (assigning parameters.)

[philipk]

Because I thought it would be simpler to extend the JdbcCursorItemReader, however I shall now try your suggestion.

[philipk]

I tried with a PreparedStatementSetter and it didn't work, in the sense that there was no error and no results. After much experimenting I discovered that a working query stopped working if I added "and SEND_TYPE='N'" to the where clause.

Running the exact same query from sqldeveloper worked.

[arno]

how do you set the clause? by concatenation or with the prepared statement?

[philipk]

I set the and clause in the sql string property of the item reader, but as noted in a separate thread the root cause was an incompatible Oracle jdbc driver. It is necessary to check the ojdbc6.jar manifest to determine the version of the driver, and make sure it is compatible with the Oracle server being used.

At the time of starting this thread I had not realised that I had also changed the query to contain a single quote, so the thread title is misleading.

Philip