Hi Philippe Thank you so much for your quick response. It did work as per your solution and thanks for explaining <dynamic/> tag behavior in detail.
What I was missing was prepend=" " !! ~ Nilesh -----Original Message----- From: Philippe Laflamme [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 4:51 PM To: ibatis-user-java@incubator.apache.org Subject: Re: Dynamic insert stmt Hi Nilesh, The purpose of the <dynamic> tag is mostly to solve this problem. Here's how it works. Consider the following snippet: [...] WHERE <isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty> <isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty> [...] The situation here is similar to yours: if A is empty and B is non-empty, only the second isNotEmpty tag is evaluated and it would result in something like this: [...] WHERE AND B = 'BValue' [...] Which obviously breaks... Now consider the following snippet: [...] <dynamic prepend="WHERE"> <isNotEmpty property="A" prepend="AND">A = #A#</isNotEmpty> <isNotEmpty property="B" prepend="AND">B = #B#</isNotEmpty> </dynamic> [...] See the "WHERE" keyword? Whenever tags are embedded, "prepend" tags are pushed down the tree until the body of an embdedded tag is evaluated. In the situation described above, the prepend="WHERE" would be pushed down to the isNotEmpty tags. The tag for the "A" property is not evaluated, so it moves on to the tag for the "B" property. In this case, its body is evaluated so its "prepend" attribute becomes prepend="WHERE" and prepend="AND" is pushed down (since no other tags are embedded, it simply disapears). The result is: WHERE B = 'BValue' In your situation you should simply add <dynamic prepend=" "> and </dynamic> tags. <dynamic> tags can be used in any kind of statement (even insert). Read the SQLMap guide carefully, it's pretty clear on how dynamic tags and the prepend attribute behave... Cheers, Philippe Nilesh Bhattad wrote: > Hi there, > > > > I have the below <insert/> statement defined in a sqlmap. If > 'BlackListed' is not passed thro the HashMap, and rest of the fields are > passed in, one comma is inserted before 'isMarried' column and it > becomes an invalid sql stmt. I tried surrounding <isNotEmpty/> with > <dynamic/> tag, but that didn't help. (not sure if <dynamic/> can be > used within in insert stmt or not) > > > > <insert id="insert" parameterClass="java.util.HashMap"> > > insert into Consultant( > > <isNotEmpty property="BlackListed">BlackListed</isNotEmpty> > > <isNotEmpty property="isMarried" > prepend=",">isMarried</isNotEmpty> > > <isNotEmpty property="ActiveInactive" > prepend=",">ActiveInactive</isNotEmpty> > > <isNotEmpty property="HomeTelephone" > prepend=",">HomeTelephone</isNotEmpty> > > <isNotEmpty property="OfficeTelephone" > prepend=",">OfficeTelephone</isNotEmpty> > > <isNotEmpty property="OfficeTelExt" > prepend=",">OfficeTelExt</isNotEmpty> > > ) values ( > > <isNotEmpty property="BlackListed">#BlackListed#</isNotEmpty> > > <isNotEmpty property="isMarried" > prepend=",">#isMarried#</isNotEmpty> > > <isNotEmpty property="ActiveInactive" > prepend=",">#ActiveInactive#</isNotEmpty> > > <isNotEmpty property="HomeTelephone" > prepend=",">#HomeTelephone#</isNotEmpty> > > <isNotEmpty property="OfficeTelephone" > prepend=",">#OfficeTelephone#</isNotEmpty> > > <isNotEmpty property="OfficeTelExt" > prepend=",">#OfficeTelExt#</isNotEmpty> > > ) > > <selectKey resultClass="int" keyProperty="id">SELECT > @@IDENTITY as last_insert_id</selectKey> > > </insert> > > > > Would someone please help me out to find what I'm doing wrong? > > > > Thanks > > Nilesh >
