Parameter Binding or String Interpolation for Building SQL Plain Queries in Scala?

Question

def insertToTableSQLQuery(tableName: String, columnDefinitions: Seq[ColumnDefinition], rowData: ListBuffer[Any]): Future[Int] = db.run {
        val columns = columnDefinitions.map(_.name).mkString(", ")

        sqlu"insert into #$tableName(#$columns) values (#${rowData(0)}, #${rowData(1)}, #${rowData(2)}, #${rowData(3)}, #${rowData(4)});"
}

How to build the sql query such that rowData values are binded or interpolated dynamically in Slick using Scala?

def insertToTableSQLQuery(tableName: String, columnDefinitions: Seq[ColumnDefinition], rowData: ListBuffer[Any]): Future[Int] = db.run {
            val columnNames = columnDefinitions.map(_.name).mkString(", ")
            val placeholders = List.fill(rowData.length)("?").mkString(", ")

            sqlu"insert into #$tableName(#$columnNames) values (#${rowData.map(_ => "?").mkString(", ")});".bind(rowData: _*)
}

I have tried to use parameter binding but this is throwing an error for me.

THE ERROR

value bind is not a member of slick.sql.SqlAction[Int,slick.dbio.NoStream,slick.dbio.Effect]    
[error] possible cause: maybe a semicolon is missing before `value bind`?
[error]                 .bind(rowData: _*)
[error]                  

Answer 1

There are two ways in slick of inject values at runtime in a plain sql.

• slick - string interpolation

Plain SQL queries in Slick are built via string interpolation using the sql, sqlu and tsql interpolators. They are available through the standard api._ import from a Slick profile:

import slick.jdbc.H2Profile.api._

Any variable or expression injected into a query gets turned into a bind variable in the resulting query string. It is not inserted directly into a query string, so there is no danger of SQL injection attacks. You can see this used in here:

def insert(c: Coffee): DBIO[Int] =
  sqlu"insert into coffees values (${c.name}, ${c.supID}, ${c.price}, ${c.sales}, ${c.total})"

The SQL statement produced by this method is always the same:

insert into coffees values (?, ?, ?, ?, ?)

• slick - Splicing Literal Values

While most parameters should be inserted into SQL statements as bind variables, sometimes you need to splice literal values directly into the statement, for example to abstract over table names or to run dynamically generated SQL code. You can use #$ instead of $ in all interpolators for this purpose, as shown in the following piece of code:

val table = "coffees"
sql"select * from #$table where name = $name".as[Coffee].headOption

So, for literal values such as tables or columns you will need to use #$ and $ for variable values.

In your case the code should be something like

def insertToTableSQLQuery(
  tableName: String,
  columnDefinitions: Seq[ColumnDefinition],
  rowData: ListBuffer[Any] // this will produce a compilation error
): Future[Int] = db.run {
  val columns = columnDefinitions.map(_.name).mkString(", ")

  sqlu"insert into #$tableName(#$columns) values (${rowData(0)}, ${rowData(1)}, ${rowData(2)}, ${rowData(3)}, ${rowData(4)});"
}

This is not enough to solve the problem because when the code is compiled, you would get the following error

could not find implicit value for parameter e: slick.jdbc.SetParameter[Any]
    sqlu"insert into #$tableName(#$columns) values (${rowData(0)}, ${rowData(1)}, ${rowData(2)}, ${rowData(3)}, ${rowData(4)});"

which means, slick doesn't know how to bind values of type Any. Slick provides implicit SetParameter for common values such as Int, Boolean, String, Doubel, BigDecimal, etc.

One way of fixing that, is to create your own implicit SetParameter[Any] = ???. I suggest to avoid the use of Any as much as you can in any scala project unless you really need to.

The other option is to have a case class or a Tuple instead of a List[Any]. The case class is easier to use because you can set a name to each field mean while when you want to access the tuple you need to know the position of the value. The case class is also easier to refactor.