Parameter Problem inside a sub-query

[Todd]

Hi -

I am trying to write a query with a subquery:
<select id="getSitesByDomain" parameterType="SiteSearchObject"
resultType="java.lang.String">
SELECT sites.site_url
FROM cme.tbl_sites sites
WHERE sites.site_url LIKE '#{domain}'
AND sites.site_id IN (
SELECT accreds.site_id
FROM cme.tref_siteaccreditationorg accreds,
cme.tbl_organizations orgs,
cme.tref_organizationuser orgusers
WHERE accreds.site_id = sites.site_id
AND accreds.org_id = orgs.accred_org_id
AND orgs.org_id = orgusers.org_id
AND orgusers.user_id = #{userId})

</select>

and I am getting this error:
### Error querying database. Cause: java.sql.SQLException: Parameter
index out of range (2 > number of parameters, which is 1).
### The error may involve TrackerInfo.getSitesByDomain-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Parameter index out of range (2 >
number of parameters, which is 1).
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
at tracker.TrackerDao.isApprovedDomain(TrackerDao.java:211)
at tracker.TrackerDao.processCreditRequest(TrackerDao.java:160)
at
tracker.TrackerDataCredit.processCreditRequest(TrackerDataCredit.java:
73)
at webservice.CmeTrackerWs.CreditWS(CmeTrackerWs.java:73)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at
org.jboss.wsf.container.jboss42.InvocationHandlerJSE.invoke(InvocationHandlerJSE.java:
102)
at
org.jboss.ws.core.server.ServiceEndpointInvoker.invoke(ServiceEndpointInvoker.java:
221)
at
org.jboss.wsf.stack.jbws.RequestHandlerImpl.processRequest(RequestHandlerImpl.java:
466)
at
org.jboss.wsf.stack.jbws.RequestHandlerImpl.handleRequest(RequestHandlerImpl.java:
284)
at
org.jboss.wsf.stack.jbws.RequestHandlerImpl.doPost(RequestHandlerImpl.java:
201)
at
org.jboss.wsf.stack.jbws.RequestHandlerImpl.handleHttpRequest(RequestHandlerImpl.java:
134)
at
org.jboss.wsf.stack.jbws.EndpointServlet.service(EndpointServlet.java:
84)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
290)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
206)
at
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:
96)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:
235)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
206)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:
230)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:
175)
at
org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:
182)
at
org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:
84)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:
127)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:
102)
at
org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:
157)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
109)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
262)
at
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:
844)
at org.apache.coyote.http11.Http11Protocol
$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:
446)
at java.lang.Thread.run(Unknown Source)

It seems that when I have a parameter inside a sub-query it doesnt
work. Anyone come across this before?

Thanks for the help!

[Poitras Christian]

Try removing the extra ' in: WHERE sites.site_url LIKE '#{domain}'
It should be: WHERE sites.site_url LIKE #{domain}

Christian

-----Message d'origine-----
De : mybati...@googlegroups.com [mailto:mybati...@googlegroups.com] De la part de Todd
Envoyé : September-02-10 11:52 AM
À : mybatis-user
Objet : Parameter Problem inside a sub-query

[Todd Brady]

Sorry, I've changed the query so many times to get it to work I missed that before posting. 

I want to do this in the end:

WHERE sites.site_url LIKE '%#{domain}%'

 

I tried it without the ' and it said invalid syntax.  And with the ' I still get the same Cause: java.sql.SQLException: Parameter

index out of range (2 > number of parameters, which is 1).

 error.

[Poitras Christian]

You have different options:

1)      Use LIKE ‘%${domain}%’ – remember that this opens you to SQL injection attacks.

2)      Add % to domain variable in the Service/DAO before calling the select and keep only LIKE #{domain}.

3)      Use a syntax that allows a normal use of parameter #{domain}. For example, LIKE ‘%’ || #{domain} || ‘%’

 

Christian

 

De : mybati...@googlegroups.com [mailto:mybati...@googlegroups.com] De la part de Todd Brady
Envoyé : September-02-10 12:26 PM
À : mybati...@googlegroups.com
Objet : Re: Parameter Problem inside a sub-query

[Todd Brady]

Yes! You're a life saver.  I would never have figured this out without your help!  The error was very general and wasn't leading me in the right direction.  Again, thanks so much for your help.   :-)