• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getting Primary key when inserting

 
Srinivas Rao
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I am inserting a row which contain primary key (generated by sequence), I want to get the sequence back after inserting the row in table. That key value I need to insert into child table. I am using Oracle 9i. I heard that there is some thing for this with Sybase. But don't know about the availability for Oracle. So far we are firing a query to get the sequence number and inserting with that value both in parent & child tables. This is making another DB trip (to get sequence).

Is is a DB/drivers feature or JDBC mandates to provides similar to that?
 
Sripathi Krishnamurthy
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Srinivas Aluri:
When I am inserting a row which contain primary key (generated by sequence), I want to get the sequence back after inserting the row in table. That key value I need to insert into child table. I am using Oracle 9i. I heard that there is some thing for this with Sybase. But don't know about the availability for Oracle. So far we are firing a query to get the sequence number and inserting with that value both in parent & child tables. This is making another DB trip (to get sequence).

Is is a DB/drivers feature or JDBC mandates to provides similar to that?



Assuming the key is a int,
after the insert is success, use
select max(primaryleycolumn) from table

This should fetch you the last primary key updates from a sequence.
 
Padma Lalwani
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
insert into maintable(Primary_Key)
values(seq.nextval)

insert into childtable(Foreign_Key)
values(seq.currentval)


Make sure these are part of same transaction, for you could have another insert in between these two doing seq.nextval, which will change current value of the sequence.

Padma
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64994
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming the key is a int,
after the insert is success, use
select max(primaryleycolumn) from table


Not a good idea. This sets up a race condition in which it is possible that another record can be added to the DB in between the time that you add yours and the time that you try to fetch the new value.
 
Srinivas Rao
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for replies.

What Padma Lalwani looks, it will solve my problem. Any still better solutions Post here.
 
Sripathi Krishnamurthy
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Bear Bibeault:


Not a good idea. This sets up a race condition in which it is possible that another record can be added to the DB in between the time that you add yours and the time that you try to fetch the new value.


Agreed that my solution will faulter. But I was looking at a generic solution. Sequence limits to a oracle DB. How do we make a generic solution that will work for all Databases?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your database supports it (and this is not always the case) you can use the method Statement.getGeneratedKeys(). You have to add an additional integer to the execute command to enable this. Consult the Statement API.

If the command works, use it, although in my own personal experience I've had trouble getting the DBMS to support it. The select max record solution works depending on your transaction management and optimistic locking setup.

Fully manual approach: One general solution I've seen in practice is not to let databases control sequence generation. This is usually a pretty heavy solution, though, since you then have to implement your own singleton sequence generator object and/or service.

Fully automated approach: Finally, you can use java database tools in J2EE environments such as Enterprise EJBs and JDO that, if I'm not mistakenly, can be set to insert sets of data, ie parents and children together, and insert the parent keys as its created.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic