aspose file tools*
The moose likes JDBC and the fly likes Get or Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Get or Insert" Watch "Get or Insert" New topic
Author

Get or Insert

Brian Shultz
Greenhorn

Joined: Jan 10, 2007
Posts: 2
I am trying to create a method that either gets and Id or inserts the a new row, what i have works some of the time but not all the time. sometimes i get a Duplicate Key message back. The table is keyed on the combination of identifierref and object_type_id, can anyone recommend a better more stable way, This is using the SQLServerDriver class for 2005 and Microsoft SQL Server 2000. Thank you in advance.

Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30917
    
158

Brian,
Welcome to JavaRanch!

It looks like the duplicate key exception is thrown when two callers execute the code at the same time. This could happen if you have the following sequence of events:
1) You check if record exists and it doesn't
2) Another thread inserts a record with that key
3) You insert the record and get a duplicate key exception

Take a look at transactions to eliminate this scenario.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Brian Shultz
Greenhorn

Joined: Jan 10, 2007
Posts: 2
Thankyou,
That makes sense, so would the best way around this be a try catch that looks for duplicate key again, and then if caught try to select it and if not found try to insert it again. Or is there a good way around this at all?
Thanks
_Brian
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Get or Insert