Slick flatMap does not execute all queries as a group

Question

I fount that Scala Slick can not execute multiple queries as plain sql.

For example:

val query = sql"""
  SET @referenceTime = '12:00:00';
  SELECT * FROM table WHERE dateTime <= @referenceTime;
""".as[ClassResult]

dbConfig.db.run(query)

In this string are 2 queries, and Slick return an error as :

You have an error in your SQL syntax; check the manual .... to use near 'SELECT * FROM

From this, I understand that all queries before "SELECT" (maybe the last query) are ignored. So, I found a solution using flatMap, but is not the perfect.

val timeQuery = sql"""SET @referenceTime = '12:00:00';""".as[String]
val dataQuery = sql"""SELECT * FROM table WHERE dateTime <= @referenceTime;""".as[ClassResult]

val composedAction = for {
  timeRes <- timeQuery,
  dataRes <- dataQuery
} yield dataRes

dbConfig.db.run(composedAction)

This run and in 99% of cases return the result (a list of items). But, sometimes the list is empty, even if I'm sure that must return some data). So, I think that composedAction doesn't wait and execute both queries as a group every time. How I can do this, because I need in second query the result of the first (is used as a parameter in second)

edit: another solution is to wait for result of the first query and use it as parameter in second. But it is a good solution/practice? Using sync code.

   val timeQuery = sql"""SELECT '12:00:00';""".as[String]    
   var defaultTime: String = ""
   val tempResult = dbConfig.db.run(timeParameterQuery.head).map(res => defaultTime = res)
   Await.result(tempResult, Duration.Inf)

   val dataQuery = sql"""SELECT * FROM table WHERE dateTime <= $defaultTime;""".as[ClassResult]    
   dbConfig.db.run(dataQuery)

Ossip Ossip · Accepted Answer · 2019-11-07T16:31:41

I guess using SQL variables in slick is problematic, could be multiple db contexts. You can chain the queries with the 'map' rather than awaiting the result. Try something like this (untested)

   val timeQuery = sql"""SELECT '12:00:00';""".as[String]    
   dbConfig.db.run(timeQuery.head).flatMap{res => 
      dbConfig.db.run(sql"""SELECT * FROM table WHERE dateTime <= $res;""".as[ClassResult])
   }

Slick flatMap does not execute all queries as a group

1 Answers