I'm exploring with Slick - postgres db. I have the tables created from psql and, I am trying to do CRUD/select operations from the application. Below is the stack trace I see. I am using direct sql instead of writing the case classes and extending TableQuery. I see it's mentioned in documentation that this too is supposed to work. Am I missing something here?

sql - select id FROM users where id = '1';
SQL Exception occurred: ERROR: syntax error at or near "$1"
  Position: 1
[info] - should describe users *** FAILED ***
[info]   .postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
[info]   Position: 1
[info]   at .postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
[info]   at .postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
[info]   at .postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
[info]   at .postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
[info]   at .postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
[info]   at .postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
[info]   at .postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
[info]   at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
[info]   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
[info]   at slick.jdbc.LoggingPreparedStatement.$anonfun$execute$5(LoggingStatement.scala:185)
[info]   ...

Below is the logic written to run the query.

  def select(tableName: String, condition: String) = {
    val selectCommand = s"select id FROM $tableName $condition;"
    println(s"sql - $selectCommand")
    val query = sql"$selectCommand".as[String]
    try {
      val result = db.run(query)
      val users = Await.result(result, 10.seconds)
      println(users)
      Future(users)
    } catch {
      case ex: SQLException =>
        println(s"SQL Exception occurred: ${ex.getMessage}")
        throw ex
      case ex: Exception =>
        println(s"An unexpected error occurred: ${ex.getMessage}")
        throw ex
    }
  }

Below is the same query from psql and, it is running fine.

banktest=# select id FROM users where id = '1';
 id
----
 1
(1 row)

Could you help to point the issue here.

I'm exploring with Slick - postgres db. I have the tables created from psql and, I am trying to do CRUD/select operations from the application. Below is the stack trace I see. I am using direct sql instead of writing the case classes and extending TableQuery. I see it's mentioned in documentation that this too is supposed to work. Am I missing something here?

sql - select id FROM users where id = '1';
SQL Exception occurred: ERROR: syntax error at or near "$1"
  Position: 1
[info] - should describe users *** FAILED ***
[info]   .postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
[info]   Position: 1
[info]   at .postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
[info]   at .postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
[info]   at .postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
[info]   at .postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
[info]   at .postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
[info]   at .postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
[info]   at .postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
[info]   at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
[info]   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
[info]   at slick.jdbc.LoggingPreparedStatement.$anonfun$execute$5(LoggingStatement.scala:185)
[info]   ...

Below is the logic written to run the query.

  def select(tableName: String, condition: String) = {
    val selectCommand = s"select id FROM $tableName $condition;"
    println(s"sql - $selectCommand")
    val query = sql"$selectCommand".as[String]
    try {
      val result = db.run(query)
      val users = Await.result(result, 10.seconds)
      println(users)
      Future(users)
    } catch {
      case ex: SQLException =>
        println(s"SQL Exception occurred: ${ex.getMessage}")
        throw ex
      case ex: Exception =>
        println(s"An unexpected error occurred: ${ex.getMessage}")
        throw ex
    }
  }

Below is the same query from psql and, it is running fine.

banktest=# select id FROM users where id = '1';
 id
----
 1
(1 row)

Could you help to point the issue here.

• scala
• slick
• slick-3.0

• 1 Please don't post images of text. Edit your post and paste the code, error message and else using the right format. Check markdown help. – Gastón Schabas Commented Jan 30 at 3:34
• Hello @GastónSchabas, Changed now. Thank you for the suggestion. – Janani Commented Jan 30 at 4:00
• 2 My guess is that the problem could be the string interpolation. Have you tried to not build the query in dynamic way using s and just using the sql string interpolator? – Gastón Schabas Commented Jan 30 at 4:01
• That's a great guess. It's working like a charm. But I want this query to be built runtime based on the values passed, I was following this documentation - scala-slick./doc/devel/… Any suggestions here? – Janani Commented Jan 30 at 4:21
• 2 The section Splicing Literal Values and Concatenating SQL statements could help you to build dynamic queries. Bind variables and literal values are not the same. – Gastón Schabas Commented Jan 30 at 4:33

1 Answer 1

As mentioned by @Gaston, https://scala-slick./doc/devel/sql.html#splicing-literal-values, I need to use #$ for string interpolation.

sql"#$selectCommand"