Jeff,
 
I agree -- I don't need it in this case -- but every example I've seen has it in the front and back -- suppose I had a nested tag with < >  -- still wouldn't work, right?
 
Just curious.
 
Bill
 
-----Original Message-----
From: Jeff Butler [mailto:jeffgbutler@gmail.com]
Sent: Wednesday, September 13, 2006 6:18 PM
To: user-java@ibatis.apache.org
Subject: Re: For some reason CDATA doesn't "work" in some queries

You've got CDATA arount too much - it's causing iBATIS to ignore the nested dynamic SQL tags.
 
You only need to use CDATA sections if your SQL statements contain XML delimeters like < and >.  And even in that case you can use &lt; and &gt; for greater clarity.
 
Bottom line - don't use CDATA unless you absolutely need it.  I don't think you need it at all in this query.
 
Jeff Butler

 
On 9/13/06, Morone, Bill <Morone.Bill@pennmutual.com> wrote:

Hello,

Maybe it is something I am doing wrong (long history of such circumstances).
When I attempt to run this statement, only passing in the userClientId, clientRoles, viewableAcctInsTypeCodes with the CDATA demarcation I get a java.lang.NumberFormatException. If I make exactly the same call without the CDATA demarcation it runs perfectly fine. I've noticed sometines CDATA causes other problems -- is it a bug?

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" " http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="ClientholdingApp">
    <statement id="getPolicyListClient" parameterClass= "java.util.Map" resultClass="xml" xmlResultName="policy">   
     <![CDATA[
     select distinct a.acct_nmbr as POLICYID      
        from acct a, client_acct ca, client c, prod p          
        where
          c.base_client_id = #userClientId#
          and c.client_id = ca.client_id
          and a.acct_id = ca.acct_id
          and ca.client_acct_role_cd in          
          <iterate property="clientRoles" open="(" close=")" conjunction=",">
              #clientRoles[]#
          </iterate>          
          and a.prod_cd = p.prod_cd
                  and p.ins_type_cd in
          <iterate property="viewableAcctInsTypeCodes" open="(" close=")" conjunction=",">
              #viewableAcctInsTypeCodes[]#
          </iterate>

                  <isNotEmpty property="policyNumber">
                                and upper(a.acct_nmbr) like #policyNumber#
                  </isNotEmpty>        
                 
                  <isNotEmpty property="clientRoleCodes">     
                                and a.acct_id = ca.client_id
                                and ca.client_id = c.client_id                                 
                                and ca.client_acct_role_cd in          
                            <iterate property="clientRoleCodes" open="(" close=")" conjunction=",">
                              #clientRoleCodes[]#
                            </iterate>                                 
                            <isNotEmpty property="taxId">
                                and c.tax_id=#taxId#
                            </isNotEmpty>                         
                            <isNotEmpty property="firstName">
                                and upper(c.first_nm) like #firstName#
                            </isNotEmpty>
                            <isNotEmpty property="middleName">
                                and upper(c.mddl_nm) like #middleName#
                            </isNotEmpty>
                            <isNotEmpty property="lastName">
                                and upper(c.last_nm) like #lastName#
                            </isNotEmpty>                                                                  
          </isNotEmpty>
          ]]>                                                   
    </statement>    
</sqlMap>

This message, including any attachments, is intended only for the recipient(s) 
named above. It may contain confidential and privileged information. If you have 
received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not 
the intended recipient, any review, disclosure, copying, distribution or any 
action or reliance based on this message is prohibited by law.  


This message, including any attachments, is intended only for the recipient(s) 
named above. It may contain confidential and privileged information. If you have 
received this communication in error, please notify the sender immediately and 
destroy or delete the original message. Also, please be aware that if you are not 
the intended recipient, any review, disclosure, copying, distribution or any 
action or reliance based on this message is prohibited by law.