CODEWITHSUNDEEP
scalaslickslick-3.0
Janani
Janani

Reputation: 35

Insert query is working in psql but failing in application - Play with Slick(postgres db)/Scala

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]   org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
[info]   Position: 1
[info]   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
[info]   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
[info]   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
[info]   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
[info]   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
[info]   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
[info]   at org.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.

Upvotes: 0

Views: 41

Answers (1)

Janani
Janani

Reputation: 35

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

sql"#$selectCommand"

Upvotes: 1

Related Questions