persist object in postgresql database with anorm in a play framework 2

Question

I'm trying to persist objects to a postgresql Db inside my play2 webapp using anorm library. The Create statement of the table "Address" is this one:

create table address (
    addressId               bigserial not null,
    addressName             varchar(255) not null,
    street                  varchar(255) not null,
    number                  varchar(15) not null,
    ZIP                     varchar(15) not null,
    region                  varchar(63) not null,
    country                 varchar(63) not null,
    constraint pk_address primary key (addressId)
);

And my Scala/Anorm code looks like this:

private def create(address: Address, recursive : Boolean): Long = {
    DB.withConnection { implicit connection =>
        SQL( """
             insert into {table_name} (addressName, street, number, ZIP, region, country)
             values ({name},{street},{number},{ZIP},{region},{country})
            """).on(
            "table_name" -> TABLE_NAME,
            "name" -> address.name,
            "street" -> address.street,
            "number" -> address.number,
            "ZIP" -> address.ZIP,
            "region" -> address.region,
            "country"-> address.country
        ).executeInsert[Option[Long]]().get
    }
}

Obviously TABLE_NAME is a val that contains the string "Address"

The problem is that when i try to execute it with a proper Address instance like:

Address("Antonio","vacchi","12","48012","RA","Italia")

I get this exception:

[PSQLException: ERROR: syntax error at or near "$1" Posizione: 13] 

'Posizione' is the italian word for Position (dunno why i get the error in italian o:)

Answer 1

Using the interpolation with the {table_name} syntax will result in the SQL containing a positioned parameter. This is something that is not supported when it comes to CREATE statements. So in this case you have to write out Address and not pass it in the on parameters.