Parameter Problem inside a sub-query

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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!
Reply | Threaded
Open this post in threaded view
|

RE: Parameter Problem inside a sub-query

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 : [hidden email] [mailto:[hidden email]] De la part de Todd
Envoyé : September-02-10 11:52 AM
À : mybatis-user
Objet : Parameter Problem inside a sub-query

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!
Reply | Threaded
Open this post in threaded view
|

Re: Parameter Problem inside a sub-query

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


On Thu, Sep 2, 2010 at 11:11 AM, Poitras Christian <[hidden email]> wrote:
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 : [hidden email] [mailto:[hidden email]] De la part de Todd
Envoyé : September-02-10 11:52 AM
À : mybatis-user
Objet : Parameter Problem inside a sub-query

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!

Reply | Threaded
Open this post in threaded view
|

RE: Parameter Problem inside a sub-query

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 : [hidden email] [mailto:[hidden email]] De la part de Todd Brady
Envoyé : September-02-10 12:26 PM
À : [hidden email]
Objet : Re: Parameter Problem inside a sub-query

 

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.

 

On Thu, Sep 2, 2010 at 11:11 AM, Poitras Christian <[hidden email]> wrote:

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 : [hidden email] [mailto:[hidden email]] De la part de Todd
Envoyé : September-02-10 11:52 AM
À : mybatis-user
Objet : Parameter Problem inside a sub-query


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!

 

Reply | Threaded
Open this post in threaded view
|

Re: Parameter Problem inside a sub-query

Todd
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.   :-)
 


 
On Thu, Sep 2, 2010 at 12:09 PM, Poitras Christian <[hidden email]> wrote:

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 : [hidden email] [mailto:[hidden email]] De la part de Todd Brady
Envoyé : September-02-10 12:26 PM
À : [hidden email]
Objet : Re: Parameter Problem inside a sub-query

 

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.

 

On Thu, Sep 2, 2010 at 11:11 AM, Poitras Christian <[hidden email]> wrote:

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 : [hidden email] [mailto:[hidden email]] De la part de Todd
Envoyé : September-02-10 11:52 AM
À : mybatis-user
Objet : Parameter Problem inside a sub-query


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!