Running H2 under Spring Download

Question

I created the Spring Boot web application using the Spring Initializer, the built-in Tomcat, the Thymeleaf template engine, and the package as an executable JAR file.

Technologies used:

Spring Download 1.4.2.RELEASE, Spring 4.3.4.RELEASE, Thymeleaf 2.1.5.RELEASE, Tomcat Paste 8.5.6, Maven 3, Java 8

This is the bean I call when starting DB

@SpringBootApplication @EnableAutoConfiguration @Import({SecurityConfig.class}) public class BookApplication { public static void main(String[] args) { SpringApplication.run(BookApplication.class, args); } } @Configuration public class PersistenceConfig { ... /** * Creates an in-memory "books" database populated * with test data for fast testing */ @Bean public DataSource dataSource(){ return (new EmbeddedDatabaseBuilder()) .addScript("classpath:db/H2.schema.sql") .addScript("classpath:db/H2.data.sql") .build(); } 

When I launched this insert in

 CREATE TABLE IF NOT EXISTS t_time_lapse ( id bigint PRIMARY KEY, name varchar(50) NOT NULL, description varchar(200) NOT NULL, sunday boolean DEFAULT NULL, monday boolean DEFAULT NULL, tuesday boolean DEFAULT NULL, wednesday boolean DEFAULT NULL, thursday boolean DEFAULT NULL, friday boolean DEFAULT NULL, saturday boolean DEFAULT NULL, init_period date NOT NULL , end_period date NOT NULL , init_time time DEFAULT NULL, end_time time DEFAULT NULL, company_id bigint DEFAULT NULL, FOREIGN KEY (company_id) REFERENCES public.t_company(id) ); insert into T_TIME_LAPSE (ID, NAME, DESCRIPTION, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY, INIT_PERIOD, END_PERIOD, INIT_TIME, END_TIME, COMPANY_ID) values (9090,'key', 'key', 1,1,1,1,1,1,1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), 1); 

I got this error

 user lacks privilege or object not found: PARSEDATETIME 

Executing the same query in the data source explorer -> Database Links -> SQL Scrapbook - everything is in order!

adding SHOW CREATE FUNCTION PARSEDATETIME to the script:

 Failed to execute SQL script statement #1 of class path resource [db/H2.data.sql]: SHOW CREATE FUNCTION PARSEDATETIME; nested exception is java.sql.SQLSyntaxErrorException: unexpected token: SHOW 

and CREATE FUNCTION PARSEDATETIME;

 Failed to execute SQL script statement #1 of class path resource [db/H2.data.sql]: CREATE FUNCTION PARSEDATETIME; nested exception is java.sql.SQLSyntaxErrorException: unexpected end of statement: required: ( 

and with a suggested example:

 Failed to execute SQL script statement #2 of class path resource [db/H2.data.sql]: INSERT INTO test values (1, CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT')); nested exception is java.sql.SQLSyntaxErrorException: unexpected token: CALL 

Answer 1

I tried to reproduce your problem by creating a Spring boot project from scratch using the dependencies spring-boot-starter-data-jpa and h2 . I have done two things:

1) I placed your scripts in /resources with the names schema.sql and data.sql to create and populate the database, respectively. By default, Spring Boot will load SQL from these places , as described here .

2) I configured the testdb H2 database in application.properties as follows:

 # H2 database configuration spring.datasource.url = jdbc:h2:file:~/testdb;DB_CLOSE_ON_EXIT=FALSE # Enable SQL script scanning in /resources folder spring.jpa.hibernate.ddl-auto=none # Enable H2 console under http://localhost:8080/console/ for dev purposes spring.h2.console.enabled=true spring.h2.console.path=/console/ 

As a result, the H2 database is populated with data samples that you provided without errors (I did not configure the DataSource as you did in PersistenceConfig and nothing more / nothing more).

If you want to stick to custom SQL script locations, consider setting up a DataSource after this answer fooobar.com/questions/152564 / ....

Answer 2

For some reason, setting a saved PARSEDATETIME function PARSEDATETIME not allow you to access it. Provide SHOW CREATE FUNCTION PARSEDATETIME . And check out the spring stuff.

Or, more likely, PARSEDATETIME is a Java function, not a MySQL function.

Note. The place where you are using implies that it is a MySQL function. To use it as a Java function, you need to "bind" it to INSERT .

Answer 3

You tried to change the insert statement from

 insert into T_TIME_LAPSE (ID, NAME, DESCRIPTION, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY, INIT_PERIOD, END_PERIOD, INIT_TIME, END_TIME, COMPANY_ID) values (9090,'key', 'key', 1,1,1,1,1,1,1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), 1); 

to

 insert into T_TIME_LAPSE (ID, NAME, DESCRIPTION, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY, INIT_PERIOD, END_PERIOD, INIT_TIME, END_TIME, COMPANY_ID) values (9090,'key', 'key', 1,1,1,1,1,1,1,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), CALL PARSEDATETIME('03:05:06 GMT','HH:mm:ss z', 'en', 'GMT'), 1); 

?