How to retrieve the auto incremented ID using slick / plainSQL?

Question

Isn't there a slick/plainSQL native solution to retrieve the auto incremented id of the current INSERT?

userId is an auto incremental field in my mySQL table.

sql"""
   INSERT INTO `table`(`email`) 
   OUTPUT INSERTED.userId 
   VALUES ("theEmailAdress@test.de")
""".as[Int].firstOption

Help would be greatly appreciated.

Cheers Oliver

Oliver, PLEASE cross-link topics when you cross post with the mailing list https://groups.google.com/d/msgid/scalaquery/f0842022-0e62-4fab-b0e9-fb13e77f5bd9%40googlegroups.com — cvogt, Feb 25 '14 at 17:01

score 3 · Answer 1 · edited Feb 25 '14 at 16:24

3

Depends on the database.

For MS SQL it's SCOPE_IDENTITY(), for mySQL it's LAST_INSERT_ID(). Try searching for equivalent for your DB if it's none of the above.

Added by cvogt:

There is currently no slick-built-in feature for plain SQL for this and no way to access the underlying jdbc statement when using Slick's sql"..." interpolation or StaticQuery, which would allow you to access getGeneratedKeys. You could probably patch the SQL interpolator and StatementInvoker to allow this. They are just 150 LOC. Maybe worth giving a shot and submitting a PR.

You could however use one of the Slick session method like withPreparedInsertStatement, which wrap jdbc connection methods to work with a jdbc statement. I created a PR to add documentation about this: https://github.com/slick/slick/pull/691

edited Feb 25 '14 at 16:24

cvogt

11,260
30
46

answered Feb 25 '14 at 11:40

Ziouas

519
1
6
18

Thanks for the quick answere. But this is not the solution Iam looking for: INSERT INTO `table`(`email`) VALUES ("theEmailAdress@test.de"); SELECT LAST_INSERT_ID(); Arent there slick on-board native tools? – OliverKK Feb 25 '14 at 11:45
I don't know what do you mean by that. AFAIK this is the only common solution and I never had a reason to search for something else. – Ziouas Feb 25 '14 at 11:51
3

I took the liberty of adding directly to the answer. – cvogt Feb 25 '14 at 14:58

score 1 · Accepted Answer · edited May 23 '17 at 12:13

Thank you cvogt for helping out in this discussion. I think it would be helpful to submit a PR, inasmuch as it is a very common and useful functionality which should not be missing in slick's plainSQL queries.

Finally, i found a work-around to replace the missing native function as following.

Within the same session I settle two queries. The first one is the INSERT statement, the second statement is SELECT LAST_INSERT_ID() which returns the newest automatically generated value that was set for the AUTO_INCREMENT column by the recently executed INSERT(1). More details here: MySQL Reference - LAST_INSERT_ID()

Database.forDataSource(dataSource).withDynSession {
  sqlu"""INSERT INTO `users`(`email`) VALUES ("theEmailAdress@test.de")
  """.firstOption match {
    case Some(num) if num == 1 => sql"SELECT LAST_INSERT_ID()".as[Long].firstOption()
    case None => None
  }
}

This works for me right now. If there are any improvements, do not hesitate to post your solution.

How to retrieve the auto incremented ID using slick / plainSQL?

2 Answers2

Linked