Set IGNORECASE and other options for H2 in a Play!

Question

I've setup my Play! (with Slick) application to use H2 when running tests, and it has worked great so far. I'm getting an error now, because of a plain-SQL query that uses lower-case column and table names, and H2 complains that the TABLE (all uppercase) could not be found.

Now I need to set a few options, IGNORECASE for sure, and possibly the MODE.

When I setup my database for tests, I use

def fakeAppWithMemoryDatabase = FakeApplication(additionalConfiguration = inMemoryDatabase())

For development, I use PSQL, so in my application.conf file, I have:

slick.db.driver=scala.slick.driver.H2Driver

db.default.driver=org.postgresql.Driver
db.default.url="jdbc:postgresql://localhost:5432/mydb"

From the documentation, I see I can pass settings to the db.default.url string, like

db.default.url="jdbc:h2:mem:play;MODE=PostgreSQL;"

but my default.url setting is for my Postgres DB. Is there a way to pass in MODE and IGNORECASE settings to H2 in this scenario?

I have tried to append SET IGNORECASE TRUE; to my SQL query but I still receive the same error.

Answer 1

You need to add DATABASE_TO_UPPER=false to your Url. It will let you leave the "" out. With this I will continue to use H2 otherwise I wouldn't. Can't tell why this option is true by default...

Answer 2

Ok, based off this merge into the Play! code, I figured it out:

FakeApplication(additionalConfiguration = inMemoryDatabase(options = Map("MODE"->"PostgreSQL","IGNORECASE"->"TRUE")))

Unfortunately, IGNORECASE wasn't what I thought it was, my tables and columns still need to be capitalized in order for H2 to properly use my plain-SQL queries.