CODEWITHSUNDEEP
scalaplayframeworkplayframework-2.0postgresql-9.1anorm
dabuki
dabuki

Reputation: 1031

Play, Anorm and PostgreSQL: Problems with serial columns?

I used Play 2.1.0 with anorm, Scala 2.10 and PostgreSQL (9.1, driver: 9.1-901.jdbc4). The following query worked fine in MySQL. After moving to PostgreSQL it doesn't. After entering the method, there is an "exception" thrown in line "implicit connection", i.d. the debugger jumps right to Sql.resultSetToStream line 527, where obviously the metadata of the columns are determined. In the Play logs there is no error shown...

The id field was in MySql an integer and in PostgreSQL it is a serial. Does Anorm has a problem with serial columns?

def getUserId(userName: String): Int = {
DB.withConnection {
  implicit connection =>
    try {
      val result = SQL("select  id from users where user_name = {userName}")
        .on('userName -> userName).apply().head
      result[Int]("id")
    } catch {
      case e: SQLException =>
        Logger.error(e.getStackTraceString)
        //error logged, but no problem when we return 0
        0
    }
  }
 }

I've got the same problem with an insert statement in the same table.

The interesting thing is that the following query works:

def checkCredentials(userName: String, password: String): Boolean = {
DB.withConnection {
  implicit connection =>
    try {
      val result = SQL("select count(*) as c from users where user_name = {userName} and password = crypt({password}, password)")
        .on('userName -> userName,
          'password -> password).apply().head
      result[Long]("c") > 0
    } catch {
      case e: SQLException =>
        Logger.error(e.getStackTraceString)
        false
    }
}

Upvotes: 1

Views: 310

Answers (1)

dabuki
dabuki

Reputation: 1031

The problem was the id column. Apparently PostgreSQL considers this as a function word, so if you put it between ' ' then it works, this means writing 'id' solves the problem.

Upvotes: 1

Related Questions