Query in Quill crashing at runtime on syntax error

Question

Relevant snippets:

case class Video(
  id: String,
  title: String,
  url: String,
  pictureUrl: String,
  publishedAt: Date,
  channel: String,
  duration: Option[String],
  createdOn: Date
)

"Connecting" to DB is working, selecting from video table returns expected results. I have a problem with saving, this simple method:

  def saveToCache(item: Video): Unit = {
    logger.trace(item)
    import ctx._
    val x = quote {
      val lItem = lift(item)
      val fromDb = query[Video].filter(_.id == lItem.id)
      if (fromDb.isEmpty) query[Video].insert(lItem)
      else fromDb.update(lItem)
    }
    logger.trace(x.ast)
    run(x)
  }

crashes at runtime with (shortened):

11:14:06.640 [scala-execution-context-global-84] ERROR io.udash.rpc.AtmosphereService - RPC request handling failed
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "CASE": syntax error)
        at org.sqlite.core.DB.newSQLException(DB.java:909) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.newSQLException(DB.java:921) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.throwex(DB.java:886) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.NativeDB.prepare_utf8(Native Method) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.NativeDB.prepare(NativeDB.java:127) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.prepare(DB.java:227) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235) ~[sqlite-jdbc-3.18.0.jar:na]
        at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:317) ~[HikariCP-2.7.2.jar:na]
        at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-2.7.2.jar:na]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1(JdbcContext.scala:98) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1$adapted(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$withConnection$1(JdbcContext.scala:46) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at scala.Option.getOrElse(Option.scala:121) ~[scala-library-2.12.2.jar:1.0.0-M1]
        at io.getquill.context.jdbc.JdbcContext.withConnection(JdbcContext.scala:44) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.executeAction(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]

Relevant output from macro during compilation:

CASE WHEN NOT EXISTS (SELECT x3.* FROM video x3 WHERE x3.id = ?) THEN INSERT INTO video (id,title,url,picture_url,published_at,channel,duration,created_on) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ELSE UPDATE video SET id = ?, title = ?, url = ?, picture_url = ?, published_at = ?, channel = ?, duration = ?, created_on = ? WHERE id = ? END

I wanted to enable logging of queries, but I have no clue how to do it (Quill docs say just something about SLF4J which is useless information for me - I don't see any logs and I don't know what to search for in SLF4J docs).

So far I am quite disappointed by Quill - first time it generates invalid query for ordering when using default ordering type and now this.

I fixed errors in my answer and added info about logging. – mixel Nov 25 '17 at 12:03 — mixel, Nov 25 '17 at 12:03

mixel · Accepted Answer · 2017-11-25T12:03:11.457

You should rewrite you query as:

import ctx._
def saveToCache(item: Video): Unit = {
  if (ctx.run(query[Video].filter(_.id == lift(item.id)).isEmpty))
    ctx.run(query[Video].insert(lift(item)))
  else ctx.run(query[Video].filter(_.id == lift(item.id)).update(lift(item)))
  ()
}

because quote must contain expression that will be compiled to a single query. This code will compile to three queries.

But better use optimized version:

import ctx._
def saveToCacheOptimized(item: Video): Unit = {
  if (ctx.run(query[Video].filter(_.id == lift(item.id)).update(lift(item))) == 0)
    ctx.run(query[Video].insert(lift(item)))
  ()
}

which compiles to two queries.

Quill used SLF4J for logging, you should read this project documentation. To enable logging you should add some logging backend, for example, logback:

libraryDependencies ++= Seq(
  "ch.qos.logback" % "logback-classic" % "1.2.3"
)

It should be enough for just getting queries logged.

Thank you, it's working :). I was probably expecting too much from Quill, perhaps because I worked a bit with ORMs few years back. But Quill seems to be much closer to the DB layer and isn't as fool-proof as I hoped it would be (rule "if it compiles it's correct" clearly doesn't hold here, even with correct types it might be failing at runtime). — monnef, Nov 25 '17 at 13:48
Actually it seems to be a bug. I reported [it](https://github.com/getquill/quill/issues/974). — mixel, Nov 26 '17 at 12:15

Query in Quill crashing at runtime on syntax error

1 Answers1