0

I have statements such as below

  def create(company: Company): Future[Company] = {
    dbConfig.db.run(companies.filter(_.name === company.name).result.headOption)
}

and they fail with exceptions such as this

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement

I have tried to escape the single quote but wasn't successful.

dbConfig.db.run(companies.filter(_.name === company.name.replace("'", "''")).result.headOption)

When I tried to insert a record such as this:

val company = doSync(companies.create(Company(0, "C's company")))

The exception I've gotten is:

Syntax error in SQL statement "select `id`, `name` from `company` where `name` = 'C\'s company[*]'"; SQL statement:
select `id`, `name` from `company` where `name` = 'C\'s company' [42000-200]
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select `id`, `name` from `company` where `name` = 'C\'s company[*]'"; SQL statement:
select `id`, `name` from `company` where `name` = 'C\'s company' [42000-200]

Please note that I am using H2 in Mysql mode to run my tests.

Ivan Kurchenko
  • 4,043
  • 1
  • 11
  • 28
Ducaz035
  • 3,054
  • 2
  • 25
  • 45

1 Answers1

1

That error suggests to me that you have the wrong Slick profile imported. I'm not familiar with H2 in Mysql mode. Perhaps you need to use the MySQL profile? (Or the H2 one if you're using MySQL)

Richard Dallaway
  • 4,250
  • 1
  • 28
  • 39
  • I'm already using the mysqlProfile in the code, I've double checked that. – Ducaz035 Mar 11 '21 at 09:53
  • I'd maybe try the H2 profile to see if that resolves the SQL syntax issues (with unmodified queries, that is). I don't believe you should need to change anything about the Slick query to make it run against a supported database. – Richard Dallaway Mar 11 '21 at 10:14
  • It could try that but then it'd mess up with my production DB (MySql). That's why I run H2 in MySQL mode locally. – Ducaz035 Mar 11 '21 at 12:36
  • You can abstract out the profile, and mix in the right one locally vs. production to solve that difference. There's a section on that in Essential Slick: https://books.underscore.io/essential-slick/essential-slick-3.html#abstracting-over-databases – Richard Dallaway Mar 11 '21 at 14:57
  • Thanks for the reference, I struggle to understand how it can work in my setup: I have repository classes that do operations on the DB level, does it mean I need to use that trait in those classes? – Ducaz035 Mar 11 '21 at 16:38
  • I made it work eventually and it solved the problem (importing the JbdcProfile and letting the runtime import the correct profile eventually), but I still don't get why it solves the issue :S – Ducaz035 Mar 11 '21 at 22:04
  • It solves the issue because: different databases have differences in syntax and capabilities. The Slick profile defines how Slick generates SQL. So you have to import a correct matching profile for the database otherwise you'll get SQL issues. To use the same code on different database products, you need to abstract over the profile (e.g., via that example in Essential Slick). Great you have it working! – Richard Dallaway Mar 12 '21 at 11:23
  • Thanks for the pointer indeed, but still I don't get it :) Because I've always imported MysqlProfile and yet it failed in my production instance which is Mysql, I didn't import anything else than Mysql – Ducaz035 Mar 12 '21 at 19:09