org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80] with root cause com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80

Spring JIRA | Miles Huang | 3 years ago
tip
Your exception is missing from the Samebug knowledge base.
Here are the best solutions we found on the Internet.
Click on the to mark the helpful solution and get rewards for you help.
  1. 0

    To reproduce this issue: 1. Create more than 20 job executions in the job repository. 2. From spring batch admin web application, select Executions. 3. Click Next page. An error screen will show up. Root cause: The SQL generated by the Db2PagingQueryProvider (SqlWindowingPagingQueryProvider) is: SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC The sortKey for this paginate query is "E.JOB_EXECUTION_ID". And the SQL seems to have problem on the field name since the table alias is only applicable for the subquery instead of the outer main query. Correct SQL should be: SELECT TMP_SUB.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY TMP_SUB.JOB_EXECUTION_ID DESC Attached stack trace for the exception: 17:55:05,750 DEBUG http-bio-8080-exec-7 support.SQLErrorCodesFactory:175 - SQL error codes for 'DB2/LINUXX8664' found 17:55:05,760 DEBUG http-bio-8080-exec-7 support.SQLErrorCodeSQLExceptionTranslator:403 - Translating SQLException with SQL state '42703', error code '-206', message [DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80]; SQL was [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC] for task [StatementCallback] Aug 27, 2013 5:55:05 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [Batch Servlet] in context with path [/batchsampleweb] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80] with root cause com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:127) at com.ibm.db2.jcc.am.fo.c(fo.java:2653) at com.ibm.db2.jcc.am.fo.d(fo.java:2641) at com.ibm.db2.jcc.am.fo.a(fo.java:2090) at com.ibm.db2.jcc.t4.cb.h(cb.java:141) at com.ibm.db2.jcc.t4.cb.b(cb.java:41) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.sb.i(sb.java:135) at com.ibm.db2.jcc.am.fo.ib(fo.java:2059) at com.ibm.db2.jcc.am.fo.a(fo.java:3130) at com.ibm.db2.jcc.am.fo.a(fo.java:688) at com.ibm.db2.jcc.am.fo.executeQuery(fo.java:672) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477) at org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:191) at org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:234) at org.springframework.batch.admin.web.JobExecutionController.list(JobExecutionController.java:161) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:73) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722)

    Spring JIRA | 3 years ago | Miles Huang
    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80] with root cause com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80

    Root Cause Analysis

    1. org.springframework.jdbc.BadSqlGrammarException

      StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80] with root cause com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80

      at com.ibm.db2.jcc.am.id.a()
    2. IBM DB2
      fo.executeQuery
      1. com.ibm.db2.jcc.am.id.a(id.java:677)
      2. com.ibm.db2.jcc.am.id.a(id.java:60)
      3. com.ibm.db2.jcc.am.id.a(id.java:127)
      4. com.ibm.db2.jcc.am.fo.c(fo.java:2653)
      5. com.ibm.db2.jcc.am.fo.d(fo.java:2641)
      6. com.ibm.db2.jcc.am.fo.a(fo.java:2090)
      7. com.ibm.db2.jcc.t4.cb.h(cb.java:141)
      8. com.ibm.db2.jcc.t4.cb.b(cb.java:41)
      9. com.ibm.db2.jcc.t4.q.a(q.java:32)
      10. com.ibm.db2.jcc.t4.sb.i(sb.java:135)
      11. com.ibm.db2.jcc.am.fo.ib(fo.java:2059)
      12. com.ibm.db2.jcc.am.fo.a(fo.java:3130)
      13. com.ibm.db2.jcc.am.fo.a(fo.java:688)
      14. com.ibm.db2.jcc.am.fo.executeQuery(fo.java:672)
      14 frames
    3. org.apache.tomcat
      DelegatingStatement.executeQuery
      1. org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      2. org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
      2 frames
    4. Spring Framework
      JdbcTemplate.queryForObject
      1. org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
      2. org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
      3. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
      4. org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464)
      5. org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472)
      6. org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477)
      6 frames
    5. org.springframework.batch
      JobExecutionController.list
      1. org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:191)
      2. org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:234)
      3. org.springframework.batch.admin.web.JobExecutionController.list(JobExecutionController.java:161)
      3 frames
    6. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      4. java.lang.reflect.Method.invoke(Method.java:601)
      4 frames
    7. Spring
      InvocableHandlerMethod.invokeForRequest
      1. org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
      2. org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
      2 frames
    8. Spring MVC
      FrameworkServlet.doGet
      1. org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
      2. org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
      3. org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
      4. org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
      5. org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
      6. org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
      7. org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
      8. org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827)
      8 frames
    9. JavaServlet
      HttpServlet.service
      1. javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
      1 frame
    10. Spring MVC
      FrameworkServlet.service
      1. org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
      1 frame
    11. JavaServlet
      HttpServlet.service
      1. javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
      1 frame
    12. Glassfish Core
      ApplicationFilterChain.doFilter
      1. org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
      2. org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
      2 frames
    13. Spring
      OncePerRequestFilter.doFilter
      1. org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
      2. org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      2 frames
    14. Glassfish Core
      ApplicationFilterChain.doFilter
      1. org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
      2. org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
      2 frames
    15. Spring
      OncePerRequestFilter.doFilter
      1. org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:73)
      2. org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
      2 frames
    16. Glassfish Core
      CoyoteAdapter.service
      1. org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
      2. org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
      3. org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
      4. org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
      5. org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
      6. org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
      7. org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
      8. org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
      9. org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
      10. org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
      10 frames
    17. Grizzly HTTP
      JIoEndpoint$SocketProcessor.run
      1. org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008)
      2. org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
      3. org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
      3 frames
    18. Java RT
      Thread.run
      1. java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
      2. java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
      3. java.lang.Thread.run(Thread.java:722)
      3 frames