CODEWITHSUNDEEP
scalaslick
John
John

Reputation: 443

How to write dynamic SQL queries with sql""" interpolation in slick

I am new to Scala and Slick and trying to write a plain SQL queries with Slick interpolation.

Case 1: I want the generalize the code so that queries are stored as constants.

for instance:

val SQL_ALL_TABLE_METADATA: String = """SELECT DISTINCT table_name, column_name, data_type
                                            FROM information_schema.columns
                                                    WHERE table_schema = 'apollo' OR table_schema = 'dpa' ORDER BY table_name""";

And create plain query from constant something like

var plainQuery = sql"""$SQL_ALL_TABLE_METADATA""".as[List[String]]

Case 2: Replace a part of the query

For instance: get information on column f_name from table 'table1'

var column= "f_name"
var plainQuery = sql"""SELECT $column FROM table1""".as[String]

When I try the above cases it is not working as it looks like query is binding statically on compile time.

Please note that as of now I want to use plain SQL and use advanced Slick API in future.

Upvotes: 18

Views: 12573

Answers (3)

Gayathri
Gayathri

Reputation: 165

The way to achieve case 2 would be this :

var plainQuery = sql"""SELECT ${column.unsafesql} FROM table1""".as[String]

Upvotes: 0

Łukasz Gawron
Łukasz Gawron

Reputation: 917

I'm posting answer which is not using interpolation, maybe someone find it helpful.

I solved it that way in tests, executeUpdate method return actual result of query. I was able to have dynamic query from String variable.

dbConnection = JdbcBackend.Database.forURL(url = dbConfig.getString("db.url"), driver = "org.h2.Driver")
val createTablesSqlQuery:String = //read_from_file
dbConnection.createSession().createStatement().executeUpdate(createTablesSqlQuery)

Helpful topic was this one: https://groups.google.com/forum/#!topic/scalaquery/OxAgtcCPMyg

Upvotes: 1

Dimitri
Dimitri

Reputation: 1786

Case 1

Why not simply have this?

val SQL_ALL_TABLE_METADATA: StaticQuery = sql"""SELECT DISTINCT table_name, column_name, data_type
                                            FROM information_schema.columns
                                                    WHERE table_schema = 'apollo' OR table_schema = 'dpa' ORDER BY table_name"""

var plainQuery = SQL_ALL_TABLE_METADATA.as[List[String]]

Case 2

Use #$ instead of $

var column= "f_name"
var plainQuery = sql"""SELECT #$column FROM table1""".as[String]

Upvotes: 23

Related Questions