org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "drop table if exists [*]user CASCADE "; expected "identifier"; SQL statement:

Question

I've below data.sql file in my src/main/resources/data.sql file. I would like user tabel to be created from user.csv file.

DROP TABLE IF EXISTS `USER` CASCADE;
CREATE TABLE `user` AS SELECT * FROM CSVREAD('classpath:user.csv');

When the spring application starts it always errors out with below error -

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "drop table if exists [*]user CASCADE "; expected "identifier"; SQL statement:

I've spent long time to figure out the error but couln't. Any idea how can I fix this error?

Please also include the relevant Java code.

Tim Biegeleisen
– Tim Biegeleisen

2022-04-03 02:20:39 +00:00
Commented Apr 3, 2022 at 2:20 — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 3, 2022 at 2:20

lu_ko · Accepted Answer · 2022-05-12 10:40:37Z

28

There is a keyword USER in newer versions of H2 database, therefore You need to escape it correctly. You should use double quote " instead of single quote '.

Copy/paste from H2 documentation:

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes). The following tokens are keywords in H2:

..., USER, ...

Newer versions of H2 may have more keywords than older ones.

answered May 12, 2022 at 10:40

lu_ko

4,3132 gold badges29 silver badges32 bronze badges

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

1 Comment

ScrappyDev Over a year ago

Wow, sounds like a bug with H2.

Ramesh · Accepted Answer · 2022-04-03 03:06:15Z

7

Always check if you are able to run same query on database you are using through DBMS tool like pgadmin or toad. I assume you are using postgresql, so user keyword is reserved if u still. Need to create table with name user, it must be enclosed in double quotes, may be you can try with double quotes instead single quotes in Java you will need to escape this double quotes to get included into string text for this query text

DROP TABLE IF EXISTS "USER" CASCADE;
CREATE TABLE "user" AS SELECT * FROM CSVREAD('classpath:user.csv')

answered Apr 3, 2022 at 3:06

Ramesh

4904 silver badges14 bronze badges

Collectives™ on Stack Overflow

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "drop table if exists [*]user CASCADE "; expected "identifier"; SQL statement:

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