This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Foreign key problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Foreign key problem" Watch "Foreign key problem" New topic
Author

Foreign key problem

Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hi!

I'm having some problems with foreign key. My program has to import some data from XML file and write them in mySQL table. I created two tables, TR_AJPES_TR in which I store data from XML file and TR_AJPES_TR_LOG which contains date of import and file from which data was imported.
In TR_AJPES_TR table I created foreign key which is linked to primary key in TR_AJPES_TR_LOG table and I want that this foreign key is written in its own column next to imported data.
So I googled tutorials how to make foreign key and managed to did it but now I don't know if this foreign key writes in TR_AJPES_TR automatically or I have to do it manually? If so, how?

Here is my code so you can easily understand me. I complicate, I know.

Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Here are my thoughts
1, why are you selecting all the rows from the table? all you want to do is insert new rows.
2, investigate prepared statements (find them from connection).
2a, Inserting using result sets just feels dirty.
2b, create your prepared statement outside your while loop, they are a create once use lots of times object. (see the thread on max num cursor exceeds http://www.coderanch.com/t/517015/JDBC/java/java-sql-SQLException-ORA-maximum)
3, yes you will need to manually set the foriegn key value yourself in your insert statement for the child row.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hi!

Thanks for answer. I'm basically continuing thread from this topic.
I'm just looking answer how do I update/write foreign key? I'm now googling 2 days to find answer but I still haven't found it.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

it is just a column, you insert it like any other column in your insert statement.
The only diff between a foriegn key column and another column is that the database does some checking on the values inserted.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hm...before insert statement I tried this:

int j = uprs1.getInt("ID_LOG"); - this should get value of ID_LOG column in TR_AJPES_TR_LOG table, right?
uprs.updateInt("ID_LOG",j); - and then this should update ID_LOG column in TR_AJPES_TR table(ID_LOG in this table is foreign key)

But I get this error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`dbtest`.`t_ajpes_tr`, CONSTRAINT `t_ajpes_tr_ibfk_1` FOREIGN KEY (`ID_LOG`) REFERENCES `t_ajpes_tr_log` (`ID_LOG`) ON DELETE CASCADE ON UPDATE CASCADE)
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

i have just been playing around with this is mysql, and the only time i get the error you are reporting is when i try to update to a log_id that doesn't exist.

so use break points/logging and find out what is actually being used as the new value.

and this is helpful:
http://stackoverflow.com/questions/201887/primary-key-from-inserted-row-jdbc
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
LOG_ID?? Hm...I checked and I don't have any LOG_ID column. I tried with my code above and checked what is going on and int j doesn't get value from ID_LOG.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

oki have read the other thread, and you need to stop using this resultSet stuff and use prepared statements.
if your boss objects to that get another job, which i hope you are already looking for.
And if you explain why you are leaving so quickly and the other boss doesn't think that is a good reason, don't go there either (for anyone else reading this he isn't allowed to use sax etc to parse xml but do it by hand)
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
I still haven't found solution to my problem.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Igor Juric wrote:I still haven't found solution to my problem.

You have been helped a lot.
You have been told to investigate a more regular way of implementing this database handling, then people will be able to help you.
You were told in the previuos thread to look up the javadoc for resultSet.update, and that it wasn't doing what you expected.
I have never looked at that method but as you are inserting many rows a prepared statement will be much more efficient and more people will be able to help you.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Hi!

Yes, I know I've been helped a lot. I will investigate and update this program after I finish it but now I have to do it this way.
Igor Juric
Ranch Hand

Joined: Nov 04, 2010
Posts: 31
Now working.
What I did is that I moved cursor to last updated row in TR_AJPES_TR_LOG and used getInt() to get value from "ID" column.

Thanks to everybody who helped me. I really appreciate your help, W.G.!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Foreign key problem
 
Similar Threads
Reading from XML file
UnsupportedEncodingException: SJIS
Check for duplicates in database before insert
a few general questions
Nested Reslutsets