Hi everyone, this seems quite a simple problem to solve, still I can't find a solution for it, nor can find on the web some usefull info to head me in the right direction.
So, I'm using MySQL and iBATIS as my ORM tool. I got a table defined like this :
While my insert statement is defined like this in the sql mapping xml generated by iBator.
Please note that this <insert> node has not been generated by iBator, I defined it by hand starting from the default one generated by Ibator and added the <selectKey> element to retrieve the last inserted autoincrement id (this because Ibator generates an insert statement assuming the object has the key field yet assigned, thus ignoring the autoincrement option).
Inserting the first record on DB runs without any error and I can get back the generated autoincrement key (which is, by no surprise, 1!).
During the 2nd execution, anyway, I get this exception :
So, it seems that MySQL is doing something wrong when generating the 2nd key for the next record inserted. Please also note that I suspect this is an iBATIS problem since using autoincrement keys with raw JDBC code brings no errors at all and everything works fine. I didn't make test to see if I get this to work WITHOUT retrieving the last inserted key (thus deleting the <selectKey> element in mapping xml), but that would not be what I need the code to do.
Sorry everyone, I figured out the problem was not in the mapping I posted, just in a very similar part later in code flow. I asked Scott Selikoff to close and delete this, just to not let anyone to loose time on an unexistent problem.
Hello Matteo, I would appreciate if you would post the difference in what you thought was a problem. I am currently having a similar problem with a referenced table unable to acquire the auto-generated key. I know the key is being generated in the parent table as I set @Rollback(value=true) on the JUnit test method. And, as a result, the parent table record gets inserted with a new record primary key ID. But, then, the new key ID value is lost on the child table that needs the generated primary key value to allow the insertion of a new record into the child table. And, as a result the record insertion attempt on the child table throws the following exceptions. Any ideas are considered helpful. Regards, David.
--- The error occurred in com/st/data/master_SqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the master.insert-InlineParameterMap
Cannot add or update a child row: a foreign key constraint fails (`elblanco`.`master`, CONSTRAINT `fk_Master` FOREIGN KEY (`Id`) REFERENCES `master` (`Id`) ON UPDATE CASCADE); nested exception is com.ibatis.common.jdbc.exception.NestedSQLException
Yet some, not wise, go to the other side of the globe, to barbarous and unhealthy regions, and devote ten or twenty years, in that they may live,-that is, keep comfortably warm,- and die in New England at last. Henry David Thoreau - Walden - 1845