Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to retrieve the id of a record just inserted?

 
emily li
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
emily li
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Cory Wilkerson
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Cory Wilkerson
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
 
gaurav chaudhary
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What happens if another record gets inserted before you manage to execute your query?
You will assume the wrong id for your data.
Dave
 
gaurav chaudhary
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic