Printing interpolated SQL query in Slick

Question

I am trying to print an interpolated Slick2 SQL statement for debugging and all I get is the one with question marks e.g.

def query(name: String) = sql"SELECT MAX(age) FROM users WHERE name = $name".as[Int]
println(query("Bob").getStatement)   

The above prints this:

SELECT MAX(age) FROM users WHERE name = ?

How can I make it print this:

SELECT MAX(age) FROM users WHERE name = 'Bob'

Note: This questions is NOT a duplicate of this

Student

The problem that you are trying to solve is not strictly slick related: slick creates prepared statements at the JDBC level and those are printed in that way. I suspect that you should look into how to print JDBC prepared statements rather than focusing your attention on slick. I am not sure that there is a way to do that in JDBC though. – Jilt

Answer 1

You probably want to add the following to your application.conf

logger.scala.slick.session=DEBUG

This should show compiled query strings in the console.

Entity

I have a Play app. I added it to both my application.conf and I added it to my logger.xml: <logger name="scala.slick.session" level="INFO"/> But, I don't see it... – Student

What happens when you change to log level to "DEBUG" in logger.xml? – Entity

Same. I downgraded to DEBUG too. Nothing. I am on Slick 2.0 + Play 2.2 – Student

Can you edit to show what your application.conf and logger.xml looks like? I'm sure you're just one param off from getting the logs. Meanwhile, try <logger name="slick.jdbc" level="DEBUG"/> – Entity

Answer 2

From the slick documentation: "You can use #$ instead of $ to get the literal value inserted directly into the query".

//note the '#'
def query(name : String) = sql"SELECT MAX(age) FROM users WHERE name = '#$name'".as[Int]

Tuna

No, I don't want #$ as that is meant to splice in parts of the SQL statement itself (e.g. table names etc). This would produce incorrect SQL since it would do where name=Bob instead of where name='Bob'. I want $ to bind variables... – Student

This also won't work for non primitives e.g: def query(birthdate : LocalDate) = sql"SELECT MAX(age) FROM users WHERE birthday > $birthdate".as[Int] Here if I have the correct implicit in scope, it will convert into the correct SQL bind variable. If I use #$, it would just put the .toString of LocalDate which I may not want. – Student

I added the single quotes into the query to produce proper sql. I don't know what you would want the println to show for "non-primitives" since those are the only types of values that can go into an sql query... – Shults

If you want non-default behavior then why not construct the string first and then apply the sql function to the constructed string? Why have sql act as your regular expression evaluator? – Shults

Not sure what you mean by "sql act as your regular expression evaluator". Slick let's you bind custom classes using the type mapper which can only be correctly binded using $ interpolator and not the #$ one. – Student