Announcement Announcement Module
Collapse
No announcement yet.
BadSqlGrammarException running CommandLineJobRunner Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • BadSqlGrammarException running CommandLineJobRunner

    Hello,

    I am trying to use the CommandLineJobRunner to run one of the sample jobs. I am using the org.springframework.batch-2.0.0.RELEASE-with-dependencies.zip bundle as I am not using Maven. My database is Oracle 10g. I have the metadata tables created with a schema name, e.g. MYDB. I am using the batch.schema=MYDB in the batch-oracle.properties file . So I am guessing the select statement as shown below would be like -
    Code:
    SELECT JOB_INSTANCE_ID, JOB_NAME from MYDB.BATCH_JOB_INSTANCE where JOB_NAME ...
    If needed here is simple-job-launcher-context.xml I am using -
    Code:
    <import resource="data-source-context.xml" />
    	<import
    		resource="classpath:/org/springframework/batch/sample/config/common-context.xml" />
    	<bean id="jobLauncher"
    		class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
    		<property name="jobRepository" ref="jobRepository" />
    	</bean>
    
    	<bean class="org.springframework.batch.core.configuration.support.JobRegistryBeanPostProcessor">
    		<property name="jobRegistry" ref="jobRegistry"/>
    	</bean>
    
    	<bean id="jobRepository"
    		class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"
    		p:dataSource-ref="dataSource" p:transactionManager-ref="transactionManager" />
    
    	<bean id="mapJobRepository"
    		class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"
    		lazy-init="true" autowire-candidate="false" />
    
    	<bean id="jobOperator"
    		class="org.springframework.batch.core.launch.support.SimpleJobOperator"
    		p:jobLauncher-ref="jobLauncher" p:jobExplorer-ref="jobExplorer"
    		p:jobRepository-ref="jobRepository" p:jobRegistry-ref="jobRegistry" />
    
    	<bean id="jobExplorer"
    		class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean"
    		p:dataSource-ref="dataSource" />
    
    	<bean id="jobRegistry"
    		class="org.springframework.batch.core.configuration.support.MapJobRegistry" />
    
    	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    
    	<bean id="logAdvice" class="org.springframework.batch.sample.common.LogAdvice" />
    
    	<bean id="eventAdvice"
    		class="org.springframework.batch.sample.jmx.StepExecutionApplicationEventAdvice" />
    
    </beans>
    and here is the first part of the error messages I am getting -

    Code:
    13:18:14,145 ERROR main CommandLineJobRunner:211 - Job Terminated in error:
    org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLException: ORA-00942: table or view does not exist
    
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
    Wondering what code is executing SQL preparedstatement for these table inserts? Is there certain setup I need to do to fix this problem?

    Thanks

  • #2
    First, you don't need to declare a "jobRepository" and "mapJobRepository". The "mapJobRepository" is just an in-memory version of a JobRepository, so since you are using the database, you don't need it.

    If you need to access the table as "MYDB.BATCH_JOB_INSTANCE" then you'll have to override the "tablePrefix" on the JobRepositoryFactoryBean to include the schema name. The default prefix is always "BATCH_". Yours will have to be "MYDB.BATCH_".

    Comment


    • #3
      So, take out -

      Code:
      <bean id="jobRepository"
      		class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"
      		p:dataSource-ref="dataSource" p:transactionManager-ref="transactionManager" />
      
      	<bean id="mapJobRepository"
      		class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean"
      		lazy-init="true" autowire-candidate="false" />
      tags and leave the rest in the simple-job-launcher-context.xml file?

      Next about modifying the JobRepositoryFactoryBean - is the code in one of the jar file? Now going back to question about metadata tables which belongs to a schema. In my case it's MYDB. Kind of puzzled why this schema name has to be hardcoded in the code if I have understood it correctly? what was the purpose of the 'batch.schema' key for in the batch-oracle.properties then?

      Thanks

      Comment


      • #4
        No. Remove "mapJobRepository". Change "jobRepository":
        Code:
        <bean id="jobRepository"
              class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"
              p:dataSource-ref="dataSource" p:transactionManager-ref="transactionManager" 
              p:tablePrefix="MYDB.BATCH_"/>

        Comment


        • #5
          Got it. Let me try it out.

          As always thanks a lot!

          Comment

          Working...
          X