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.
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.
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.
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)
so use break points/logging and find out what is actually being used as the new value.
and this is helpful:
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 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.