Announcement Announcement Module
Collapse
No announcement yet.
Error in batch admin sample application Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error in batch admin sample application

    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.

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

  • #2
    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)?
    Last edited by Dave Syer; Jan 30th, 2010, 03:47 AM. Reason: Added JIRA link

    Comment


    • #3
      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

      Comment


      • #4
        Similar type of Bug when running with Sybase ASA

        Originally posted by Dave Syer View Post
        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

        Comment

        Working...
        X