Uploaded image for project: 'Spring Batch Admin'
  1. Spring Batch Admin
  2. BATCHADM-168

Paging through job execution on Oracle fails with exception

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Complete
    • Affects Version/s: 1.3.0
    • Fix Version/s: 1.3.0
    • Component/s: Manager
    • Labels:
      None

      Description

      When current master branch code of Spring Batch Admin is configured to run against Spring Batch 2.2.0 - we cannot page through the job executions screen on Oracle (and likely on some other DBs too) due to the following exception:

      org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM (SELECT E.JOB_EXECUTION_ID, ROWNUM as TMP_ROW_NUM FROM (SELECT E.JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY E.JOB_EXECUTION_ID DESC)) WHERE TMP_ROW_NUM = 20]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "E"."JOB_EXECUTION_ID": invalid identifier

      org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
      org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
      org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
      org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464)
      org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472)
      org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477)
      org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:191)
      org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:234)

      The problem is due to prefixing JOB_EXECUTION_ID column with E. prefix. Once the prefix is removed everything begins to work properly.

      I have tested this fix against all database types (by visually inspecting SQL output) and think it will work fine.

      Note: the E. prefix was present in previous releases of Spring Batch Admin and things appeared to work fine. There is a possibility that something has changed in the PagingQueryProvider implementation in Spring Batch 2.2.0.

      The pull request is coming.

        Attachments

          Activity

            People

            • Assignee:
              chrisschaefer Chris Schaefer
              Reporter:
              kyrill007 Kyrill Alyoshin
            • Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: