How to send plain SQL queries (and retrieve results) using scala slick 3

Question

I am trying to make a class that has methods that can send and get data to an SQLite db using plain sql queries. This unfortunately does not work. I do not want to use the withSession implicit parts.

import slick.driver.SQLiteDriver.api._
import slick.lifted.TableQuery
import slick.jdbc.JdbcBackend.Database;
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent._
import ExecutionContext.Implicits.global

    class DBops {
  val db = Database.forURL("jdbc:sqlite:S:/testing/testdb.sql",driver = "org.sqlite.JDBC")

def getData(TableName: String):Future[(Int,Double,String)]={
    db.run(sql"""select * from $TableName """.as[(Int,Double,String)])

}

}

The following error is thrown:

type mismatch; found : slick.profile.SqlStreamingAction[Vector[(Int, Double, String)],(Int, Double, String),slick.dbio.Effect] required: slick.dbio.DBIOAction[(Int, Double, String),slick.dbio.NoStream,Nothing] DBops.scala

Answer 1

You can use sql"..." with any String content using #$tableName:

db.run(sql"SELECT * FROM #$tableName".as[(Int, Double, String)])

Please remember: don't ever take the tableName as a user input - otherwise, there is a great risk for SQL injection. The normal $value solves these problems for you.

Read Slick manual (http://slick.typesafe.com/doc/3.0.0/sql.html#splicing-literal-values)

... sometimes you need to splice literal values directly into the statement, for example to abstract over table names ... You can use #$ instead of $ in all interpolators for this purpose ...