multiple insert within a select tag
Title: multiple insert within a select tag Hi all, following scenario: I'va an object Instrument. It references an another object option. create table instrument ( id int not null auto_increment primary key, internalNumber varchar(12) not null unique ); create table option ( instrument_id int not null auto_increment primary key, name varchar(12) not null unique constraint instrument_option_fk_01 foreign key (instrument_id) references instrument (id) ON DELETE CASCADE ON UPDATE CASCADE ); An option object can not be exist without corresponding instrument. That means, deleting of an instrument object from the data base implies the need of deletion of the referenced option (if not null) before. Deleting and update of instruments work well. But inserting a new instrument throws a lot of questions: 1. first I tried to call multiple inserts within an insert element in mapping file. The hibernate doesn't accept this. org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in Instrument.xml. --- The error occurred while applying a parameter map. --- Check the instrument-InlineParameterMap. --- Check the statement (update failed). --- Cause: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO option(instrument_id, name at line 1 2. To solve this problem, I wrote two insert fragments. One for instrument and one for option. In my DAO implementaiotn then call they in right order (first inserting instrument and then option using id of the stored instrument. My Question is: how does iBATIS handle the inserting of coposed objects? If my instrument is referenced by N other objects, shoud I take care of inserting all of them? Why are delete and insert not symmetric? During delete is done by just one step, why does insert several steps? I'm using iBATIS 2.1.5 in combination with spring 1.2.x. Thanks in advance and best regards Reza
Re: multiple insert within a select tag
Reza, I don't know if this is what is causing the problem, but I think there might be a problem with your table definition for the option table. instrument_id is supposed to reference the id in the instrument table, but you have it set up as a auto_increment field. I would think that you would need to retrieve the generated id from the instrument insert to populate the instrument_id field before doing the insert. Do you do that? Don't use MySQL, but thought it might be worth mentioning. Regards, Al At 01:56 AM 8/29/2005, you wrote: Hi all, following scenario: I'va an object Instrument. It references an another object option. create table instrument ( id int not null auto_increment primary key , internalNumber varchar (12) not null unique ); create table option ( instrument_id int not null auto_increment primary key , name varchar (12) not null unique constraint instrument_option_fk_01 foreign key (instrument_id) references instrument (id) ON DELETE CASCADE ON UPDATE CASCADE ); An option object can not be exist without corresponding instrument. That means, deleting of an instrument object from the data base implies the need of deletion of the referenced option (if not null) before. Deleting and update of instruments work well. But inserting a new instrument throws a lot of questions: 1. first I tried to call multiple inserts within an insert element in mapping file. The hibernate doesn't accept this. org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in Instrument.xml. --- The error occurred while applying a parameter map. --- Check the instrument-InlineParameterMap. --- Check the statement (update failed). --- Cause: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO option(instrument_id, name at line 1 2. To solve this problem, I wrote two insert fragments. One for instrument and one for option. In my DAO implementaiotn then call they in right order (first inserting instrument and then option using id of the stored instrument. My Question is: how does iBATIS handle the inserting of coposed objects? If my instrument is referenced by N other objects, shoud I take care of inserting all of them? Why are delete and insert not symmetric? During delete is done by just one step, why does insert several steps? I'm using iBATIS 2.1.5 in combination with spring 1.2.x. Thanks in advance and best regards Reza
Re: multiple insert within a select tag
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=intSELECT last_insert_id()
/selectKey
INSERT INTO instrument_option (instrument_id,name) VALUES (#id#, #optionComponent.name#);
/insert
Thanks,
Reza
