[JBoss-dev] [ jboss-Bugs-529329 ] IN expression in EJB-QL

[<no...@so...>]

Bugs item #529329, was opened at 2002-03-13 03:26
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=529329&group_id=22866

Category: JBossCMP
Group: v3.0 Rabbit Hole
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: Erik Turesson (eritu)
>Assigned to: Dain Sundstrom (dsundstrom)
Summary: IN expression in EJB-QL 

Initial Comment:
If you specify a EJB-QL query like this.

<query>
<description></description>
<query-method>
<method-name>findByImage</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT DISTINCT OBJECT(a) FROM PublBean AS a, 
IN(a.images) img WHERE img.id = ?1</ejb-ql>
</query>

The following SQL is generated.

SELECT DISTINCT t0_a.id 
FROM PublBean t0_a, ImageBean t1_img 
WHERE t1_img.id = ? AND 
(t0_a.id=t2_a_images_RELATION_TABLE.PublBean AND
t1_img.id=t2_a_images_RELATION_TABLE.ImageBean)

It generats the following errors:

2002-03-12 20:21:45,333 DEBUG 
[org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.PublBean
.findByImage] Executing SQL: SELECT DISTINCT t0_a.id 
FROM PublBean t0_a, ImageBean t1_img WHERE t1_img.id 
= ? AND (t0_a.id=t2_a_images_RELATION_TABLE.PublBean 
AND t1_img.id=t2_a_images_RELATION_TABLE.ImageBean)
2002-03-12 20:21:45,343 DEBUG 
[org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.PublBean
.findByImage] Find failed
java.sql.SQLException: Column not found: PUBLBEAN in 
statement [SELECT DISTINCT t0_a.id FROM PublBean t0_a, 
ImageBean t1_img WHERE t1_img.id = 1 AND 
(t0_a.id=t2_a_images_RELATION_TABLE.PublBean AND 
t1_img.id=t2_a_images_RELATION_TABLE.ImageBean)]
at org.hsqldb.Trace.getError(Trace.java:180)
at org.hsqldb.Result.<init>(Result.java:175)
at org.hsqldb.jdbcConnection.executeHSQL
(jdbcConnection.java:907)
at org.hsqldb.jdbcConnection.execute
(jdbcConnection.java:718)
at org.hsqldb.jdbcStatement.fetchResult
(jdbcStatement.java:686)
at org.hsqldb.jdbcStatement.executeQuery
(jdbcStatement.java:68)
at org.hsqldb.jdbcPreparedStatement.executeQuery
(jdbcPreparedStatement.java:133)
at 
org.jboss.resource.adapter.jdbc.local.PreparedStatement
InPool.executeQuery(PreparedStatementInPool.java:862)
at 
org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand
.execute(JDBCAbstractQueryCommand.java:107)
at 
org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.
execute(JDBCFindEntitiesCommand.java:40)
at 
org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEnt
ities(JDBCStoreManager.java:396)
at 
org.jboss.ejb.plugins.CMPPersistenceManager.findEntitie
s(CMPPersistenceManager.java:320)
at org.jboss.ejb.EntityContainer.findLocal
(EntityContainer.java:581)
at java.lang.reflect.Method.invoke(Native Method)
at 
org.jboss.ejb.EntityContainer$ContainerInterceptor.invo
keHome(EntityContainer.java:1059)
at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome
(AbstractInterceptor.java:73)
at 
org.jboss.ejb.plugins.EntitySynchronizationInterceptor.
invokeHome(EntitySynchronizationInterceptor.java:222)
at 
org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeH
ome(EntityInstanceInterceptor.java:134)
at 
org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome
(EntityLockInterceptor.java:79)
at 
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
(AbstractTxInterceptor.java:98)
at 
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransacti
ons(TxInterceptorCMT.java:167)
at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome
(TxInterceptorCMT.java:52)
at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome
(SecurityInterceptor.java:102)
at org.jboss.ejb.plugins.LogInterceptor.invokeHome
(LogInterceptor.java:109)
at org.jboss.ejb.EntityContainer.invokeHome
(EntityContainer.java:468)
at 
org.jboss.ejb.plugins.local.BaseLocalContainerInvoker.i
nvokeHome(BaseLocalContainerInvoker.java:243)
at 
org.jboss.ejb.plugins.local.BaseLocalContainerInvoker$H
omeProxy.invoke(BaseLocalContainerInvoker.java:367)
at $Proxy41.findByImage(Unknown Source)
at 
com.proventek.publ.image.implementation.ImageCollection
Bean.deleteImage(ImageCollectionBean.java:426)
at java.lang.reflect.Method.invoke(Native Method)
at 
org.jboss.ejb.StatelessSessionContainer$ContainerInterc
eptor.invoke(StatelessSessionContainer.java:642)
at 
org.jboss.ejb.plugins.StatelessSessionInstanceIntercept
or.invoke(StatelessSessionInstanceInterceptor.java:77)
at 
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
(AbstractTxInterceptor.java:96)
at 
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransacti
ons(TxInterceptorCMT.java:167)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke
(TxInterceptorCMT.java:61)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke
(SecurityInterceptor.java:127)
at org.jboss.ejb.plugins.LogInterceptor.invoke
(LogInterceptor.java:166)
at org.jboss.ejb.StatelessSessionContainer.invoke
(StatelessSessionContainer.java:308)
at org.jboss.ejb.Container.invoke(Container.java:681)
at com.sun.management.jmx.MBeanServerImpl.invoke
(MBeanServerImpl.java:1555)
at com.sun.management.jmx.MBeanServerImpl.invoke
(MBeanServerImpl.java:1523)
at org.jboss.invocation.local.LocalInvoker.invoke
(LocalInvoker.java:98)
at org.jboss.invocation.InvokerInterceptor.invoke
(InvokerInterceptor.java:102)
at org.jboss.proxy.TransactionInterceptor.invoke
(TransactionInterceptor.java:73)
at org.jboss.proxy.SecurityInterceptor.invoke
(SecurityInterceptor.java:76)
at 
org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke
(StatelessSessionInterceptor.java:111)
at org.jboss.proxy.ClientContainer.invoke
(ClientContainer.java:96)
at $Proxy38.deleteImage(Unknown Source)
at org.apache.jsp.afix_0005fimages$jsp._jspService
(afix_0005fimages$jsp.java:321)
at org.apache.jasper.runtime.HttpJspBase.service
(HttpJspBase.java:107)
at javax.servlet.http.HttpServlet.service
(HttpServlet.java:853)
at 
org.apache.jasper.servlet.JspServlet$JspServletWrapper.
service(JspServlet.java:202)
at org.apache.jasper.servlet.JspServlet.serviceJspFile
(JspServlet.java:382)
at org.apache.jasper.servlet.JspServlet.service
(JspServlet.java:474)
at javax.servlet.http.HttpServlet.service
(HttpServlet.java:853)
at org.mortbay.jetty.servlet.ServletHolder.handle
(ServletHolder.java:326)
at org.mortbay.jetty.servlet.ServletHandler.handle
(ServletHandler.java:572)
at org.mortbay.http.HttpContext.handle
(HttpContext.java:1356)
at org.mortbay.http.HttpContext.handle
(HttpContext.java:1309)
at org.mortbay.http.HttpServer.service
(HttpServer.java:744)
at org.jboss.jetty.Jetty.service(Jetty.java:540)
at org.mortbay.http.HttpConnection.service
(HttpConnection.java:743)
at org.mortbay.http.HttpConnection.handleNext
(HttpConnection.java:916)
at org.mortbay.http.HttpConnection.handle
(HttpConnection.java:758)
at org.mortbay.http.SocketListener.handleConnection
(SocketListener.java:145)
at org.mortbay.util.ThreadedServer.handle
(ThreadedServer.java:287)
at org.mortbay.util.ThreadPool$JobRunner.run
(ThreadPool.java:715)
at java.lang.Thread.run(Thread.java:484)


The entity and relation declarations looks like this.

 <entity>
      <display-name>PublBean</display-name>
      <ejb-name>PublBean</ejb-name>
      <local-
home>com.proventek.publ.publication.internalinterfaces.
PublLocalHome</local-home>
      
<local>com.proventek.publ.publication.internalinterface
s.PublLocal</local>
      <ejb-
class>com.proventek.publ.publication.implementation.Pub
lBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Integer</prim-key-
class>
      <reentrant>False</reentrant>
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>PublBean</abstract-schema-
name>
      <cmp-field>
        <field-name>name</field-name>
      </cmp-field>
      <cmp-field>
        <field-name>description</field-name>
      </cmp-field>
      <cmp-field>
        <field-name>anonymousAllowed</field-name>
      </cmp-field>
      <cmp-field>
        <field-name>id</field-name>
      </cmp-field>
      <primkey-field>id</primkey-field>

      <query>
        <description></description>
        <query-method>
          <method-name>findAll</method-name>
            <method-params />
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
PublBean AS a</ejb-ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByName</method-name>
            <method-params>
            <method-param>java.lang.String</method-
param>
             </method-params>
        </query-method>
         <ejb-ql>SELECT DISTINCT OBJECT(p) FROM 
PublBean p WHERE p.name = ?1</ejb-ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByPublT</method-name>
            <method-params>
            <method-param>java.lang.Integer</method-
param>
             </method-params>
        </query-method>
         <ejb-ql>SELECT DISTINCT OBJECT(pa) FROM 
PublishedArticleBean pa WHERE pa.publ.id = ?1</ejb-ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByArtT</method-name>
            <method-params>
            <method-param>java.lang.Integer</method-
param>
             </method-params>
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
PublBean AS a WHERE a.artT.id = ?1</ejb-ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByImage</method-name>
             <method-params>
             <method-param>java.lang.Integer</method-
param>
             </method-params>
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
PublBean AS a, IN(a.images) img WHERE img.id = ?1</ejb-
ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByFile</method-name>
             <method-params>
             <method-param>java.lang.Integer</method-
param>
             </method-params>
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
PublBean AS a, IN(a.files) fil WHERE fil.id = ?1</ejb-
ql>
      </query>


     </entity>


<entity>
     <ejb-name>ImageBean</ejb-name>
      <local-
home>com.proventek.publ.image.internalinterfaces.ImageL
ocalHome</local-home>
      
<local>com.proventek.publ.image.internalinterfaces.Imag
eLocal</local>
      <ejb-
class>com.proventek.publ.image.implementation.ImageBean
</ejb-class>
     <persistence-type>Container</persistence-type>
     <prim-key-class>java.lang.Integer</prim-key-class>
     <reentrant>False</reentrant>
     <cmp-version>2.x</cmp-version>
     <abstract-schema-name>ImageBean</abstract-schema-
name>
     <cmp-field>
          <field-name>name</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>photographer</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>description</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>imageDate</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>smallImage</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>mediumImage</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>largeImage</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>smallImageContentType</field-
name>
     </cmp-field>
     <cmp-field>
          <field-name>mediumImageContentType</field-
name>
     </cmp-field>
     <cmp-field>
          <field-name>largeImageContentType</field-
name>
     </cmp-field>
     <cmp-field>
          <field-name>hasSmallImage</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>hasMediumImage</field-name>
     </cmp-field>
     <cmp-field>
          <field-name>hasLargeImage</field-name>
     </cmp-field>
      <cmp-field>
          <field-name>storeInDb</field-name>
      </cmp-field>
      <cmp-field>
          <field-name>id</field-name>
     </cmp-field>
     <primkey-field>id</primkey-field>
     <query>
        <description></description>
        <query-method>
          <method-name>findAll</method-name>
            <method-params />
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
ImageBean AS a</ejb-ql>
      </query>

      <query>
        <description></description>
        <query-method>
          <method-name>findByImageCategory</method-
name>
             <method-params>
             <method-param>java.lang.Integer</method-
param>
             </method-params>
        </query-method>
        <ejb-ql>SELECT DISTINCT  OBJECT(a) FROM 
ImageBean AS a,IN (a.imgCats) ic  WHERE ic.id = ?
1</ejb-ql>
      </query>

     <query>
        <description></description>
        <query-method>
          <method-name>findByName</method-name>
            <method-params>
            <method-param>java.lang.String</method-
param>
             </method-params>
        </query-method>
         <ejb-ql>SELECT DISTINCT OBJECT(p) FROM 
ImageBean AS p WHERE p.name= ?1</ejb-ql>
      </query>


</entity>

<ejb-relation>
<ejb-relation-name>Publ-Image</ejb-relation-name> 
<ejb-relationship-role>
<ejb-relationship-role-name>Publ-Has-Images</ejb-
relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>PublBean</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>images</cmr-field-name>
</cmr-field> 
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>Image-Has-Publs</ejb-
relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>ImageBean</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

This bug is already corrected by dsundstrom

----------------------------------------------------------------------

>Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-03-13 13:03

Message:
Logged In: YES 
user_id=251431

As noted, this was fixed yesterday.

----------------------------------------------------------------------

You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=529329&group_id=22866