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 <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.
>
>
>
|