4

Can I use in-clauses with ScalikeJDBC's SQL Interpolation? e.g.

val ids = Set(1,2,3,5)
sql"""update foo set bar=${bar} where id in ${ids}""".update().apply()

This fails because ids is not interpolated.

sql"""update foo set bar=${bar} where id in (${ids.mkString(",")})""".update().apply()

This also fails because the expression is intepreted as a String, not a list of numbers. e.g. ... where id in ('1,2,3,5')

Synesso
  • 37,610
  • 35
  • 136
  • 207

1 Answers1

6

I've not figured out your issue, but interpolating Set value should work. 

libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"       % "2.2.6",
  "com.h2database"  %  "h2"                % "1.4.187",
  "ch.qos.logback"  %  "logback-classic"   % "1.1.3"
)

like this:

scala> import scalikejdbc._
import scalikejdbc._

scala> val ids = Set(1,2,3,5)
ids: scala.collection.immutable.Set[Int] = Set(1, 2, 3, 5)

scala> val s = sql"""update foo set bar=1 where id in (${ids})"""
s: scalikejdbc.SQL[Nothing,scalikejdbc.NoExtractor] = scalikejdbc.SQLToTraversableImpl@633229c7

scala> s.statement
res1: String = update foo set bar=1 where id in (?, ?, ?, ?)

scala> s.parameters
res2: Seq[Any] = List(1, 2, 3, 5)
Kazuhiro Sera
  • 1,822
  • 12
  • 15
  • 1
    You are quite correct. I don't know what I was doing wrong before. I've rolled back to what I thought I tried initially and it's working now. ありがとう – Synesso May 04 '15 at 11:52
  • Not sure if was the cause the OP's problem, but if you accidentally use Java collections, you silently get aberrant interpolation behaviour: `scala> sql"""update foo set bar=1 where id in (${Set(5, 1, 2, 3, 4).asJava})""".statement` gives `res2: String = update foo set bar=1 where id in (?)`. This caused some problems for me; I might file a bug against scalikejdbc. – Andy MacKinlay Dec 15 '15 at 04:33
  • I filed a [ScalikeJDBC bug](https://github.com/scalikejdbc/scalikejdbc/issues/469). – Andy MacKinlay Dec 15 '15 at 04:47