CODEWITHSUNDEEP
javaspringspring-booth2
jjangga
jjangga

Reputation: 622

H2 org.h2.jdbc.JdbcSQLException with correct DDL sql : error code = [42000-196]

While executing the ddl below, org.h2.jdbc.JdbcSQLException is thrown. Of course I tested it in isolated environment, without any other sql. What's somewhat more embarrasing is that the error message doesn't provide any hint except [42000-196]. I've already checked some h2-specific grammar this sql is related to on official website, but is there special grammar I missed?

sql

CREATE TABLE Product
(
  `id`          BIGINT           NOT NULL    AUTO_INCREMENT COMMENT 'id',
  `title`       VARCHAR(1000)    NULL        COMMENT 'title',
  `price`       INT              NULL        COMMENT 'price',
  `simpledesc`  VARCHAR(1000)    NULL        COMMENT 'simpledesc',
  `content`     TEXT             NULL        COMMENT 'content',
  `stock`       INT              NULL        COMMENT 'stock',
  `seq`         INT              NULL        COMMENT 'product 끼리의 순서',
  `categoryid`  INT              NULL,
  `timelog`     DATETIME         NOT NULL    DEFAULT now() COMMENT 'timelog',
  PRIMARY KEY (id)
);

Error messages on console

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "[*] CREATE TABLE Product ( id BIGINT NOT NULL COMMENT 'id', title VARCHAR(1000) NULL COMMENT 'title', price INT NULL COMMENT 'price', simpledesc VARCHAR(1000) NULL COMMENT 'simpledesc', content TEXT NULL COMMENT 'content', stock INT NULL COMMENT 'stock', seq INT NULL COMMENT 'product 끼리의 순서', categoryid INT NULL, timelog DATETIME NOT NULL DEFAULT now() COMMENT 'timelog', PRIMARY KEY (id) )"; SQL statement:
 CREATE TABLE Product ( id BIGINT NOT NULL COMMENT 'id', title VARCHAR(1000) NULL COMMENT 'title', price INT NULL COMMENT 'price', simpledesc VARCHAR(1000) NULL COMMENT 'simpledesc', content TEXT NULL COMMENT 'content', stock INT NULL COMMENT 'stock', seq INT NULL COMMENT 'product 끼리의 순서', categoryid INT NULL, timelog DATETIME NOT NULL DEFAULT now() COMMENT 'timelog', PRIMARY KEY (id) ) [42000-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.message.DbException.getSyntaxError(DbException.java:191) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.getSyntaxError(Parser.java:534) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parsePrepared(Parser.java:492) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:321) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.parse(Parser.java:297) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.command.Parser.prepareCommand(Parser.java:258) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176) ~[h2-1.4.196.jar:1.4.196]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) ~[h2-1.4.196.jar:1.4.196]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.8.jar:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:471) ~[spring-jdbc-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    ... 121 common frames omitted

Upvotes: 3

Views: 5234

Answers (2)

jjangga
jjangga

Reputation: 622

I got an answer from github issue as like below. I removed, and regenerated the schema sql file, and now it works without any sql change. The sql was automatically generated on ERD tool on cloud, as SaaS, and downloaded through the Internet. I guess the file was a little damaged somehow.

Thanks to @katzyn at github, and akash verma on stackoverflow here for their help :)

Your SQL statement incorrectly contains byte order mark (special Unicode character U+FEFF). This character may be used only as a first character in a text file written in some of Unicode encodings to distinguish between them. But this character should not be used in strings with SQL statements passed to JDBC methods.

Script tool provided with H2 reads such files correctly.

You are using some tool from Spring instead, so either this tool does not skip BOM mark correctly or your text file is damaged somehow (contains more than one BOM, for example).

Upvotes: 0

akash verma
akash verma

Reputation: 159

I think the error is in this line :

`timelog`     DATETIME         NOT NULL    DEFAULT now() COMMENT 'timelog',

Update this as and then check for any error comes or not :

 `timelog`     DATETIME         NOT NULL    COMMENT 'timelog',

--------UPDATED-------

Try this first without that function now(), i have tested this one

CREATE TABLE Product
(
  `id`          BIGINT           NOT NULL    AUTO_INCREMENT COMMENT 'id',
  `title`       VARCHAR(1000)    DEFAULT NULL        COMMENT 'title',
  `price`       INT              DEFAULT NULL COMMENT 'price',
  `simpledesc`  VARCHAR(1000)    DEFAULT NULL COMMENT 'simpledesc',
  `content`     TEXT             DEFAULT NULL COMMENT 'content',
  `stock`       INT              DEFAULT NULL COMMENT 'stock',
  `seq`         INT              DEFAULT NULL COMMENT 'product 끼리의 순서',
  `categoryid`  INT              DEFAULT NULL,
  `timelog`     DATETIME         NOT NULL     COMMENT 'timelog',
  PRIMARY KEY (id)
);

Upvotes: 1

Related Questions