2

I'm working in development mode with an H2 in memory database, but I'd like it to behave as much as possible like a mysql database (see http://www.h2database.com/html/features.html#compatibility)

this is my configuration in application.conf file:

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play;MODE=MYSQL;DB_CLOSE_DELAY=-1"

to test it I just run "play" and from play's console I issue "h2-browser" and in the url jdbc field I enter "jdbc:h2:mem:play;MODE=MYSQL;DB_CLOSE_DELAY=-1"

the following statements work ok in mysql:

CREATE TABLE `tmp` (
  `name` varchar(50) NOT NULL
);
insert into tmp (name) values ('slash: \\, simple quotes \', double quotes \" -');
select * from tmp;

but in the h2 console I get an error, and the only character I can escape is the single quotes, just by preceding it with another single quote. (Also tried entering 'SET MODE MySQL;')

Is there some way to have h2 behave like mysql from play's framework h2-browser? or is it just a limitation of h2?

opensas
  • 60,462
  • 79
  • 252
  • 386
  • seems like just using double single quotes works on both, mysql and h2... – opensas Sep 10 '12 at 03:30
  • 1
    The ANSI SQL standard defines that you need to use two single quotes within a string, so `' ... \' ... '` is not correct, you need to use `' ... '' ... '`. This is documented. – Thomas Mueller Sep 10 '12 at 04:58

1 Answers1

7

The link you provided, http://www.h2database.com/html/features.html#compatibility, documents the exact compatibility features H2 supports. Things that are not documented are not supported. In this case it seems the problem is the 'backslash' escaping within a String literal:

'slash: \\, simple quotes \', double quotes \" -'

The backslash is not an escape character for ANSI SQL; to escape a single quote you need to use two single quotes. The problem is this might not work for MySQL as the single backslash is still an escape character:

'slash: \, simple quotes '', double quotes " -'

For this problem, a solution is to use the ANSI mode for MySQL, another solution is to always use bind variables for String literals (PreparedStatement within JDBC).

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132