12

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

Community
  • 1
  • 1
pathikrit
  • 32,469
  • 37
  • 142
  • 221
  • 3
    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. – Aldo Stracquadanio Oct 26 '15 at 12:20

2 Answers2

5

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.

airudah
  • 1,169
  • 12
  • 19
  • 1
    I have a Play app. I added it to both my application.conf and I added it to my logger.xml: `` But, I don't see it... – pathikrit Oct 29 '15 at 18:42
  • What happens when you change to log level to "DEBUG" in logger.xml? – airudah Oct 30 '15 at 11:18
  • Same. I downgraded to DEBUG too. Nothing. I am on Slick 2.0 + Play 2.2 – pathikrit Oct 30 '15 at 17:07
  • 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 `` – airudah Nov 02 '15 at 11:38
3

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]
Ramón J Romero y Vigil
  • 17,373
  • 7
  • 77
  • 125
  • 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... – pathikrit Oct 23 '15 at 17:24
  • 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. – pathikrit Oct 23 '15 at 17:27
  • 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... – Ramón J Romero y Vigil Oct 23 '15 at 17:28
  • 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? – Ramón J Romero y Vigil Oct 23 '15 at 17:29
  • 1
    Not sure what you mean by "sql act as your regular expression evaluator". Slick let's you bind custom classes using the [type mapper](http://stackoverflow.com/questions/14818827/customer-type-mapper-for-slick-sql) which can only be correctly binded using `$` interpolator and not the `#$` one. – pathikrit Oct 23 '15 at 17:31