Search code examples
scalaplayframeworkh2anorm

Scala Play Anorm: Placeholder not replaced

I've just started using Anorm and have stumbled across a weird behavior. When I try to do the following:

val userTableName: String = "User"
SQL("INSERT INTO {userTableName} (email, forename, surname, refreshToken) VALUES ({email}, {forename}, {surname}, {refreshToken})")
        .on("userTableName" -> userTableName,
            "email"->email,
            "forename"->forename,
            "surname"->surname,
            "refreshToken"->refreshToken).executeInsert()

I the following error:

play.api.Application$$anon$1: Execution exception[[JdbcSQLException: Syntax error in SQL statement "INSERT INTO ?[*] (EMAIL, FORENAME, SURNAME, REFRESHTOKEN) VALUES (?, ?, ?, ?) "; expected "identifier"; SQL statement:
INSERT INTO ? (email, forename, surname, refreshToken) VALUES (?, ?, ?, ?) [42001-175]]]
    at play.api.Application$class.handleError(Application.scala:296) ~[play_2.11-2.3.8.jar:2.3.8]
    at play.api.DefaultApplication.handleError(Application.scala:402) [play_2.11-2.3.8.jar:2.3.8]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:205) [play_2.11-2.3.8.jar:2.3.8]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:202) [play_2.11-2.3.8.jar:2.3.8]
    at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36) [scala-library-2.11.1.jar:na]
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "INSERT INTO ?[*] (EMAIL, FORENAME, SURNAME, REFRESHTOKEN) VALUES (?, ?, ?, ?) "; expected "identifier"; SQL statement:
INSERT INTO ? (email, forename, surname, refreshToken) VALUES (?, ?, ?, ?) [42001-175]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) ~[h2-1.3.175.jar:1.3.175]
    at org.h2.message.DbException.getSyntaxError(DbException.java:197) ~[h2-1.3.175.jar:1.3.175]
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:2926) ~[h2-1.3.175.jar:1.3.175]
    at org.h2.command.Parser.readTableOrView(Parser.java:5049) ~[h2-1.3.175.jar:1.3.175]
    at org.h2.command.Parser.parseInsert(Parser.java:995) ~[h2-1.3.175.jar:1.3.175]

If I hardcode the table name

SQL("INSERT INTO User (email, forename, surname, refreshToken) VALUES ({email}, {forename}, {surname}, {refreshToken})")
        .on("email"->email,
            "forename"->forename,
            "surname"->surname,
            "refreshToken"->refreshToken).executeInsert()

it works. I solved the problem, but it would be better not to hardcode the table name for refactoring reasons. Why can't I use the dynamic mapping functionality of Anorm in this case?

Solution

  • As said by m-z, the syntax {placeholder} is only for parameter (when followed by .on("placeholder", value)).

    You can mix plain String interpolation with this syntax SQL(s"SELECT $table ... {id}").