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
Joined: Dec 18, 2002
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, 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.
Joined: Aug 14, 2001
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
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
subject: how to retrieve the id of a record just inserted?