Retrival of newly created row auto generated identity column values

Retrival of newly created row auto generated identity column values

Post by Chris Highfie » Wed, 28 Feb 2001 02:35:35



I am trying to write a piece of code that will insert a new row into a
table with and auto incrementing identity column and retrieve the
newly generated id.  This is easy to do with JavaScript using the
underlying Microsoft ADO using the following code as an example:

// rs is an updatable ADODB.recordset object
// created with a 'select * from test_table'
// or 'select top 1 * from test_table'
rs.AddNew();
rs("sample_column") = "hello world";
rs.Update();
var newId = rs("id_column");

( assuming the table has 2 columns of which id_column is auto
incrementing )

The following Java code correctly creates the record

// rs is a scrollable, updatable ResultSet object
rs.moveToInsertRow();
rs.updateString("sample_column","hello world");
rs.insertRow();

I have been unable to simple obtain the value of the newly created id
immediately after inserting the row (eventhough it is in the database)
and have tried many methods ( moving to different rows, chaing the
select statement etc etc) to do so.

The platform I am using is as follows:
Database: Microsoft SQL Server 7
JDK: 1.3
Java Database Connectivity: JDataServer (by NetDirect)

If anyone can offer any assistance it would be much appreciated.

Regards,

Chris.

 
 
 

Retrival of newly created row auto generated identity column values

Post by Hartmu » Wed, 28 Feb 2001 18:44:34


Chris,

after insert do the query

This is a special variable in SQLServer containing always the
last generated identity value in the current connection.
Be aware:
if you insert e.g. 3 rows, you get only the i of the last of it.

It works great, also with different connections or different
users doing concurrently inserts.

Regards,
Hartmut