filemonczyk filemonczyk - 1 month ago 19
SQL Question

SQL error while running script with spring boot

Im trying to populate my database as I start my project:


  1. spring-boot

  2. h2 embedded database



this is the script:

INSERT INTO 'VET' VALUES (1, 'AAAA', 'BBBB');


here is my entity in java:

@Entity
public class Vet extends BaseClass{

// @Id
// @GeneratedValue
// private int id;
@NotNull(message="{NotNull}")
@Size(min=2,max=15,message="{Size}")
@Column(name = "first_name")
private String firstName;
@NotNull(message="{NotNull}")
@Size(min=2,max=15,message="{Size}")
@Column(name = "last_name")
private String lastName;

@MappedSuperclass
public class BaseClass {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}


and as I start spring , this is the stacktrace:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO 'VET'[*] VALUES (1, 'AAAA', 'BBBB')"; expected "identifier"; SQL statement:
INSERT INTO 'VET' VALUES (1, 'AAAA', 'BBBB') [42001-192]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.192.jar:1.4.192]
at org.h2.message.DbException.getSyntaxError(DbException.java:205) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3130) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.readTableOrView(Parser.java:5365) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.parseInsert(Parser.java:1053) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.parsePrepared(Parser.java:413) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.parse(Parser.java:317) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.parse(Parser.java:289) ~[h2-1.4.192.jar:1.4.192]
at org.h2.command.Parser.prepareCommand(Parser.java:254) ~[h2-1.4.192.jar:1.4.192]
at org.h2.engine.Session.prepareLocal(Session.java:560) ~[h2-1.4.192.jar:1.4.192]
at org.h2.engine.Session.prepareCommand(Session.java:501) ~[h2-1.4.192.jar:1.4.192]
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1202) ~[h2-1.4.192.jar:1.4.192]
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170) ~[h2-1.4.192.jar:1.4.192]
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) ~[h2-1.4.192.jar:1.4.192]
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473) ~[spring-jdbc-4.2.7.RELEASE.jar:4.2.7.RELEASE]
... 67 common frames omitted


Of course the name of the script is data.sql and its located in resources folder, its obviously reading the script accordingly to stack trace. I dont understand the part with identifier

////EDIT

ok so i tried something like this:

INSERT INTO VET('id', 'first_name', 'last_name') VALUES (1, 'AAAA', 'BBBB');


and thats the error

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO VET('id'[*], 'first_name', 'last_name') VALUES (1, 'AAAA', 'BBBB')"; expected "identifier"; SQL statement:
INSERT INTO VET('id', 'first_name', 'last_name') VALUES (1, 'AAAA', 'BBBB') [42001-192]


but when i go with:

INSERT INTO VET VALUES (1, 'AAAA', 'BBBB');


build is succesfull

so for educational purposes only why the first option gives error?

Jay Jay
Answer

You shouldn't have quotes around your column names or table names.

INSERT INTO VET(id, first_name, last_name) VALUES (1, 'AAAA', 'BBBB')