Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JdbcPagingItemReader query fails when specifiying multiple columns in sortKey [BATCH-1749] #1842

Closed
spring-projects-issues opened this issue May 12, 2011 · 2 comments
Labels
has: backports Legacy label from JIRA. Superseded by "for: backport-to-x.x.x" in: build type: feature
Milestone

Comments

@spring-projects-issues
Copy link
Collaborator

spring-projects-issues commented May 12, 2011

Bryce Cummock opened BATCH-1749 and commented

I currently receive the following error when specify more than 1 column in the sortKey parameter, when querying an Oracle 10g database from a Spring Batch application:

12 05 2011 13:54:57: ERROR:Encountered an error executing the step
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT * FROM (SELECT Account, Description, ROWNUM as TMP_ROW_NUM FROM batch_test ORDER BY Account, Description ASC) WHERE ROWNUM <= 1000]; nested exception is java.sql.SQLException: Invalid column name

The query 'SELECT * FROM (SELECT Account, Description, ROWNUM as TMP_ROW_NUM FROM batch_test ORDER BY Account, Description ASC) WHERE ROWNUM <= 1000' works without error when executed in a SQL editor against the same database (there in only 1 db with this table defined).

The columns definitely exist, and the query works fine if I remove either Account or Description from sortKey.

Bean config details are below:

<bean id="glPostingsJdbcReader"
	class="org.springframework.batch.item.database.JdbcPagingItemReader" scope="step">
	<property name="dataSource" ref="fusionDataSource" />
	<property name="queryProvider">
		<bean
			class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
			<property name="dataSource" ref="fusionDataSource" />
			<property name="selectClause"  
				 value="Account, Description" />
				
			<property name="fromClause" value="from batch_test" />
			<property name="sortKey" value="Account, Description" />
		</bean>
	</property>
	<property name="pageSize" value="1000" />
	<property name="rowMapper">
		<bean class="StagingFromDbMapper"  scope="step"/>
	</property>
</bean>

DB defn:

CREATE TABLE BATCH_TEST
(
ACCOUNT VARCHAR2(20 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(20 BYTE)
)

Thanks,

Bryce


Affects: 2.1.7

Referenced from: commits 1cf9573, 920d0e5, 12d9918

Backported to: 2.2.0 - Sprint 4

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented May 12, 2011

Dave Syer commented

Regardless of whether the query is correct or not, the item reader doesn't work on restart with multiple column keys. It was never designed to work with multi-column keys in fact, so that should probably be clearer from the documentation (but it is clear from reading the source code). I've marked this issue speculatively as fix for 2.2, but that will depend on someone coming up with a solution. Contributions welcome.

@spring-projects-issues
Copy link
Collaborator Author

spring-projects-issues commented Nov 2, 2012

Michael Minella commented

Pull request: #65

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
has: backports Legacy label from JIRA. Superseded by "for: backport-to-x.x.x" in: build type: feature
Projects
None yet
Development

No branches or pull requests

1 participant