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

Question

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.

score 23 · Accepted Answer · edited May 23 '17 at 12:18

23

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]

edited May 23 '17 at 12:18

Community

1
1

answered Dec 06 '14 at 13:26

Dimitri

1,786
14
22

Is the interpolation in case 2 sanitized? If not, is there a way to sanitize strings that we are going to interpolate using `#$`? – ciuncan Feb 25 '15 at 22:29
1

@ciuncan No it's not sanitized. I can't answer your second question. It can be anything: a column name, a table name, a schema, a query, part of a query... – Dimitri Feb 25 '15 at 22:47
@Dimitri Ah, you are right. In my case it is column names, and string literals to generate constant column values. Is it also dependent on database, or jdbc just handles that? If that's the case I am working with Postgresql. (Edit: I imaged jdbc could have some way to sanitize such strings for interpolation) – ciuncan Feb 25 '15 at 22:49
@ciuncan Sorry, I can't tell if such function is available. – Dimitri Feb 26 '15 at 09:11
1

Ok thank you. For anyone who needs such a thing, I found quoteIdentifier function in JdbcDriver driver trait, and I think it should be useful with escaping column and table names. I will look further and report here if I find anything relevant. – ciuncan Feb 26 '15 at 10:01
Answering your question "Why not simply have this?" someone may want to have sql query in a file, after reading it to String I just want to run the query. Working on that atm, but its hard to do that, case sqlu produces "?" from my query interpolated. – Łukasz Gawron Mar 12 '19 at 14:34
`PSQLException: ERROR: syntax error at or near "#"` – techkuz Jul 15 '19 at 05:00

Łukasz Gawron · Answer 2 · 2019-03-23T20:27:18.560

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

score 0 · Answer 3 · answered Dec 16 '21 at 07:09

0

The way to achieve case 2 would be this :

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

answered Dec 16 '21 at 07:09

Gayathri

95
1
1
11

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

3 Answers3

Case 1

Case 2