Slick Scala not inserting any values

Question

Sort of a slick n00b...I'm trying to build an insert statement, but find I am not inserting anything.

def newUser(userName: String, email: Option[String], password: String = null, removed: Option[Int] = null) = SlickInit.dbMaster withSession {

  val creation = Option(new java.sql.Timestamp(new java.util.Date().getTime()))
  val last = new java.sql.Timestamp(new java.util.Date().getTime())

  printf("REACHED 1. creation: " + creation + "\n last: " + last)
  println("\nusername: " + userName + "\n email: " + email)
  println("maxId: " + maxId)

  val invoker = Users.insertInvoker

  (Users.userId ~ Users.userName ~ Users.email ~ Users.userPassword ~ Users.creationDate ~ Users.lastLoginDate ~ Users.removed).insert(maxId, userName, email, password, creation, last, removed)

  val statement = Users.insertStatement
  println("REACHED 2. \n\nStatement: " + statement)
}

REACHED 1 prints (with the desired values) when a POST request is issued, but not REACHED 2. I'm sure theres something wrong with my insert statement, but I'm not sure what. (Also, obviously, when I query the database, the inserted value does not return.) Does anyone have any insight into this?

EDIT, here is my Users table definition:

object Users extends Table[(Int, String, Option[String], String, Option[Timestamp], Timestamp, Option[Int])]("APUsers") {

   def userId          = column[Int]("UserId", O.PrimaryKey, O.AutoInc)
   def userName        = column[String]("UserName")
   def email           = column[Option[String]]("Email", O.Nullable)
   def userPassword    = column[String]("UserPassword")
   def creationDate    = column[Option[Timestamp]]("CreationDate", O.Nullable)
   def lastLoginDate   = column[Timestamp]("LastLoginDate")
   def removed         = column[Option[Int]]("removed", O.Nullable)

   def * = userId ~ userName ~ email ~ userPassword ~ creationDate ~ lastLoginDate ~ removed
 }

Do see any exceptions being thrown? Are you using a memory database? — pedrofurla, Jul 24 '13 at 17:03

David Riccitelli · Accepted Answer · 2013-07-24T17:18:44.560

In the Users object definition, you need to change this:

object Users extends Table[(Int, String, Option[String], String, Option[Timestamp], Timestamp, Option[Int])]("APUsers") {

to this:

object Users extends Table[(Option[Int], String, Option[String], String, Option[Timestamp], Timestamp, Option[Int])]("APUsers") {

because the id is assigned by the DBMS (O.AutoInc). See the example here: http://slick.typesafe.com/doc/1.0.1/lifted-embedding.html#mapped-tables

Then you need to change this:

def * = userId ~ userName ~ email ~ userPassword ~ creationDate ~
        lastLoginDate ~ removed

to this:

def * = userId.? ~ userName ~ email.? ~ userPassword ~ creationDate.? ~
        lastLoginDate ~ removed.?

because they are defined as Option. See here: http://slick.typesafe.com/doc/1.0.1/lifted-embedding.html#mapped-tables

In the newUser the insert line should be, from this:

(Users.userId ~ Users.userName ~ Users.email ~ Users.userPassword ~
 Users.creationDate ~ Users.lastLoginDate ~ Users.removed)

to

(Users.userName ~ Users.email.? ~ Users.userPassword ~ Users.creationDate.? ~ 
 Users.lastLoginDate ~ Users.removed.?)
 .insert(userName, email, password, creation, last, removed)

without userId, because it'll be assigned by the DBMS. See the example here: http://slick.typesafe.com/doc/1.0.1/lifted-embedding.html#inserting

Since you do not accept Null in the database, I would suggest to change this:

def newUser(userName: String, email: Option[String], password: String = null, removed: Option[Int] = null)

to this:

def newUser(userName: String, email: Option[String], password: String, removed: Option[Int] = null)

and check that the password is not null.

As per pedrofurla suggestion you can add the following to the Users object:

def forInsert = Users.userName ~ Users.email.? ~ Users.userPassword ~ 
                Users.creationDate.? ~ Users.lastLoginDate ~ Users.removed.?

and make the line more readable:

Users.forInsert.insert(userName, email, password, creation, last, removed)

See http://slick.typesafe.com/doc/1.0.1/lifted-embedding.html#inserting

Thanks for the suggestion David. Unfortunately, Eclipse is throwing the error: `- too many arguments for method insert: (value: (Int, String, Option[Option[String]], String, Option[Option[java.sql.Timestamp]], java.sql.Timestamp, Option[Option[Int]]))(implicit session: scala.slick.session.Session)Int` — Alex Spangher, Jul 24 '13 at 16:59
Oh, true, I forgot about the PK. I usually make another projection containing the insertion fields. — pedrofurla, Jul 24 '13 at 17:16
Yep, I do as well :-) removed on behalf of the the following `While some database systems allow inserting proper values into AutoInc columns or inserting None to get a created value, most databases forbid this behaviour, so you have to make sure to omit these columns. Slick does not yet have a feature to do this automatically but it is planned for a future release. For now, you have to use a projection which does not include the AutoInc column`: http://slick.typesafe.com/doc/1.0.1/lifted-embedding.html#inserting — David Riccitelli, Jul 24 '13 at 17:19
@AlexSpangher you can try the updated answer, let me know if something is unclear. — David Riccitelli, Jul 24 '13 at 18:18

Slick Scala not inserting any values

1 Answers1