How to find out value of inserted AutoInc value

[Jonathan]

I have a ScalaQuery database table with an AutoInc primary key field.
After inserting a new record, what’s the best way to find out the id
that was automatically generated and inserted into its id field?

For example…

object Users extends ExtendedTable[(Int, String, String)]("users") {
def id = column[Int]("id", O PrimaryKey, O AutoInc)
def username = column[String]("username")
def password = column[String]("password")
def * = id ~ username ~ password
def noId = username ~ password
}

db withSession {
Users.noId insert (“user1”, “12345”)

// how do I retrieve the most-recently-inserted id
field of my new row?
}

Thanks,

Jonathan

[Stefan Zeiger]

On 2011-01-22 00:32, Jonathan wrote:
> I have a ScalaQuery database table with an AutoInc primary key field.

> After inserting a new record, what�s the best way to find out the id

> that was automatically generated and inserted into its id field?

The mechanisms for this are very DBMS-specific. There's an issue about
this here https://github.com/szeiger/scala-query/issues#issue/10 which
somehow got closed. I have reopened it now. Assuming that this was
opened by you and you're indeed using H2, this should work:

val db = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver")
db withSession {

object User extends Table[(Int, String)]("USERS") {
def id = column[Int]("ID", O.AutoInc, O.PrimaryKey)
def name = column[String]("NAME")
def * = id ~ name
}

val scopeIdentity =
SimpleScalarFunction.nullary[Long]("scope_identity")

User.ddl.create

User.name.insert("foo")
println("Inserted id "+Query(scopeIdentity).first)
User.name.insert("bar")
println("Inserted id "+Query(scopeIdentity).first)
}

H2's SCOPE_IDENTITY() function returns the last inserted identity value
for the current session (see
http://www.h2database.com/html/functions.html#scope_identity)

In practice, you'll probably want to cache the Invoker for scopeIdentity
or use a SimpleStatement, but this is the most general solution.
Creating a SimpleScalarFunction allows you to use SCOPE_IDENTITY() in
typed queries like you would use any built-in function.

-sz

[nafg]

As I commented on the ticket, while it's true that at the SQL level it depends on the DBMS, JDBC abstracts over it. See the links in my comment on the ticket.

[Troy Payne]

Stefan,

What import should be used to do this?  I tried to import org.scalaquery.ql.SimpleScalarFunction but I'm receiving a "SimpleScalarFunction is not a member of org.scalaquery.ql compile error."  I'm using ScalaQuery v0.9.5

Thanks in advance,

--Troy

[virtualeyes]

import org.scalaquery.ql.SimpleFunction

val sequenceID = SimpleFunction.nullary[Int]("Your-DB-Sequence-Function-Name")

works here...

[Troy Payne]

great that worked, thanks!

--
 
 
 

[ijuma]

On Saturday, 22 January 2011 11:16:14 UTC, Stefan Zeiger wrote:

Assuming that this was
opened by you and you're indeed using H2, this should work:

     val db = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver")
     db withSession {

       object User extends Table[(Int, String)]("USERS") {
         def id = column[Int]("ID", O.AutoInc, O.PrimaryKey)
         def name = column[String]("NAME")
         def * = id ~ name
       }

       val scopeIdentity =
SimpleScalarFunction.nullary[Long]("scope_identity")

       User.ddl.create

       User.name.insert("foo")
       println("Inserted id "+Query(scopeIdentity).first)
       User.name.insert("bar")
       println("Inserted id "+Query(scopeIdentity).first)
     }

H2's SCOPE_IDENTITY() function returns the last inserted identity value
for the current session (see
http://www.h2database.com/html/functions.html#scope_identity)

In practice, you'll probably want to cache the Invoker for scopeIdentity
or use a SimpleStatement, but this is the most general solution.
Creating a SimpleScalarFunction allows you to use SCOPE_IDENTITY() in
typed queries like you would use any built-in function.

This seems unnecessarily painful when compared to other libraries. In Squeryl, for example, it's completely transparent. I don't like that it mutates the inserted entity, but it should be possible to come up with something more general for Slick too, no?

Best,

Ismael