Error in batch admin sample application

[Shriblue]

When I did a next page on the UI for job executions, it fails because of BAD SQL syntax. The DB is SQL server.

The inner query needs an alias for this to work properly.

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT SORT_KEY FROM ( SELECT E.JOB_EXECUTION_ID AS SORT_KEY, ROW_NUMBER() OVER (ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) WHERE ROW_NUMBER = 20]; nested exception is java.sql.SQLException: Incorrect syntax near the keyword 'WHERE'.
	org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
	org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
	org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
	org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:471)
	org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:476)
	org.springframework.jdbc.core.JdbcTemplate.queryForLong(JdbcTemplate.java:480)
	org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForLong(SimpleJdbcTemplate.java:127)
	org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:185)
	org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:209)
	org.springframework.batch.admin.web.JobExecutionController.list(JobExecutionController.java:143)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	java.lang.reflect.Method.invoke(Method.java:597)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:731)
	org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:168)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:385)
	org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:373)
	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
	org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:71)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:57)
	org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

[Dave Syer]

That's a bug in Spring Batch actually (http://jira.springframework.org/browse/BATCH-1497). I wasn't aware of the need for an alias, so thanks for pointing that out. Can you write down the correct SQL for that query for use and paste it in the JIRA (and back here if you like)?

[Shriblue]

Added the query to the JIRA issue..

For MS-SQL server, the correct query for

SELECT
SORT_KEY
FROM
( SELECT
E.JOB_EXECUTION_ID AS SORT_KEY,
ROW_NUMBER() OVER (ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER
FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I
WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID)
WHERE ROW_NUMBER = 20

with the alias is

SELECT
SORT_KEY
FROM
( SELECT
E.JOB_EXECUTION_ID AS SORT_KEY,
ROW_NUMBER() OVER (ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER
FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I
WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) A
WHERE ROW_NUMBER = 20

[corpa]

That's a bug in Spring Batch actually (http://jira.springframework.org/browse/BATCH-1497). I wasn't aware of the need for an alias, so thanks for pointing that out. Can you write down the correct SQL for that query for use and paste it in the JIRA (and back here if you like)?

The same pagination of the "Executions" screen code also appears to have a bug that manifests when run with sybase asa - because it seems use of the keyword "OVER" isn't legitimate for sybase asa ( i think sybase iq does have that keyword but that's a lesser-used version of sybase). Interesting that pagination of the "Job Instances for Job" screen does work; I'd have thought the sql needed would have been similar. Anyway, the error I'm seeing is :


HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletExceptio n: Request processing failed; nested exception is org.springframework.dao.TransientDataAccessResourc eException: StatementCallback; SQL [SELECT SORT_KEY FROM ( SELECT E.JOB_EXECUTION_ID AS SORT_KEY, ROW_NUMBER() OVER (ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) WHERE ROW_NUMBER = 20]; Incorrect syntax near the keyword 'OVER'.
; nested exception is java.sql.SQLException: Incorrect syntax near the keyword 'OVER'.

org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:894)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :58)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)


root cause

org.springframework.dao.TransientDataAccessResourc eException: StatementCallback; SQL [SELECT SORT_KEY FROM ( SELECT E.JOB_EXECUTION_ID AS SORT_KEY, ROW_NUMBER() OVER (ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) WHERE ROW_NUMBER = 20]; Incorrect syntax near the keyword 'OVER'.
; nested exception is java.sql.SQLException: Incorrect syntax near the keyword 'OVER'.

org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:107)
org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:407)
org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:456)
org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:464)
org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:472)
org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:477)
org.springframework.jdbc.core.JdbcTemplate.queryFo rLong(JdbcTemplate.java:481)
org.springframework.jdbc.core.simple.SimpleJdbcTem plate.queryForLong(SimpleJdbcTemplate.java:131)
org.springframework.batch.admin.service.JdbcSearch ableJobExecutionDao.getJobExecutions(JdbcSearchabl eJobExecutionDao.java:181)
org.springframework.batch.admin.service.SimpleJobS ervice.listJobExecutions(SimpleJobService.java:231 )
org.springframework.batch.admin.web.JobExecutionCo ntroller.list(JobExecutionController.java:163)
sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:601)
org.springframework.web.method.support.InvocableHa ndlerMethod.invoke(InvocableHandlerMethod.java:213 )
org.springframework.web.method.support.InvocableHa ndlerMethod.invokeForRequest(InvocableHandlerMetho d.java:126)
org.springframework.web.servlet.mvc.method.annotat ion.ServletInvocableHandlerMethod.invokeAndHandle( ServletInvocableHandlerMethod.java:96)
org.springframework.web.servlet.mvc.method.annotat ion.RequestMappingHandlerAdapter.invokeHandlerMeth od(RequestMappingHandlerAdapter.java:617)
org.springframework.web.servlet.mvc.method.annotat ion.RequestMappingHandlerAdapter.handleInternal(Re questMappingHandlerAdapter.java:578)
org.springframework.web.servlet.mvc.method.Abstrac tHandlerMethodAdapter.handle(AbstractHandlerMethod Adapter.java:80)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:923)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :58)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)


root cause

java.sql.SQLException: Incorrect syntax near the keyword 'OVER'.

net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnos tic(SQLDiagnostic.java:368)
net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2820)
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:2258)
net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:632)
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL Query(JtdsStatement.java:477)
net.sourceforge.jtds.jdbc.JtdsStatement.executeQue ry(JtdsStatement.java:1304)
org.apache.commons.dbcp.DelegatingStatement.execut eQuery(DelegatingStatement.java:208)
org.springframework.jdbc.core.JdbcTemplate$1QueryS tatementCallback.doInStatement(JdbcTemplate.java:4 41)
org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:396)
org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:456)
org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:464)
org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:472)
org.springframework.jdbc.core.JdbcTemplate.queryFo rObject(JdbcTemplate.java:477)
org.springframework.jdbc.core.JdbcTemplate.queryFo rLong(JdbcTemplate.java:481)
org.springframework.jdbc.core.simple.SimpleJdbcTem plate.queryForLong(SimpleJdbcTemplate.java:131)
org.springframework.batch.admin.service.JdbcSearch ableJobExecutionDao.getJobExecutions(JdbcSearchabl eJobExecutionDao.java:181)
org.springframework.batch.admin.service.SimpleJobS ervice.listJobExecutions(SimpleJobService.java:231 )
org.springframework.batch.admin.web.JobExecutionCo ntroller.list(JobExecutionController.java:163)
sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:601)
org.springframework.web.method.support.InvocableHa ndlerMethod.invoke(InvocableHandlerMethod.java:213 )
org.springframework.web.method.support.InvocableHa ndlerMethod.invokeForRequest(InvocableHandlerMetho d.java:126)
org.springframework.web.servlet.mvc.method.annotat ion.ServletInvocableHandlerMethod.invokeAndHandle( ServletInvocableHandlerMethod.java:96)
org.springframework.web.servlet.mvc.method.annotat ion.RequestMappingHandlerAdapter.invokeHandlerMeth od(RequestMappingHandlerAdapter.java:617)
org.springframework.web.servlet.mvc.method.annotat ion.RequestMappingHandlerAdapter.handleInternal(Re questMappingHandlerAdapter.java:578)
org.springframework.web.servlet.mvc.method.Abstrac tHandlerMethodAdapter.handle(AbstractHandlerMethod Adapter.java:80)
org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:923)
org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :58)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)


note The full stack trace of the root cause is available in the Apache Tomcat/6.0.35 logs.


--------------------------------------------------------------------------------

Apache Tomcat/6.0.35