Get autoincremented value back after insert #10

Closed
omj opened this Issue Jan 11, 2011 · 9 comments

5 participants

@omj

If I want to insert values to table with autoincremented row, I want it's value back. How can I accomplish this with Scala Query?

@szeiger
Scala Language-Integrated Connection Kit member

Supporting this feature in ScalaQuery is challenging because different DBMSs require very different solutions (see http://en.wikipedia.org/wiki/Insert_%28SQL%29#Retrieving_the_key for an overview). Some of these solutions can be used easily on top of ScalaQuery (e.g. LAST_INSERT_ID() for MySQL) whereas others would need support from the ScalaQuery drivers (e.g. the RETURNING clause). In these cases, I suggest creating GUIDs on the client side or SELECTing incremented sequence values and using them for subsequent INSERTs as a work-around.

I am leaving this ticket open in the hope that it will be possible to define semantics for such a feature that can be made to work on most supported DBMSs.

@omj

Some of these solutions can be used easily on top of ScalaQuery

I'm using H2 where the function is :
INSERT INTO TABLENAME(COLUMNNAME1, COLUMNNAME2) VALUES(VAL1, VAL2);
SELECT SCOPE_IDENTITY()
So what should I do to insert values and retrieve id of inserted row?
Also,

SELECTing incremented sequence values

could be error prone for concurrent
environment so GUID is only possible workaround in such cases.

@szeiger
Scala Language-Integrated Connection Kit member

You can wrap add SCOPE_IDENTITY() as a ScalaQuery function:

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

And then use it in queries:

 println("Inserted id "+Query(scopeIdentity).first)

Concurrent access is not a problem because SCOPE_IDENTITY() works independently for each session.

@macias

I would vote even for partially implementing this, after all other features are also not equally implemented for all databases.

If I am not mistaken this feature is essential to fully support "clustered" inserts (master record + slave records).

@gsigmund

Is there a plan when this will be implemented?

@nafg
@gsigmund

ok. Is there info about SLICK available on the web?

@szeiger
Scala Language-Integrated Connection Kit member

Fixed in 09a65a8

@szeiger szeiger closed this Aug 9, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment