ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert L. Sapp" <as...@uiuc.edu>
Subject Re: multiple insert within a select tag
Date Mon, 29 Aug 2005 14:06:19 GMT
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
>
>
>
>
>
>
>
>

Mime
View raw message