Not running DDL scripts with spring batch + spring boot + sql server application

Question

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

so where are those DDL scripts, that are supposed to get executed? — Jens Schauder
– Jens Schauder, Commented Sep 2, 2018 at 7:09
here is the DDL script for it github.com/michaelcgood/Spring-Batch-CSV-Example/blob/master/… — MukthyarStack
– MukthyarStack, Commented Sep 2, 2018 at 7:13
@jens Can you please explain something more on your answers, you mean BATCH_JOB_INSTANCE not getting created by our application? — MukthyarStack
– MukthyarStack, Commented Sep 2, 2018 at 8: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
– Jens Schauder, Commented Sep 2, 2018 at 10:02

Mahmoud Ben Hassine · Accepted Answer · 2018-09-03 08:26:41Z

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

answered Sep 3, 2018 at 8:26

Mahmoud Ben Hassine

32.1k5 gold badges38 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MukthyarStack Over a year ago

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 github.com/michaelcgood/Spring-Batch-CSV-Example

MukthyarStack Over a year ago

We are struggling to fix it, Please download and try with this repo. Thanks Much

Mahmoud Ben Hassine Over a year ago

> 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 stackoverflow.com/a/44243647/5019386

Collectives™ on Stack Overflow

Not running DDL scripts with spring batch + spring boot + sql server application

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related