Anorm String Interpolation not replacing variables

Question

We are using Scala Play, and I am trying to ensure that all SQL queries are using Anorm's String Interpolation. It works with some queries, but many are not actually replacing the variables before the query is executing.

import anorm.SQL
import anorm.SqlStringInterpolation

object SecureFile 
{
  val table = "secure_file"
  val pk = "secure_file_idx"
  ...

// This method works exactly as I would hope
def insert(secureFile: SecureFile): Option[Long] = {
  DBExec { implicit connection =>
    SQL"""
      INSERT INTO secure_file (
        subscriber_idx,
        mime_type,
        file_size_bytes,
        portal_msg_idx
      ) VALUES (
        ${secureFile.subscriberIdx},
        ${secureFile.mimeType},
        ${secureFile.fileSizeBytes},
        ${secureFile.portalMsgIdx}
        )
      """ executeInsert()
    }
  }

def delete(secureFileIdx: Long): Int = {
  DBExec { implicit connection =>
    // Prints correct values
    println(s"table: ${table} pk: ${pk} secureFileIdx: ${secureFileIdx} ")

    // Does not work
    SQL""" 
      DELETE FROM $table WHERE ${pk} = ${secureFileIdx} 
    """.executeUpdate()

    // Works, but unsafe
    val query = s"DELETE FROM ${table} WHERE ${pk} = ${secureFileIdx}" 
    SQL(query).executeUpdate() 
  }
}
....
}

Over in the PostgreSQL logs, it's clear that the delete statement has not acquired the correct values:

2015-01-09 17:23:03 MST ERROR:  syntax error at or near "$1" at character 23
2015-01-09 17:23:03 MST STATEMENT: DELETE FROM $1 WHERE $2 = $3
2015-01-09 17:23:03 MST LOG:  execute S_1: ROLLBACK

I've tried many variations of execute, executeUpdate, and executeQuery with similar results. For the moment, we are using basic string replacement but of course this is bad because it's not using PreparedStatements.

Lysander

There is a misunderstanding of Anorm interpolation, standard Scala interpolation and PreparedStatement. – Poised

Answer 1

Anorm String interpolation was introduced to pass parameter (e.g. SQL"Select * From Test Where id = $x), with interpolation arguments (e.g. $x) set on underlying PreparedStament according proper type conversion (see use cases on https://www.playframework.com/documentation/2.3.x/ScalaAnorm ).

Next Anorm release will also have the #$foo syntax to mix interpolation for parameter with standard string interpolation. This will allow to write DELETE FROM #$table WHERE #${pk} = ${secureFileIdx} and having it executed as DELETE FROM foo WHERE bar = ? (if literal table is "foo" and pk is "bar"), with literal secureFileIdx passed as parameter. See related pull request.

Until next revision is release, you can build Anorm from its master sources ti benefit from this change.

Poised

Answer 2

For anyone else sitting on this page scratching their head and wondering what they might be missing...

SQL("select * from mytable where id = $id") 

is NOT the same as

SQL"select * from mytable where id = $id" 

The former does not do String interpolation whereas the latter does.

This is easily overlooked in the aforementioned docs as all the samples provided just happen to have a (non-related) closing parenthesis on them (like this sentence does)

Pancratium

Thanks! I spent too much time wondering why I get ERROR: syntax error at or near "{" with syntax like {id} until I found this and tried SQL() with parentheses. – Lahey