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

Help with FK in PreparedStatement

Dominic Steng�rd
Ranch Hand

Joined: Feb 05, 2001
Posts: 186
Hey JDBC gurus!

Need help. Im working with PreparedStatement to commit a transaction with info to two database tables. The problem is that the second tables INSERT statement needs to know which primary key the first tables INSERT was given, so that the second tables foreign key can be set to the correct value. The foreign key in the second table refers to the primary key in the first table.

How can I get to know which primary key the first INSERT statement is given, before I INSERT the second data chunk?

Hm ... hope you guys understand what my problem is.
Thanks in advance.

Regards


Dominic Steng�rd<br />Sun Certified Java 2 Programmer
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
One option is to use the method below, where the autoGenertedKeys is
Statement.RETURN_GENERATED_KEYS. Then you can use then use the getGeneratedKeys method which will return a resultset of the generated keys:

Shankar Narayana
Ranch Hand

Joined: Jan 08, 2003
Posts: 134
Hi,

What I do basically to get around with this type of problem is create a sequence table for ex: for table name 'abc', i have the corresponding sequence table 'abc_sequence',which contains a single field 'sequence_id' and before you update 'abc' table, u insert a record into 'abc_sequence' table and get the 'sequence_id' which will be the primary key for abc table.

This way you can work with any database.


Regards,
Shankar.


"Failure is not when you fall down; its only when you fail to get up again."
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1125

Dominic,
Not all JDBC drivers (and databases) support Craig's suggestion of using the Statement.RETURN_GENERATED_KEYS. Oracle, for example, does not. So if you are using an Oracle database, you're out of luck.

Shankar failed to mention that you need to lock the "sequence" table (in a multi-user environment) so that no-one else "steals" your primary key. While most databases provide facilities for locking tables, not all do.

In other words, the way to solve your problem depends on the database you are using. So if you care to mention what database that is, you may get some more relevant replies.

Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help with FK in PreparedStatement