This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Journey To Enterprise Agility and have Daryl Kulak & Hong Li on-line!
See this thread for details.
Win a copy of The Journey To Enterprise Agility this week in the Agile and Other Processes forum! And see the welcome thread for 20% off.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

getting Primary key when inserting  RSS feed

 
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?
 
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.
 
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
 
Author and ninkuma
Marshal
Posts: 66783
168
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?
 
author
Bartender
Posts: 4096
21
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
Boost this thread!