Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 63090 invoked from network); 13 Sep 2006 22:24:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 13 Sep 2006 22:24:33 -0000 Received: (qmail 10788 invoked by uid 500); 13 Sep 2006 22:24:32 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 10293 invoked by uid 500); 13 Sep 2006 22:24:31 -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 10282 invoked by uid 99); 13 Sep 2006 22:24:31 -0000 Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org) (140.211.166.84) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Sep 2006 15:24:30 -0700 Authentication-Results: idunn.apache.osuosl.org smtp.mail=jeffgbutler@gmail.com; spf=pass Authentication-Results: idunn.apache.osuosl.org header.from=jeffgbutler@gmail.com; domainkeys=good X-ASF-Spam-Status: No, hits=0.4 required=5.0 tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,RCVD_BY_IP Received-SPF: pass (idunn.apache.osuosl.org: domain gmail.com designates 66.249.82.224 as permitted sender) DomainKey-Status: good X-DomainKeys: Ecelerity dk_validate implementing draft-delany-domainkeys-base-01 Received: from ([66.249.82.224:55881] helo=wx-out-0506.google.com) by idunn.apache.osuosl.org (ecelerity 2.1 r(10620)) with ESMTP id 63/C2-00682-28488054 for ; Wed, 13 Sep 2006 15:23:09 -0700 Received: by wx-out-0506.google.com with SMTP id h30so2854610wxd for ; Wed, 13 Sep 2006 15:21:51 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=CrSNvlODJkzLbCo+IYuCE8Fckmg+uc7YjfucFWGEBjWlpU1254cHVmOWi7ddcvVe9By1u6ZVFhOeVVWo6PqkhgAiu62Ztcgag9Qibc2LZXk0iCrUiBZrcsZZorJ/pCJy/yOqvnBboLNJIzJJv1WHulPajjKemRYxkJBZ6WrqvO4= Received: by 10.90.117.15 with SMTP id p15mr3047264agc; Wed, 13 Sep 2006 15:21:51 -0700 (PDT) Received: by 10.90.74.4 with HTTP; Wed, 13 Sep 2006 15:21:51 -0700 (PDT) Message-ID: Date: Wed, 13 Sep 2006 17:21:51 -0500 From: "Jeff Butler" To: user-java@ibatis.apache.org Subject: Re: For some reason CDATA doesn't "work" in some queries In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1048_25885696.1158186111524" References: <48CC2EED5175B540BCE9633A5D94F14406BBB74C@mail4.pennmutual.com> X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N ------=_Part_1048_25885696.1158186111524 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Here's some good information about CDATA if you're new to XML: http://www.w3schools.com/xml/xml_cdata.asp Jeff Butler On 9/13/06, Jeff Butler wrote: > > 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 < and > > 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 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? > > > > > > > http://www.ibatis.com/dtd/sql-map-2.dtd"> > > > > > resultClass="xml" xmlResultName="policy"> > > > 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 > > > conjunction=","> > > #clientRoles[]# > > > > and a.prod_cd = p.prod_cd > > and p.ins_type_cd in > > > close=")" conjunction=","> > > #viewableAcctInsTypeCodes[]# > > > > > > > > and upper(a.acct_nmbr) like > > #policyNumber# > > > > > > > > and a.acct_id = ca.client_id > > and ca.client_id = c.client_id > > > > and ca.client_acct_role_cd in > > > close=")" conjunction=","> > > #clientRoleCodes[]# > > > > > > and c.tax_id=#taxId# > > > > > > and upper(c.first_nm) like #firstName# > > > > > > and upper(c.mddl_nm) like #middleName# > > > > > > and upper(c.last_nm) like #lastName# > > > > > > > > ]]> > > > > > > > > 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. > > > > > > > ------=_Part_1048_25885696.1158186111524 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Here's some good information about CDATA if you're new to XML:
 
 
Jeff Butler


 
On 9/13/06, Jeff Butler <jeffgbutler@gmail.com> wrote:
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.


------=_Part_1048_25885696.1158186111524--