ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Niels Beekman" <n.beek...@wis.nl>
Subject RE: Problems applying parameter map property
Date Wed, 02 Nov 2005 14:15:03 GMT
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:dhsilva@gmail.com] 
Sent: woensdag 2 november 2005 13:37
To: user-java@ibatis.apache.org
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 <n.beekman@wis.nl> 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:dhsilva@gmail.com]
> Sent: woensdag 2 november 2005 2:01
> To: user-java@ibatis.apache.org
> 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.
>

Mime
View raw message