You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Yamil Bracho <ya...@hotmail.com> on 2008/10/22 18:19:03 UTC
Problems with Dynamic SQL
Hi.
I have this mapping
<select id="SolAfi.findAllByBancoRif"
parameterClass="java.util.HashMap"
resultMap="mapSolAfi">
<![CDATA[
SELECT co_afiliacion,
nu_rif,
nb_empresa,
cce.solicitud_afiliacion.co_sector,
cce.sector.nb_sector,
cce.solicitud_afiliacion.co_banco,
cce.banco.nb_banco,
ti_solicitud,
st_solicitud,
fe_registro,
co_usuario,
fe_respuesta,
co_funcionario,
nb_nota
FROM cce.solicitud_afiliacion, cce.banco, cce.sector
WHERE cce.solicitud_afiliacion.co_banco = #codigoBanco# AND
cce.solicitud_afiliacion.co_banco = cce.banco.co_banco AND
cce.solicitud_afiliacion.co_sector = cce.sector.co_sector AND
<dynamic prepend=" nu_rif IN ">
<iterate property="rifList" open="('" close="')" conjunction=", ">
#rifList[]#
</iterate>
</dynamic>
]]>
</select>
Where nu_rif is a String field and rifList is an array o String.
In java I did:
Map parameters = new HashMap();
parameters.put("codigoBanco", codigoBanco);
parameters.put("rifList", rifList.split(","));
return getSqlMapClientTemplate().queryForList("SolAfi.findAllByBancoRif", parameters);
However I am getting Error getting ordinal list from JavaBean. Cause java.lang.NumberFormatException: For input string: ""
nu_rif are string like 'E12345', 'J4567', etc
When I take out the dynamic part the code works OK but I need the dynamic part..
Any hint ?
TIA
Yamil
_________________________________________________________________
¡Entra en el Club oficial de Messenger y te enterarás de todas las novedades!
http://www.vivelive.com/ilovemessenger
RE: Problems with Dynamic SQL
Posted by Yamil Bracho <ya...@hotmail.com>.
Well, I change my mapping to
<select id="SolAfi.findAllByBancoRif"
parameterClass="java.util.HashMap"
resultMap="mapSolAfi">
SELECT co_afiliacion,
nu_rif,
nb_empresa,
cce.solicitud_afiliacion.co_sector,
cce.sector.nb_sector,
cce.solicitud_afiliacion.co_banco,
cce.banco.nb_banco,
ti_solicitud,
st_solicitud,
fe_registro,
co_usuario,
fe_respuesta,
co_funcionario,
nb_nota
FROM cce.solicitud_afiliacion, cce.banco, cce.sector
WHERE cce.solicitud_afiliacion.co_banco = #codigoBanco# AND
cce.solicitud_afiliacion.co_banco = cce.banco.co_banco AND
cce.solicitud_afiliacion.co_sector = cce.sector.co_sector AND
cce.solicitud_afiliacion.nu_rif IN ($rifList$)
</select>
And my java code:
Map parameters = new HashMap();
parameters.put("codigoBanco", codigoBanco);
String [] rifArray = rifList.split(",");
for (int i=0; i < rifArray.length;i++) {
rifArray[i] = "'" + rifArray[i] + "'";
}
rifList = StringUtils.join(rifArray, ',');
parameters.put("rifList", rifList);
return getSqlMapClientTemplate().queryForList("SolAfi.findAllByBancoRif", parameters);
It is no what I want to do but i worked...
_________________________________________________________________
Llega la nueva temporada. Consulta las nuevas tendencias en MSN Estilo
http://estilo.es.msn.com/moda/
RE: Problems with Dynamic SQL
Posted by Yamil Bracho <ya...@hotmail.com>.
Thanks Larry.
I take out the CDATA tags and got
<select id="SolAfi.findAllByBancoRif"
parameterClass="java.util.HashMap"
resultMap="mapSolAfi">
SELECT co_afiliacion,
nu_rif,
nb_empresa,
cce.solicitud_afiliacion.co_sector,
cce.sector.nb_sector,
cce.solicitud_afiliacion.co_banco,
cce.banco.nb_banco,
ti_solicitud,
st_solicitud,
fe_registro,
co_usuario,
fe_respuesta,
co_funcionario,
nb_nota
FROM cce.solicitud_afiliacion, cce.banco, cce.sector
WHERE cce.solicitud_afiliacion.co_banco = #codigoBanco# AND
cce.solicitud_afiliacion.co_banco = cce.banco.co_banco AND
cce.solicitud_afiliacion.co_sector = cce.sector.co_sector AND
<dynamic prepend=" nu_rif IN ">
<iterate property="rifList" open="('" close="')" conjunction=", ">
#rifList[]#
</iterate>
</dynamic>
</select>
And now shows me this message
java.sql.SQLException: ORA-01006: la variable ligada no existe
Something like "Binding variable does not exist"
> Date: Wed, 22 Oct 2008 10:34:59 -0600
> From: larry.meadors@gmail.com
> To: user-java@ibatis.apache.org
> Subject: Re: Problems with Dynamic SQL
>
> If you put xml tags in a cdata section, they are ignored...that's the
> point of the cdata section...
>
> Larry
>
>
> On Wed, Oct 22, 2008 at 10:19 AM, Yamil Bracho <ya...@hotmail.com> wrote:
> > Hi.
> > I have this mapping
> >
> > <select id="SolAfi.findAllByBancoRif"
> > parameterClass="java.util.HashMap"
> > resultMap="mapSolAfi">
> > <![CDATA[
> > SELECT co_afiliacion,
> > nu_rif,
> > nb_empresa,
> > cce.solicitud_afiliacion.co_sector,
> > cce.sector.nb_sector,
> > cce.solicitud_afiliacion.co_banco,
> > cce.banco.nb_banco,
> > ti_solicitud,
> > st_solicitud,
> > fe_registro,
> > co_usuario,
> > fe_respuesta,
> > co_funcionario,
> > nb_nota
> > FROM cce.solicitud_afiliacion, cce.banco, cce.sector
> > WHERE cce.solicitud_afiliacion.co_banco = #codigoBanco# AND
> > cce.solicitud_afiliacion.co_banco = cce.banco.co_banco
> > AND
> > cce.solicitud_afiliacion.co_sector =
> > cce.sector.co_sector AND
> > <dynamic prepend=" nu_rif IN ">
> > <iterate property="rifList" open="('" close="')" conjunction=",
> > ">
> > #rifList[]#
> > </iterate>
> > </dynamic>
> > ]]>
> > </select>
> >
> > Where nu_rif is a String field and rifList is an array o String.
> > In java I did:
> >
> > Map parameters = new HashMap();
> > parameters.put("codigoBanco", codigoBanco);
> >
> > parameters.put("rifList", rifList.split(","));
> >
> > return
> > getSqlMapClientTemplate().queryForList("SolAfi.findAllByBancoRif",
> > parameters);
> >
> > However I am getting Error getting ordinal list from JavaBean. Cause
> > java.lang.NumberFormatException: For input string: ""
> > nu_rif are string like 'E12345', 'J4567', etc
> >
> > When I take out the dynamic part the code works OK but I need the dynamic
> > part..
> >
> > Any hint ?
> >
> > TIA
> >
> > Yamil
> >
> >
> > ________________________________
> > ¡Trónchate de risa con los mejores capítulos de South Park en MSN Vídeo!
_________________________________________________________________
¿Eres un cotilla? Disfruta de todas las novedades en MSN Corazón
http://entretenimiento.es.msn.com/corazon/
Re: Problems with Dynamic SQL
Posted by Larry Meadors <la...@gmail.com>.
If you put xml tags in a cdata section, they are ignored...that's the
point of the cdata section...
Larry
On Wed, Oct 22, 2008 at 10:19 AM, Yamil Bracho <ya...@hotmail.com> wrote:
> Hi.
> I have this mapping
>
> <select id="SolAfi.findAllByBancoRif"
> parameterClass="java.util.HashMap"
> resultMap="mapSolAfi">
> <![CDATA[
> SELECT co_afiliacion,
> nu_rif,
> nb_empresa,
> cce.solicitud_afiliacion.co_sector,
> cce.sector.nb_sector,
> cce.solicitud_afiliacion.co_banco,
> cce.banco.nb_banco,
> ti_solicitud,
> st_solicitud,
> fe_registro,
> co_usuario,
> fe_respuesta,
> co_funcionario,
> nb_nota
> FROM cce.solicitud_afiliacion, cce.banco, cce.sector
> WHERE cce.solicitud_afiliacion.co_banco = #codigoBanco# AND
> cce.solicitud_afiliacion.co_banco = cce.banco.co_banco
> AND
> cce.solicitud_afiliacion.co_sector =
> cce.sector.co_sector AND
> <dynamic prepend=" nu_rif IN ">
> <iterate property="rifList" open="('" close="')" conjunction=",
> ">
> #rifList[]#
> </iterate>
> </dynamic>
> ]]>
> </select>
>
> Where nu_rif is a String field and rifList is an array o String.
> In java I did:
>
> Map parameters = new HashMap();
> parameters.put("codigoBanco", codigoBanco);
>
> parameters.put("rifList", rifList.split(","));
>
> return
> getSqlMapClientTemplate().queryForList("SolAfi.findAllByBancoRif",
> parameters);
>
> However I am getting Error getting ordinal list from JavaBean. Cause
> java.lang.NumberFormatException: For input string: ""
> nu_rif are string like 'E12345', 'J4567', etc
>
> When I take out the dynamic part the code works OK but I need the dynamic
> part..
>
> Any hint ?
>
> TIA
>
> Yamil
>
>
> ________________________________
> ¡Trónchate de risa con los mejores capítulos de South Park en MSN Vídeo!