i am working with HSQL database for testing purpose. i want standalone db file. but now i am in trouble to get last inserted row id (auto-incremental - identity) in HSQL. how can i get id??
1
- 2i got the solution CALL IDENTITY(); after insert row.... (but this will give last inserted row id)Ankur Loriya– Ankur Loriya2012-04-27 15:34:52 +00:00Commented Apr 27, 2012 at 15:34
Add a comment |
2 Answers
http://www.hsqldb.org/doc/guide/ch09.html
the last inserted value into an identity column for a connection is available using the function IDENTITY(), for example (where Id is the identity column):
INSERT INTO Test (Id, Name) VALUES (NULL,'Test'); CALL IDENTITY(); 
1 Comment
Matthieu Cormier
The link in this answer is out of date. Info on Identity() can now be found in chapter 10 here: hsqldb.org/doc/guide/…
It's pretty hard to write a query to perform this when you haven't given your table schema, but something like the following:
SELECT TOP 1 Id FROM [TABLENAME] ORDER BY Id DESC 3 Comments
scottb
While this may work, it would be hard to imagine a more expensive way to get the generated identity. Moreover, if rows are deleted from a table, there is no guarantee that the next identity value generated will always be the largest.
weenoid
"there is no guarantee that the next identity value generated will always be the largest" - Regardless of the method used to obtain the id of the last row inserted you will always encounter this problem.
Adrien
Prefer IDENTITY() (see ryanjustus' answer). Using the max ID of the table would return a wrong result if other concurrent transactions also insert data.