H2 SQL Grammar Exception

Question

I am trying to import a sql script for H2. This script is provided by spring-batch and it's used to store the jobs metadata. When I execute this script directly in the H2 console, I have no syntax errors, but I referenced the same script in Hibernate/JPA to be imported at the initialization phase, I got this exception :

 org.hibernate.tool.hbm2ddl.ImportScriptException: Error during statement execution (file: 'org/springframework/batch/core/schema-h2.sql'): CREATE TABLE BATCH_JOB_INSTANCE  (
   ....    
    Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE BATCH_JOB_INSTANCE  ("; expected "identifier"; SQL statement:
CREATE TABLE BATCH_JOB_INSTANCE  ( [42001-171]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2-1.3.171.jar:1.3.171]
    at org.h2.message.DbException.get(DbException.java:169) ~[h2-1.3.171.jar:1.3.171]
    at org.h2.message.DbException.getSyntaxError(DbException.java:194) ~[h2-1.3.171.jar:1.3.171]

Here is the script I am trying to execute : https://code.google.com/p/joshlong-examples/source/browse/trunk/batch/src/main/resources/sql/schema-h2.sql?r=2

I am using hbm2ddl to import the sql file :

jpaProperties.setProperty("hibernate.connection.driver_class", "org.h2.Driver");
    jpaProperties.setProperty("hibernate.dialect", H2Dialect.class.getName());
    jpaProperties.setProperty("hibernate.hbm2ddl.auto", "create-drop");
    jpaProperties.setProperty("hibernate.hbm2ddl.import_files",
        "org/springframework/batch/core/schema-drop-h2.sql,org/springframework/batch/core/schema-h2.sql");

Any idea how I can solve this issue?

Ralph · Accepted Answer · 2013-07-29T14:32:09.757

11

Try to write each of your create statements in one line.

The statement delimiter in import.sql is a newline. If you want to change it, then you need to use Hibernate > 4.1. There you can Implement a MultipleLinesSqlCommandExtractor and specify it by hibernate.hbm2ddl.import_files_sql_extractor

edited Jul 29 '13 at 14:32

answered Jul 29 '13 at 14:24

Ralph

118,862
56
287
383

Thanks for your answer. When I execute the scripts directly in H2, I have no problem. But when I am using hibernate to import the files, I have this error. – Dimitri Jul 29 '13 at 14:31
1

It is a Hibernate "Problem". – Ralph Jul 29 '13 at 14:32
It works in with your Editor (but not with Hibernate) because the delimiter is handled in the client not in the server. So each client can handle it in a different way. – Ralph Jul 29 '13 at 14:41

score 2 · Answer 2 · answered Jul 29 '13 at 15:11

I finally found the answer to my question. Based on Ralph's answer, to correct this issue, add the following property for hibernate :

jpaProperties.setProperty("hibernate.hbm2ddl.import_files_sql_extractor", "org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor");

Or in XML :

<property key="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />

This class MultipleLinesSqlCommandExtractor is an implementation of the interface ImportSqlCommandExtractor. This is interface is called when doing SchemaExport with Hibernate. The default implementation is SingleLineSqlCommandExtractor and for unknown reason returns a syntax error. Replacing the single line extractor with the multiple line extractor solved the isssue.

H2 SQL Grammar Exception

2 Answers2

Linked