CODEWITHSUNDEEP
postgresqlscalaplayframeworkplayframework-2.2anorm
Incerteza
Incerteza

Reputation: 34924

I'm struggling getting back id from postgresql in Play

I'm struggling inserting a record into a postgresql:

val res = DB.withConnection {
        implicit con =>
          SQL(
            """INSERT INTO
              my_table(id, user_id, file_name, added_at)
              VALUES(DEFAULT, {userId}, {fileName}, {currentTime})
              RETURNING id
            """)
            .on("userId" -> 1, "fileName" -> "fileName1", "currentTime" -> new java.sql.Timestamp(DateTime.now.getMillis))
            .executeInsert()
      }

Ok(toJson(JsObject(Seq("res" -> JsString(res.toString)))))

It does insert a value (which I can see via pdAdmin) but it returns an error eventually ERROR: syntax error at or near "RETURNING".

If I remove RETURNING id then the error becomes TypeDoesNotMatch(Cannot convert requestBody4050249427671619471asTemporaryFile :class java.lang.String to Long for column ColumnName(my_table.file_name,Some(file_name)))]]

When I cut off id and DEFAULT the error remains the same.

my_table defined is 

CREATE TABLE my_table
(
  file_name character(255),
  user_id integer,
  added_at timestamp without time zone,
  id serial NOT NULL,
  CONSTRAINT my_table_pk PRIMARY KEY (id),
  CONSTRAINT scan_fk FOREIGN .....
)

Why is that? What it has to do with file_name anyway?

Upvotes: 1

Views: 451

Answers (1)

Fernando Correia
Fernando Correia

Reputation: 22375

Given this table:

CREATE TABLE my_table
(
  file_name character(255),
  user_id integer,
  added_at timestamp without time zone,
  id serial NOT NULL,
  CONSTRAINT my_table_pk PRIMARY KEY (id)
);

You can insert a record and retrieve its id like this:

package dao

import anorm._
import anorm.SqlParser._
import play.api.db.DB
import play.api.Logger
import play.api.Play.current

object MyDAO {

  def insert(fileName: String, userId: Int): Int = {
    val sql = SQL(
      """
        insert into my_table(file_name, user_id, added_at)
        values ({fileName}, {userId}, CURRENT_TIMESTAMP)
        returning id
      """)
      .on(
        "fileName" -> fileName,
        "userId" -> userId)
    DB.withConnection { implicit c =>
      val id = sql.as(scalar[Int].single)
      Logger.info(s"Inserted '$fileName' with id '$id'.")
      id
    }
  }

}

And test it like this:

import org.junit.runner._
import org.specs2.mutable._
import org.specs2.runner._
import play.api.test._
import play.api.test.Helpers._
import dao.MyDAO

@RunWith(classOf[JUnitRunner])
class MyDAOSpec extends Specification {
  "MyDAO" should {
    "insert a record and return its id" in new WithApplication {
      val id = MyDAO.insert("file1", 101)
      id must be_>=(0)
    }
  }
}

See Anorm, simple SQL data access for more examples of how to retrieve data from result sets.

Upvotes: 1

Related Questions