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