[3.0.0-RC1] get LAST_INSERT_ID

[arnaud...@gmail.com]

Hi all,

I used the following code to get the last insert id with slick 2.1

override def registerAccount(userId: Long, accountNumber: String, sortCode: String): Option[Long] = db withSession { implicit session =>
  val num1 = BankAccount.map(a => (a.userId, a.accountNumber, a.sortCode)) +=(userId, Some(accountNumber), Some(sortCode))

  val id = num1 match {
    case 1 => Q.queryNA[Long]("SELECT LAST_INSERT_ID()").firstOption
    case _ => None
  }
  id
}

How can I use returning keyword without specifying the full object  (here User)

from the doc:

val userId =
  (users returning users.map(_.id)) += User(None, "Stefan", "Zeiger")

to something like

val userId =
  (users returning users.map(_.id)) +=("Stefan", "Zeiger")

is there a better way with slick 3?

Thanks.

Arnaud.

[Shawn]

+1 I have essentially the same question and haven't found anything to help in slick 3. 

[Viktor Holmberg]

+1, having the same issue!

[Arnaud]

Finally I am using this code in slick 3 to get the Id of an insert via auto increment.

def registerGBAccount(userId: Long, accountNumber: String): Future[Long] = {
  val query = BankAccount.map(a => (a.userId, a.accountNumber))
  val actions = (for {
    rows <- query +=(userId, Some(accountNumber))
    accountId <- sql"SELECT LAST_INSERT_ID()".as[(Long)].head
  } yield accountId).transactionally

  db.run(actions)
}

Cheers,

αrnaud.

[Matan Safriel]

Hi αrnaud,

How is this actually better than using slick's .returning? 

It may seem as if this is introducing a transaction where a simple SQL idiom that does not require one, already exists. 

Also, going doing to plain SQL, why not just use plain SQL all the way instead of slick here, scala string interpolators do a pretty good job for neat string composition don't they. 

That aside, I just provide what you called "the full object" (the parallel of type User in your example), with an arbitrary zero value for the ID. That simply works.

Matan

[Arnaud]

Hi Matan,

This is not better, but I wanted to do an insert without having to set all the default values managed by MySQL.

Like auto inc, created and updated date, some empty fields.

I do not like to use a transaction for this as the query is much slower.

I was not aware that putting 0 for the auto inc works fine. 

I use now 0 for the auto inc fields and null for the NOT NULL DEFAULT CURRENT_TIMESTAMP seems to make the trick.

You said the sql idom does not require transaction. How does it works?

015-04-01 11:12:22,754 DEBUG slick.compiler.QueryCompiler  - After phase codeGen:

| ResultSetMapping : Vector[(Long')]

|   from s9: CompiledStatement "insert into `card_pay_in` (`id`)  values (?)" : (Long')

|   map: CompiledMapping : Long'

|     converter: BaseResultConverter$mcJ$sp idx=1, name=id : Long'

Preparing insert statement (returning: id): insert into `card_pay_in`

Thanks of lot for your feedback.

Arnaud.

[Matan Safriel]

Hi Arnaud,

I am not sure about your second question, maybe disregard my hasty comment concerning it. Just to note I chose the value 0 very arbitrarily.

Matan 

--

---
You received this message because you are subscribed to a topic in the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/scalaquery/QB-h1zhaTOc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to scalaquery+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/caf271c4-07d7-4208-b9d1-bfa08d67f248%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.