Yeah, that's definitely something I want to work on. I'd love to improve ParameterMaps so that they are simply external context (i.e. extra details) around named inline parameters.
I don't like ? marks any more than you do. But for some reason I failed to think of that when I originally wrote the thing. :-)
JIRA feature request anyone?
Cheers,
Clinton
On 11/2/05, Niels Beekman <[EMAIL PROTECTED]> wrote:
Ah, now I see. When you use parameterMaps you should use ?'s instead of
using the inline #'s, you already mapped properties to fields, so there
is no need to do that again. When you want to use a property multiple
times you need to declare it multiple times in the parameterMap too. The
only thing you need to do is set the ?'s in the right place in the
query.
Example:
<parameterMap id="projetosDoUsuarioMap" class=" java.util.HashMap">
<parameter property="l_superusuario" javaType="string"
jdbcType="VARCHAR"/>
</parameterMap>
<select id="projetosDoUsuario" parameterMap="projetosDoUsuarioMap"
resultClass="java.util.HashMap">
SELECT DISTINCT a.CodigoUnesco
FROM corporativo.CT_CC a
WHERE a.ultimonivel = ? <--- gets replaced by the value of
l_superusuario
</select>
It seems like you are trying to do lots of property comparisons rather
than DB-constraints (i.e. #l_superusuario# = 'S'), I believe you should
rather use $l_superusuario$ = 'S', but I suspect the $'s do not work in
combination with parameterMaps, but I did not test that, so you could
try it out...
Good luck,
Niels
-----Original Message-----
From: Daniel Henrique Ferreira e Silva [mailto: [EMAIL PROTECTED]]
Sent: woensdag 2 november 2005 13:37
To: [email protected]
Subject: Re: Problems applying parameter map property
Hi Niels,
Thanks for your reply.
I tried removing CDATA and it still doesn't work.
I was talking to Larry last nigh about this issue and he pointed an
interesting issue with parameter maps. If you look at my statement,
you'll see that i use parameters more than once along the statement.
So, it seems that it'd be necessary to declare those parameters
following order and count in my statement. There is a statement in
documentation stating that but no examples. I tried that but hadn't
any success.
So, i decided to stick to what works ok: inline parameters.
After finishing this project i'll try to look into this issue and
maybe write a Wiki entry about it with my findings.
Cheers,
Daniel Silva
On 11/2/05, Niels Beekman <[EMAIL PROTECTED]> wrote:
> Just a guess, can you try to remove the CDATA-tags? There have been a
> number of people that reported similar issues.
>
> Niels
>
> -----Original Message-----
> From: Daniel Henrique Ferreira e Silva [mailto:[EMAIL PROTECTED]]
> Sent: woensdag 2 november 2005 2:01
> To: [email protected]
> Subject: Problems applying parameter map property
>
> Hey all,
>
> I'm getting an annoying error that is giving me such a great headache
> as i can't figure it out.
>
> Database: Oracle 9iR2
> Container: Resin 3.0.14
> OS: Windows XP SP2
> iBATIS version: 2.1.5.582
>
> Here is my problem:
>
> I have this statement with its parameter map:
>
> <parameterMap id="projetosDoUsuarioMap" class="java.util.HashMap">
> <parameter property="l_superusuario" javaType="string"
> jdbcType="VARCHAR" />
> <parameter property="l_superconsulta" javaType="string"
> jdbcType="VARCHAR" />
> <parameter property="l_prestacaocontas" javaType="string"
> jdbcType="VARCHAR" />
> <parameter property="l_consultatodosmenosfitoca"
> javaType="string" jdbcType="VARCHAR" />
> <parameter property="l_usuario" javaType="int"
jdbcType="NUMBER"
> />
> <parameter property="l_documento" javaType="int"
> jdbcType="NUMBER" />
> <parameter property="l_acesso" javaType="int"
jdbcType="NUMBER"
> />
> </parameterMap>
>
> <select id="projetosDoUsuario" parameterMap="projetosDoUsuarioMap"
> resultClass="java.util.HashMap ">
> <![CDATA[
> select distinct codigoUnesco from (
> select distinct a.CodigoUnesco
> from corporativo.CT_CC a
> where a.ultimonivel = 'S'
> and ( #l_superusuario# = 'S'
> or (#l_superconsulta#='S' and (#l_acesso# is
> null or #l_acesso# = 14))
> or (#l_prestacaocontas#='S' and (#l_acesso# is
> null or #l_acesso# = 16))
> or (#l_consultatodosmenosfitoca# = 'S' and
> (#l_acesso# is null or #l_acesso# = 14) and a.un_tipoprograma not in
> ('FITOCA', 'RUNNING COSTS'))
> )
> union all
> select distinct a.CodigoUnesco
> from corporativo.Ct_CC a
> where a.ultimonivel = 'S'
> and (#l_superusuario# = 'N' or (#l_superconsulta# =
> 'N' and #l_acesso# = 14) or (#l_consultatodosmenosfitoca# = 'N' and
> #l_acesso# = 14))
> and a.handle in ( select distinct x.projeto
> from
> corporativo.vw_solicitacao_permissao x
> where
> x.sq_pessoa_beneficiada = #l_usuario#
> and x.sn_matriz = 'S'
> and (#l_documento# is
> null or x.paginaweb = #l_documento#)
> and (#l_acesso# is
> null or x.tramite = #l_acesso#)
> union all
> select distinct w.handle
> from
> corporativo.vw_permissoes_especiais y
> inner join
> corporativo.z_grupousuarios z on (z.handle = y.usuario),
> corporativo.ct_cc
w
> where z.pessoa =
> #l_usuario#
> and (#l_documento# is
> null or y.permissao = #l_documento#)
> and (#l_acesso# is
> null or y.tramite = #l_acesso#)
> and (y.projeto = 'S'
> or (y.projeto = 'N' and w.un_tipoprograma in ('FITOCA', 'RUNNING
> COSTS')))
> and w.ultimonivel =
'S'
> and w.handle not in
> (select v.projeto
>
> from corporativo.vw_projetoemexcecao v
>
> where v.codigo = 'SG-001')
> )
> )
> order by Codigounesco
>
> ]]>
> </select>
>
> When i run it i get this error in my log file:
>
> 2005-11-01 22:33:24,385 ERROR [resin-tcp-connection-*:8080-1]
> org.unesco.sicof.db.dao.impl.GeralDaoImpl - getProjetosDoUsuario
> failed!
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in org/unesco/sicof/config/geral.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Geral.projetosDoUsuarioMap.
> --- Check the parameter mapping for the 'l_superusuario' property.
> --- Cause: java.sql.SQLException : Invalid column index
>
> In a first glance, everything seems to be fine.
> Any clue?
>
> Cheers,
> Daniel Silva.
>
