3

My project has this requirement where user uploads a CSV file which has to be pushed to sql server database. I am following the below basic example to load CSV file into a sql sever database. https://github.com/michaelcgood/Spring-Batch-CSV-Example runninng this repo by making change of datasource, here we using sql server instead of in-memory db.

here is the addition to POM file:

<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
<</dependency>

additions to applications.properties file

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=springbootdb
spring.datasource.username=sa
spring.datasource.password=Projects@123

Below is the error generated while running the code base, we could not found exact root cause we tried the multiple approaches as mentioned in the google but no luck, while running this spring batch+spring boot 2.0.4 application we are facing the below errors:

    spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
o.s.boot.autoconfigure.jdbc.Datasource.initailizer disabled(not running DDL scripts)

at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE
 where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is java.sql.SQLServerException: Invalid object name 'BATCH_JOB_INSTANCE'.

We are assuming the root cause one of the below?

1.We are using the spring starter project for creating spring batch configuration so not aware how we could define the default table schema and all.

2.maybe we don't have access and/or you need to define the schema. http://forum.spring.io/forum/spring-projects/batch/63771-badsqlgrammarexception-running-commandlinejobrunner

3.Not sure on the why error is saying Invalid object name 'BATCH_JOB_INSTANCE' instead of object doesnot exist? What are the list of sql queries to create metadate table for spring batch application, to create it manually before running application.

4.we are struggling from couple of days to find the root cause of issue, Please suggest approch by taking the above github code base sample. any help here would be appreciated. Please let us know if we missed anything here. Thanks In advance

MukthyarStack
  • 195
  • 1
  • 3
  • 15
  • so where are those DDL scripts, that are supposed to get executed? – Jens Schauder Sep 02 '18 at 07:09
  • here is the DDL script for it https://github.com/michaelcgood/Spring-Batch-CSV-Example/blob/master/src/main/resources/schema.sql – MukthyarStack Sep 02 '18 at 07:13
  • It doesn't create a table `BATCH_JOB_INSTANCE` – Jens Schauder Sep 02 '18 at 07:36
  • @jens Can you please explain something more on your answers, you mean BATCH_JOB_INSTANCE not getting created by our application? – MukthyarStack Sep 02 '18 at 08:22
  • The script you linked to doesn't create the table `BATCH_JOB_INSTANCE`. Your application fails with an error showing that table does not exist. I don't understand why this combination of events leads to the question in the title: "Not running DDL scripts". No matter if your script got executed or not I would never create the table in question. – Jens Schauder Sep 02 '18 at 10:02
  • application is failign with Invalid object name 'BATCH_JOB_INSTANCE', these tables belongs to spring batch meta data these should auto create with sprint batch application. I am assuming Not running DDL and not creating tables for same reason. thanks – MukthyarStack Sep 02 '18 at 15:25

1 Answers1

3

The project you are linking to uses the in-memory hsqldb instance so Spring Boot will automatically create Spring Batch meta-data tables for you.

If you want to adapt the sample to your project and use sql server (which is not embedded in your JVM), Spring Boot will not create Spring Batch tables (unless you instruct it to do so). In this case, you have two options:

  • Run the Spring Batch DDL script for sqlserver yourself against the db server you are using before running your app

  • Or instruct Spring Boot to do it for you by specifying the following properties in your application.properties file:

    spring.batch.initialize-schema=always spring.batch.schema=classpath:org/springframework/batch/core/schema-sqlserver.sql

Mahmoud Ben Hassine
  • 28,519
  • 3
  • 32
  • 50
  • Thanks, We decided not to create metadatable/ sql table via prod application, we wan to insert only record. how can I skip them these creations? while trying getting Error creating bean 'HibernateJpaAutoConfiguration'. Please suggest what changes i need to do for this given https://github.com/michaelcgood/Spring-Batch-CSV-Example – MukthyarStack Sep 04 '18 at 06:46
  • We are struggling to fix it, Please download and try with this repo. Thanks Much – MukthyarStack Sep 04 '18 at 06:47
  • 1
    > `We decided not to create metadatable/ sql table via prod application, we wan to insert only record. ` In this case, you need to tell Spring Boot to never try to initialize the schema with `spring.batch.initialize-schema=never`. You would need to either use a Map based job repository or use an embedded database for Spring Batch. See https://stackoverflow.com/a/44243647/5019386 – Mahmoud Ben Hassine Sep 04 '18 at 09:05