Re: Dynamic insert stmt

[Philippe Laflamme]

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