ejb-ql jboss-ql integration problem?

[jhammerle]

I'm trying to use a parameterized IN for a CMP bean, which I believe requires a jboss-ql. According to some posts, I've learned that you should leave your ejb-ql empty and write your query in the jboss-ql. But I get the following exception when I deploy my ear file to jboss-3.2.5:

javax.management.MBeanException: org.jboss.deployment.DeploymentException: Error
compiling EJB-QL statement ''; - nested throwable: (org.jboss.ejb.plugins.cmp.ejbql.ParseException: FROM not found)

Apparently it does not like the empty ejb-ql, but I thought this is how you do it. Anyone know what I am doing wrong? Or what I am not doing right?

Thanks in advance,
Jason

Below are snippets from my ejb-jar.xml and jbosscmp-jdbc.xml files for my entity bean:

ejb-jar.xml

<display-name>Listing</display-name>
<ejb-name>Listing</ejb-name>
com.dtnet.service.database.entity.ListingHome
com.dtnet.service.database.entity.Listing
<ejb-class>com.dtnet.service.database.entity.ListingBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Long</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Listing</abstract-schema-name>
<cmp-field><field-name>id</field-name></cmp-field>
<cmp-field><field-name>sellerId</field-name></cmp-field>
<cmp-field><field-name>categoryIdStr</field-name></cmp-field>
<cmp-field><field-name>title</field-name></cmp-field>
<cmp-field><field-name>zip</field-name></cmp-field>
<cmp-field><field-name>picture1</field-name></cmp-field>
<cmp-field><field-name>picture2</field-name></cmp-field>
<cmp-field><field-name>picture3</field-name></cmp-field>
<cmp-field><field-name>picture4</field-name></cmp-field>
<cmp-field><field-name>available</field-name></cmp-field>
<cmp-field><field-name>pending</field-name></cmp-field>
<cmp-field><field-name>sold</field-name></cmp-field>
<cmp-field><field-name>currPrice</field-name></cmp-field>
<primkey-field>id</primkey-field>

<query-method>
<method-name>findAvailable</method-name>
<method-params/>
</query-method>
<ejb-ql/>


<query-method>
<method-name>findByListingId</method-name>
<method-params>
<method-param>long</method-param>
</method-params>
</query-method>
<ejb-ql/>


<query-method>
<method-name>findByTitle</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql/>


<query-method>
<method-name>findByCategory</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql/>


<query-method>
<method-name>findByZip</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql/>


<query-method>
<method-name>findByCategoryAndZip</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql/>



jbosscmp-jdbc.xml

<ejb-name>Listing</ejb-name>
<table-name>listing</table-name>

<query-method>
<method-name>findAvailable</method-name>
<method-params/>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.available > 0
]]>
</jboss-ql>


<query-method>
<method-name>findByListingId</method-name>
<method-params>
<method-param>long</method-param>
</method-params>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.id = ?1
]]>
</jboss-ql>


<query-method>
<method-name>findByTitle</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.available > 0
AND a.title like ?1
]]>
</jboss-ql>


<query-method>
<method-name>findByCategory</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.available > 0
AND a.title like ?1
AND a.categoryIdStr in (?2)
]]>
</jboss-ql>


<query-method>
<method-name>findByZip</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.available > 0
AND a.title like ?1
AND a.zip in (?2)
]]>
</jboss-ql>


<query-method>
<method-name>findByCategoryAndZip</method-name>
<method-params>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<jboss-ql>
<![CDATA[
SELECT OBJECT(a)
FROM Listing as a
WHERE a.available > 0
AND a.title like ?1
AND a.categoryIdStr in (?2)
AND a.zip in (?3)
]]>
</jboss-ql>

[jijisv]

Specify the load strategy with the jboss-ql

for e.g. convert the above query as below.


<query-method>
<method-name>findAvailable</method-name>
<method-params/>
</query-method>
<jboss-ql><![CDATA[SELECT OBJECT(a) FROM Listing as a WHERE a.available > 0 ]]> </jboss-ql>
<read-ahead>
none
<page-size>500</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>

[jijisv]

"cc" wrote:
Specify the load strategy with the jboss-ql

for e.g. convert the above query as below.

<query>
<query-method>
<method-name>findAvailable</method-name>
<method-params/>
</query-method>
<jboss-ql><![CDATA[SELECT OBJECT(a) FROM Listing as a WHERE a.available > 0 ]]> </jboss-ql>
<read-ahead>
<strategy>none</strategy>
<page-size>500</page-size>
<eager-load-group>*</eager-load-group>
</read-ahead>
</query>

[timshaw]

My understanding is that you cannot use 'IN' in this way.

The below snippet works correctly (even with an empty ejb-ql) for me - the IN is used to say 'the given group is IN the set referenced by the object'. You appear to be using the SQL type-of IN.
I don't think your approach works ... but if it does, please let me know as I would LOVE to be able to send in a Collection and check whether a value of an object was in it!

<jboss-ql>
[CDATA[
SELECT OBJECT(o)
FROM MyEJB o, IN(o.groups) g
WHERE o.type = ?1 and g = ?2
]]
</jboss-ql>