CODEWITHSUNDEEP
mysqlplayframeworkplayframework-2.0h2
opensas
opensas

Reputation: 63395

play framework 2: h2 in memory database mysql compatibility mode: escaping characters

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?

Upvotes: 2

Views: 4419

Answers (1)

Thomas Mueller
Thomas Mueller

Reputation: 50087

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).

Upvotes: 7

Related Questions