CODEWITHSUNDEEP
javasqlitespring-jdbc
Andreas
Andreas

Reputation: 125

SQLiteException when initializing database with Spring JDBC

I'm trying to create a SQLite database with spring-jdbc. I'm doing this using a automatic initialization with this XML configuration:

<jdbc:initialize-database data-source="logDbDataSource" enabled="true">
    <jdbc:script location="classpath:scheme.sql" />
</jdbc:initialize-database>

The scheme.sql creates a table for logging events. I also want to automatically delete old data using a trigger, so the SQL looks like this:

CREATE TABLE IF NOT EXISTS events (
    event_id INTEGER PRIMARY KEY AUTOINCREMENT,
    time DATETIME DEFAULT CURRENT_TIMESTAMP,
    value TEXT NOT NULL
);

CREATE TRIGGER IF NOT EXISTS cleanup
AFTER INSERT ON events
BEGIN
    DELETE FROM events WHERE time < datetime('now', '-35 days');
END;

This works totally fine when I create the database manually with

sqlite3 eventDb.sqlite < schema.sql.

My problem is, that it leads to the following exception when starting the program, where this is handled by Spring:

java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:53)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.jdbc.datasource.init.DataSourceInitializer#0': Invocation of init method failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [homectrlLog-schema.sql]: CREATE TRIGGER IF NOT EXISTS cleanup AFTER INSERT ON events BEGIN DELETE FROM events WHERE time < datetime('now', '-35 days')
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:304)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:300)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:700)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
at org.atennert.homectrl.Main.main(Main.java:34)
... 6 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to execute database script; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [homectrlLog-schema.sql]: CREATE TRIGGER IF NOT EXISTS cleanup AFTER INSERT ON events BEGIN DELETE FROM events WHERE time < datetime('now', '-35 days')
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:56)
at org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:84)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1612)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1549)
... 18 more
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 3 of resource class path resource [homectrlLog-schema.sql]: CREATE TRIGGER IF NOT EXISTS cleanup AFTER INSERT ON events BEGIN DELETE FROM events WHERE time < datetime('now', '-35 days')
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:202)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:135)
at org.springframework.jdbc.datasource.init.CompositeDatabasePopulator.populate(CompositeDatabasePopulator.java:56)
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:47)
... 21 more
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near ")": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:920)
at org.sqlite.core.DB.newSQLException(DB.java:932)
at org.sqlite.core.DB.throwex(DB.java:897)
at org.sqlite.core.NativeDB.prepare(Native Method)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:60)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:187)
... 24 more

My best guess is, there are some problems with automatic population and the datetime function (same problem with the date function). Everything works fine, when I remove the trigger from the script.

Is this an issue with SQLite / SQL functions and/or automatic initialization with Spring JDBC? Do I have to write it differently to get it working for automatic initialization?

Upvotes: 0

Views: 589

Answers (1)

CL.
CL.

Reputation: 180010

Failed to execute SQL script statement at line 3 of
resource class path resource [homectrlLog-schema.sql]:
CREATE TRIGGER IF NOT EXISTS cleanup AFTER INSERT ON events ⏎
BEGIN DELETE FROM events WHERE time < datetime('now', '-35 days')

Whatever code you're using to execute the script splits the SQL at every semicolon. This is wrong when you have triggers, because the entire trigger creation until the final END must be executed as a single statement.

Upvotes: 1

Related Questions