Syntax Error in SQL Statement with H2

Question

I have an error while some sql statements in H2 database. Those sql statements come from a Hibernate SchemaExport : Here are the sql statements :

create table CONTACTS (
    person_id bigint not null,
    contact_id bigint not null,
    primary key (person_id, contact_id)
)

 create table PERSON (
    id bigint generated by default as identity,
    FNAME varchar(55),
    NAME varchar(55),
    primary key (id)
)

alter table CONTACTS 
    add constraint UK_r5plahp7wqcsd47hscckyrxgd unique (contact_id)

alter table CONTACTS 
    add constraint FK_r5plahp7wqcsd47hscckyrxgd 
    foreign key (contact_id) 
    references PERSON

alter table CONTACTS 
    add constraint FK_90n1kse999lanaepr0v6hcgtv 
    foreign key (person_id) 
    references PERSON

For instance, this line won't execute in H2.

The error says : [ERROR] Caused by org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement : CREATE TABLE CONTACTS ( .... <<The same code from above>>

How I can make this SQL statement run in H2.

Dimitri · Accepted Answer · 2013-06-19 09:34:34Z

27

I finally find the reason why I had the syntax error.

I am actually running a SchemaExport/SchemaUpdate with Hibernate and I did not specify a delimiter in the SQL statement.

To specify a delimiter, use the setDelimiter method. For instance,

export.setDelimiter(";");
update.setDelimiter(";");

By the way, to identify syntax errors in H2 with SQL statements, find the * in the statement and it will give the line of your error.

answered Jun 19, 2013 at 9:34

Dimitri

8,31819 gold badges75 silver badges133 bronze badges

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

1 Comment

Ken Roy Over a year ago

Error location showed as "[*]" on my implementation, but your response helped!

Clifford Fernandes · Accepted Answer · 2018-10-31 23:57:00Z

-5

The DDL statement must be on a single line.

answered Oct 31, 2018 at 23:57

Clifford Fernandes

353 bronze badges

Collectives™ on Stack Overflow

Syntax Error in SQL Statement with H2

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related