how to retrieve the id of a record just inserted?

how to retrieve the id of a record just inserted?
in sql server, "Select @@IDENTITY", SCOPE_ IDENTITY( ) and IDENT_CURRENT('table_name') can do it;
in oracle, row_id will implement;
BUT is there any unique method to do this in oracle, sql server, and other types of database?
in our scenario, we need migrate the db in several types.
so we want to find one method to simplify that.
thanks!

This is why I tend to keep sequence management separate to the SQL statement. Firstly it is portable, and secondly you always know the inserted ID.
Dave

Originally posted by David O'Meara:
This is why I tend to keep sequence management separate to the SQL statement. Firstly it is portable, and secondly you always know the inserted ID.
Dave

thanks for your reply!
isn't there any other solutions for that?
and for "you always know the inserted ID", how do you deal with the concurrent scenario?
[ December 19, 2002: Message edited by: emily li ]

Emily,
Have a look at java.sql.Statement (your driver will have to support these operations), but you should be able to issue a Statement and pass it a flag to return generated keys:

Best of Luck,
Cory Wilkerson

Emily,
What corywilk is proposing is part of the JDBC 3.0 specification (and new to that specification). Therefore, the JDBC drivers you wish to use, need to implement that functionality. I know that the latest JDBC driver from Oracle, does not support that function -- and (naturally) there is no information regarding when (and if) that functionality will be supported. So I wouldn't get too keen about corywilk's suggestion, if I were you (for now, anyway).
As far as I know, there is still no DBMS-independent way to get the ID of the row just inserted (and that's the reason fro the additional functionality in the JDBC 3.0 spec).
Sorry I couldn't help you any more.
Good Luck,
Avi.

Agreed...I have the convenience of a JDBC 3.0 compliant driver at hand...I should have mentioned that in my post. Rather neligent of me not to.

"corywilk",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave

if we keep whole insert query and the select query in one synchronized block, and in select we order by id desc, is it a fool proof way to get the id of the record just inserted

What happens if another record gets inserted before you manage to execute your query?
You will assume the wrong id for your data.
Dave

but we have both the query in a synchronized block.....which means that at a time query will be executed for one user only
gaurav

Sorry, I missed the fact you synchronized the code block, but this doesn't make a difference since this still doesn't stop the database from being able to perform actions.
Another unsynchronized block of code could insert a row, or it could be done via a completely different interface.
Dave

for oracle the syntax is:
CallableStatement stmt = connection.prepareCall("
begin insert into foo(a,b,c,d) values (foo_seq.nextval,?,?,?) return a into ?;end;");
stmt.setString(1,b);
stmt.setString(2,c);
stmt.setString(3,d);
stmt.registerOutParameter( 4,java.sql.Types.INTEGER );
execute(stmt);
long a = stmt.getLong( a);

I have no idea how to do it in DB2, but since I never use DB2 thats not my problem...
HTH Simon