Work with utf8mb4 charset in H2

[Vitalii Dmytruk]

Hi,

I'm trying to use H2 in-memory database. A few tables need to use utf8mb4 charset for its fields. Looks like it is a problem for H2. The sql like this

CREATE TABLE IF NOT EXISTS `partners` (
  `partner_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


throws an exception


org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE IF NOT EXISTS ""PARTNERS"" (
  ""PARTNER_ID"" INT(11) NOT NULL AUTO_INCREMENT,
  ""CODE"" VARCHAR(32) CHARACTER[*] SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL,
  PRIMARY KEY (""PARTNER_ID"")
) ENGINE=INNODB DEFAULT CHARSET=UTF8 "; expected "FOR, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE IF NOT EXISTS `partners` (
  `partner_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`partner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 [42001-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.getSyntaxError(DbException.java:204)
    at org.h2.command.Parser.getSyntaxError(Parser.java:524)
    at org.h2.command.Parser.read(Parser.java:3079)
    at org.h2.command.Parser.readIfMore(Parser.java:856)
    at org.h2.command.Parser.parseCreateTable(Parser.java:5789)
    at org.h2.command.Parser.parseCreate(Parser.java:4122)
    at org.h2.command.Parser.parsePrepared(Parser.java:351)
    at org.h2.command.Parser.parse(Parser.java:306)
    at org.h2.command.Parser.parse(Parser.java:282)
    at org.h2.command.Parser.prepareCommand(Parser.java:243)
    at org.h2.engine.Session.prepareLocal(Session.java:442)
    at org.h2.engine.Session.prepareCommand(Session.java:384)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)
    at org.h2.tools.RunScript.process(RunScript.java:261)
    at org.h2.tools.RunScript.process(RunScript.java:191)
    at org.h2.tools.RunScript.process(RunScript.java:329)
    at org.h2.tools.RunScript.execute(RunScript.java:304)
    at com.cogolabs.edc.webapp.dao.SessionDaoTest.setUpClass(SessionDaoTest.java:39)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:175)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:211)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:67)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)


Is it possible to use utf8mb4 character set in H2 and, if so, how to enable it?

[Noel Grandin]

Sorry, we don't support setting the charset on columns.

We do support setting the collation
http://h2database.com/html/grammar.html#set_collation
but only on a database-wide basis.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> h2-database...@googlegroups.com <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.