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:)

Manuel Bernhardt · Accepted Answer · 2014-05-12 02:13:53Z

2

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.

answered May 12, 2014 at 2:13

Manuel Bernhardt

3,1242 gold badges30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tmnd91 Over a year ago

You saved my day/night ;) Don't know why I wasn't thinking at it like a pretty wrapper of prepared Statements ;) Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related