Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 66456 invoked from network); 29 Aug 2005 14:26:30 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 29 Aug 2005 14:26:30 -0000 Received: (qmail 28735 invoked by uid 500); 29 Aug 2005 14:26:29 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 28563 invoked by uid 500); 29 Aug 2005 14:26:28 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 28550 invoked by uid 99); 29 Aug 2005 14:26:28 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 29 Aug 2005 07:26:28 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=HTML_60_70,HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [213.138.34.28] (HELO bean.sungard.de) (213.138.34.28) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 29 Aug 2005 07:26:44 -0700 Received: from ffm-iproxy.sungard.de (localhost [127.0.0.1]) by bean.sungard.de (8.12.3/8.12.3/SuSE Linux 0.6) with ESMTP id j7TEQM8C005686 for ; Mon, 29 Aug 2005 16:26:23 +0200 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5ACA5.9FBD2179" Subject: AW: multiple insert within a select tag Date: Mon, 29 Aug 2005 16:26:19 +0200 Message-ID: <6DB5565517FFA44498EA068EA7D23B33077DC0@ffm-mx2.banking.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: multiple insert within a select tag Thread-Index: AcWsofOqq9yTd9o/QVCeeeXKaEICagAAWvuw From: "Farsi, Reza" To: X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------_=_NextPart_001_01C5ACA5.9FBD2179 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Albert, =20 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: =20 INSERT INTO instrument (internalNumber) VALUES = (#internalNumber#)=20 SELECT = last_insert_id() =20 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: =20 DELETE FROM instrument WHERE id=3D#id# =20 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?=20 if (instrument.getId() =3D=3D 0) { getSqlMapClientTemplate().insert("saveInstrument", = instrument); if (instrument.getOptionComponent() !=3D null) { getSqlMapClientTemplate().insert("saveInstrumentOption", = instrument); } } else { // ... update } } =20 INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, = #optionComponent.name#); =20 My question is: why can't I call both inserts toghther. I mean, how can = I call statements like: =20 INSERT INTO instrument (internalNumber) VALUES = (#internalNumber#)=20 SELECT = last_insert_id() INSERT INTO instrument_option (instrument_id, name) VALUES = (#id#, #optionComponent.name#); =20 Thanks, Reza =20 =20 =20 -----Urspr=FCngliche Nachricht----- Von: Albert L. Sapp [mailto:asapp@uiuc.edu] Gesendet: Montag, 29. August 2005 16:06 An: user-java@ibatis.apache.org Betreff: 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,=20 following scenario:=20 I'va an object Instrument. It references an another object option.=20 create table instrument (=20 id int not null auto_increment primary key ,=20 internalNumber varchar (12) not null unique=20 );=20 create table option (=20 instrument_id int not null auto_increment primary key ,=20 name varchar (12) not null unique=20 constraint instrument_option_fk_01 foreign key (instrument_id) = references instrument (id) ON DELETE CASCADE ON UPDATE CASCADE );=20 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:=20 1. first I tried to call multiple inserts within an insert element in = mapping file. The hibernate doesn't accept this.=20 org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; = bad SQL grammar []; nested exception is = com.ibatis.common.jdbc.exception.NestedSQLException: =20 --- The error occurred in Instrument.xml. =20 --- The error occurred while applying a parameter map. =20 --- Check the instrument-InlineParameterMap. =20 --- Check the statement (update failed). =20 --- 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:=20 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.=20 Thanks in advance and best regards=20 Reza=20 ------_=_NextPart_001_01C5ACA5.9FBD2179 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi=20 Albert,

thanks a lot = for the=20 answer. Indeed it was the reason of the problem. I added the following = selectKey=20 to my mapping file and it works:

    <insert=20 id=3D"saveInstrument" parameterClass=3D"instrument">
       &nbs= p;   =20 INSERT INTO instrument (internalNumber) VALUES=20 (#internalNumber#)
         &nb= sp; =20 <selectKey keyProperty=3D"id"=20 resultClass=3D"int">SELECT=20 last_insert_id()</selectKey = >
   =20 </insert>

I'm now facing = another=20 problem: By deleting an instrument iBATIS detects the dependency of the = option=20 data and removes it from the corresponding table. I don't need to do = anything,=20 just calling "deleteInstrument" which has been defined as=20 follows:

    <delete=20 id=3D"deleteInstrument" parameterClass=3D"instrument">
       =20 DELETE FROM instrument WHERE id=3D#id#
    </delete>

By inserting a = new=20 instrument, I first have to call inserting of the new instrument and = then call=20 manually the insert of eventually corresponding option. See Java and = mapping=20 part below:

public=20 void save(Instrument instrument) {
      // = is it=20 save or update?
      if = (instrument.getId()=20 =3D=3D 0) {
        &= nbsp;=20 getSqlMapClientTemplate().insert("saveInstrument",=20 instrument);
         &nb= sp;if=20 (instrument.getOptionComponent() !=3D null)=20 {
           &n= bsp;  =20 getSqlMapClientTemplate().insert("saveInstrumentOption",=20 instrument);
        = =20 }
      } else=20 {
           //= =20 ... update
     =20 }
}

<insert = id=3D"saveInstrumentOption"=20 parameterClass=3D"instrument">
INSERT INTO = instrument_option=20 (instrument_id, name) = VALUES (#id#,=20 #optionComponent.name#);
</insert>

My question is: = why can't I=20 call both inserts toghther. I mean, how can I call statements=20 like:

    <insert=20 id=3D"saveInstrument" parameterClass=3D"instrument">
       &nbs= p;   =20 INSERT INTO instrument (internalNumber) VALUES=20 (#internalNumber#)
        &n= bsp;  =20 <selectKey keyProperty=3D"id"=20 resultClass=3D"int">SELECT last_insert_id()</selectKey >

&nbs= p; =20 INSERT INTO instrument_option (instrument_id, name) VALUES (#id#, = #optionComponent.name#);
=20 </insert>

Thanks,

Reza

-----Urspr=FCngliche Nachricht-----
Von: Albert L. = Sapp=20 [mailto:asapp@uiuc.edu]
Gesendet: Montag, 29. August 2005=20 16:06
An: user-java@ibatis.apache.org
Betreff: Re: = multiple insert within a select tag

Reza,

I = don't=20 know if this is what is causing the problem, but I think there might = be a=20 problem with your table definition for the option table. = instrument_id=20 is supposed to reference the id in the instrument table, but you have = it set=20 up as a auto_increment field. I would think that you would need = to=20 retrieve the generated id from the instrument insert to populate the=20 instrument_id field before doing the insert. Do you do = that? Don't=20 use MySQL, but thought it might be worth=20 mentioning.

Regards,

Al

At 01:56 AM 8/29/2005, = you=20 wrote:

Hi all, =

following=20 scenario:
I'va an object Instrument. It references an another = object=20 option.

create table instrument=20 (
        id int not null auto_increment primary key ,=20
        internalNumber varchar (12) not null unique
);=20

create table option (=20
        instrument_id int not null auto_increment primary key ,=20
        name varchar (12)=20 not null unique=20

        constraint = instrument_option_fk_01 foreign key (instrument_id) references = instrument (id) ON DELETE CASCADE ON UPDATE CASCADE

);

An option object can = not be=20 exist without corresponding instrument. That means, deleting of an=20 instrument object from the data base implies the need of deletion of = the=20 referenced option (if not null) before.

Deleting and update = of=20 instruments work well. But inserting a new instrument throws a lot = of=20 questions:
1. first I tried to call multiple inserts within an = insert=20 element in mapping file. The hibernate doesn't accept this. =
org.springframework.jdbc.BadSqlGrammarException:=20 SqlMapClient operation; bad SQL grammar []; nested exception is=20 com.ibatis.common.jdbc.exception.NestedSQLException:  =20

--- The error occurred = in=20 Instrument.xml.
--- = The error=20 occurred while applying a parameter map.
--- Check the = instrument-InlineParameterMap. =20
--- Check the statement = (update=20 failed).
--- Cause:=20 java.sql.SQLException: You have an error in your SQL syntax; check = the=20 manual that corresponds to your MySQL server version for the right = syntax to=20 use near '; INSERT   INTO option(instrument_id, name at = line=20 1

2. To solve this problem, I wrote two insert = fragments. One=20 for instrument and one for option. In my DAO implementaiotn then = call they=20 in right order (first inserting instrument and then option using id = of the=20 stored instrument.

My Question is:
how does iBATIS handle = the=20 inserting of coposed objects? If my instrument is referenced by N = other=20 objects, shoud I take care of inserting all of them? Why are delete = and=20 insert not symmetric? During delete is done by just one step, why = does=20 insert several steps?

I'm using iBATIS 2.1.5 in combination = with=20 spring 1.2.x.

Thanks in advance and best regards
Reza=20 =

------_=_NextPart_001_01C5ACA5.9FBD2179--