Just to correct a possible misunderstanding - iBATIS does not detect the dependancy on your delete. MySQL is doing it through a cascaded delete.
iBATIS is really just sending SQL to the database through JDBC - there's very little magic going on here.
iBATIS knows very little about your table structures or your intent with these kinds of operations. The solution in your case is to break the double insert into two different statements and call them independantly, and in the right order, from your DAO layer - it will still be the same transaction and connection so there will be little or no performance impact.
This is a typical issue using auto generated keys - each database handles it differently so iBATIS is limited in what it can do for you automatically. As you've seen, updates and deletes can be handled by the database because the keys are already set. Inserts with auto generated keys are a special case in all databases.
Jeff Butler
On 8/29/05, Farsi, Reza <[EMAIL PROTECTED]> wrote:
Hi Albert,thanks a lot for the answer. Indeed it was the reason of the problem. I added the following selectKey to my mapping file and it works:<insert id="saveInstrument" parameterClass="instrument">
INSERT INTO instrument (internalNumber) VALUES (#internalNumber#)
<selectKey keyProperty="id" resultClass="int"> SELECT last_insert_id()</selectKey >
</insert>I'm now facing another problem: By deleting an instrument iBATIS detects the dependency of the option data and removes it from the corresponding table. I don't need to do anything, just calling "deleteInstrument" which has been defined as follows:<delete id="deleteInstrument" parameterClass="instrument" >
DELETE FROM instrument WHERE id=#id#
</delete>By inserting a new instrument, I first have to call inserting of the new instrument and then call manually the insert of eventually corresponding option. See Java and mapping part below:public void save(Instrument instrument) {
// is it save or update?
if (instrument.getId() == 0) {
getSqlMapClientTemplate().insert("saveInstrument", instrument);
if (instrument.getOptionComponent() != null) {
getSqlMapClientTemplate().insert("saveInstrumentOption", instrument);
}
} else {
// ... update
}
}<insert id="saveInstrumentOption" parameterClass="instrument">
INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, #optionComponent. name#);
</insert>My question is: why can't I call both inserts toghther. I mean, how can I call statements like:<insert id="saveInstrument" parameterClass="instrument">
INSERT INTO instrument (internalNumber) VALUES (#internalNumber#)
<selectKey keyProperty="id" resultClass="int">SELECT last_insert_id() </selectKey >INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, #optionComponent.name#);
</insert>Thanks,Reza
