Plain SQL inSetBind

[Michael Pollmeier]

Is there an alternative for inSetBind for Plain SQL?

This question on SO suggests to use Seq.mkString(",") when creating the query.
http://stackoverflow.com/questions/19285971/how-to-do-batch-insert-use-plain-sql-in-scala-slick

That's ok for small lists of integers, but is not optimal for
- large lists (inefficient)
- lists of Strings: risky for sql injection and very hard to get the formatting right (values have to be surrounded with double quotes, if they contain double quotes these have to be escaped, ...)
Also not using query params means the db can't cache the query plan.

Thoughts?

[med...@saltation.de]

Why don't you use sql parameters? You can pass them to the execute() or list() function of your query. All you need is a SetParameter implementation to convert your Seq into PositionedParameters.

example:

val params = Seq("foo", "bar", "baz")

implicit object SetSqlParamList extends SetParameter[Seq[String]] {

  override def apply(values: Seq[String], pp: PositionedParameters): Unit = values.foreach(v => pp.setString(v))

}

StaticQuery.query[List[String], User](s"SELECT * FROM foo WHERE bar IN (${params.map(_ => "?").mkString(",")})")..list(params)

[Michael Pollmeier]

That's what I was looking for, thank you!

For completeness, here's the imports:
import slick.jdbc.SetParameter
import slick.jdbc.PositionedParameters

And one little caveat: if your params are a Set, then params.map(_ =>
"?") will become Set("?"), no matter how many entries params had ;)
So simply make it a seq first:
params.toSeq.map(_ => "?")

Before you go and implement your own SetParameter, there are a bunch
already available and here:
import slick.jdbc.SetParameter._