EJB-QL syntax

[erikture]

Hello!

I have a query that is declared as bellow.
I have used in Jboss 3.0 alpha but now in Jboss 2.0 beta it seams to be invalid syntax.

<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 WHERE a.imgCats.id = ?1</ejb-ql>
</query>

When I try to deploy my beans I get the following error.
Unable to parse EJB-QL.

It seams that it is the were part that is causing the problem. If I remove it the query passes.

The imgCats attribute of my ImageBean is a on side of a many to many relation. The related object has a primary key named id and its type is Integer.

How shall I write my query to get it to pass?

/Erik Turesson

[dsundstrom]

SELECT DISTINCT OBJECT(a)
FROM ImageBean AS a
WHERE a.imgCats.id = ?1

Based on the name, I'm guessing the imgCats is a collection valued relationship. If that is true, the correct EJB-QL would be:

SELECT DISTINCT OBJECT(a)
FROM ImageBean AS a, IN(a.imgCats) AS cats
WHERE cats.id = ?1

The code in cvs HEAD includes a completely rewritten EJB-QL compiler which gives real error messages.

[erikture]

Thanks!

It did help writing the query as you suggested.
But still I have three queries that will not pass.
Is there somewhere a syntax sample for EJB-QL. All samples I have found are to "easy" they dont handle more complex queries.

This is my three reaminging queries.



<query-method>
<method-name>findByPublAndDate</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
<method-param>long</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT DISTINCT OBJECT(pa) FROM PublishedArticleBean AS pa WHERE pa.publ.id = ?1 AND pa.stopDate => ?2 AND ?2 => pa.startDate </ejb-ql>


I whant to return items where the ?2 parameter is between or equal to the pa.startDate and pa.stopDate attributes.




<query-method>
<method-name>findTopLevelContribs</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT DISTINCT OBJECT(a) FROM ContribBean AS a WHERE a.discus.id = ?1 AND a.parent.id IS NULL ORDER BY a.contribDate</ejb-ql>


I whant to return all items that has the related discus with the id of ?1 and whos parent attribute is null and order them by date.




<query-method>
<method-name>findUsersNotInAccessGroup</method-name>
<method-params>
<method-param>java.lang.Integer</method-param>
</method-params>
</query-method>
<ejb-ql>SELECT DISTINCT OBJECT(a) FROM UserBean AS a , IN(a.aG) ag WHERE ag.id IS NOT ?1</ejb-ql>


How do I query when I whant to return all Users that is do not have the ?1 id in its relations attribute aG.

/Eri Turesson

[dsundstrom]

&gt; Is there somewhere a syntax sample for EJB-QL. All
&gt; samples I have found are to "easy" they dont handle
&gt; more complex queries.

There are some in the EJB 2.0 spec, but not many. I would suggest that you take a closer look at the BNF, as most of your errors are simple syntax errors.

&gt; SELECT DISTINCT OBJECT(pa)
&gt; FROM PublishedArticleBean AS pa
&gt; WHERE pa.publ.id = ?1 AND
&gt; pa.stopDate =&gt; ?2 AND
&gt; ?2 =&gt; pa.startDate

The last and is illegal. A parameter is only allowed on the right hand side of an operator.

&gt; SELECT DISTINCT OBJECT(a)
&gt; FROM ContribBean AS a
&gt; WHERE a.discus.id = ?1 AND a.parent.id IS NULL
&gt; ORDER BY a.contribDate

Order by is only supported by JBossQL. What you need to do is copy the query section into your jbosscmp-jdbc.xml file and change the ejb-ql element to jboss-ql. Also remove the order by clause from the ejb-ql in the ejb-jar.xml file.

&gt; SELECT DISTINCT OBJECT(a)
&gt; FROM UserBean AS a , IN(a.aG) ag
&gt; WHERE ag.id IS NOT ?1

What the hell is this? Did you even read the spec, or even an article on EJB-QL? Try the not equal operator '&lt;&gt;'.

[erikture]

Thank you for your reply and excuse me if my ejb-ql code is not correct. The last of my three examples where most like seudo code when everything else did fail.

> > SELECT DISTINCT OBJECT(pa)
> > FROM PublishedArticleBean AS pa
> > WHERE pa.publ.id = ?1 AND
> > pa.stopDate => ?2 AND
> > ?2 => pa.startDate

I have tried to write this query like this:
<ejb-ql>SELECT DISTINCT OBJECT(pa) FROM PublishedArticleBean AS pa WHERE pa.publ.id = ?1 AND pa.stopDate >= ?2 AND pa.startDate <= ?2</ejb-ql>

The parser complains about the <= expression.

>
> > SELECT DISTINCT OBJECT(a)
> > FROM ContribBean AS a
> > WHERE a.discus.id = ?1 AND a.parent.id IS NULL
> > ORDER BY a.contribDate
>
When I put the jboss-ql in the jbosscmp-jdbc.xml it is OK, but the IS NULL condition is not ok. It is parsed ok but the result is not as I would expect. I whant to receive all items that do not have a parent related to them. In the java object the method getParent returns null. But if I write a.parent IS NULL it will not pass the parsing.

>
> > SELECT DISTINCT OBJECT(a)
> > FROM UserBean AS a , IN(a.aG) ag
> > WHERE ag.id IS NOT ?1
>
The obove was seudocode when everything else did fail.

The parser will not recognize <> as a correct operator.

/Erik Turesson

[dsundstrom]

Ok, you need to go buy a book that through covers EJB-QL.

> > > SELECT DISTINCT OBJECT(pa)
> > > FROM PublishedArticleBean AS pa
> > > WHERE pa.publ.id = ?1 AND
> > > pa.stopDate => ?2 AND
> > > ?2 => pa.startDate
>
> I have tried to write this query like this:
> <ejb-ql>SELECT DISTINCT OBJECT(pa) FROM
> (pa) FROM PublishedArticleBean AS pa WHERE pa.publ.id
> = ?1 AND pa.stopDate >= ?2 AND pa.startDate <=
> ?2</ejb-ql>
>
> The parser complains about the <= expression.

If start date is a Data type, then from the ejb spec we have:

datetime_value { = | <> | > | < } datetime_expression

> > > SELECT DISTINCT OBJECT(a)
> > > FROM ContribBean AS a
> > > WHERE a.discus.id = ?1 AND a.parent.id IS NULL
> > > ORDER BY a.contribDate
> >
> When I put the jboss-ql in the jbosscmp-jdbc.xml it
> is OK, but the IS NULL condition is not ok. It is
> parsed ok but the result is not as I would expect. I
> want to receive all items that do not have a parent
> related to them. In the java object the method
> getParent returns null. But if I write a.parent IS
> NULL it will not pass the parsing.

This is covered in the spec. Try 'a.parent IS NULL'. If you put a.parent.id then you assume that a.parent is not null. Also this will only work in beta 2 (currently only available via cvs), as beta 1 used the old bugy EJB-QL parser.

> > > SELECT DISTINCT OBJECT(a)
> > > FROM UserBean AS a , IN(a.aG) ag
> > > WHERE ag.id IS NOT ?1
> >
> The above was seudocode when everything else did
> fail.
>
> The parser will not recognize <> as a correct
> operator.

Is id a custom primary key (i.e., not a simple number or string)? If so you can not make a comparison involving a dependent value object (i.e., not a simple type) in EJB-QL. I would guess that you want the following:

SELECT DISTINCT OBJECT(a)
FROM UserBean AS a
WHERE a.aG IS EMPTY

Note: the above will only work correctly in beta 2.

[erikture]

> > The parser complains about the <= expression.
>
> If start date is a Data type, then from the ejb spec
> we have:
>
> datetime_value { = | <> | > | < }
> datetime_expression

startDate is a long.
>
> > > > SELECT DISTINCT OBJECT(a)
> > > > FROM ContribBean AS a
> > > > WHERE a.discus.id = ?1 AND a.parent.id IS NULL
>
> > > > ORDER BY a.contribDate
> > >
>
> This is covered in the spec. Try 'a.parent IS NULL'.
> If you put a.parent.id then you assume that a.parent
> is not null. Also this will only work in beta 2
> (currently only available via cvs), as beta 1 used
> the old bugy EJB-QL parser.

I quess I have to get the beta 2, because 'a.parent IS NULL does not work. It does not parse.
>
> > > > SELECT DISTINCT OBJECT(a)
> > > > FROM UserBean AS a , IN(a.aG) ag
> > > > WHERE ag.id IS NOT ?1
> > >
>
> Is id a custom primary key (i.e., not a simple number
> or string)? If so you can not make a comparison
> involving a dependent value object (i.e., not a
> simple type) in EJB-QL. I would guess that you want
> the following:
>
> SELECT DISTINCT OBJECT(a)
> FROM UserBean AS a
> WHERE a.aG IS EMPTY
>
> Note: the above will only work correctly in beta 2.
>
>
The primary key is an Integer and I want every object that does not have ?1 in its collection. I have tried something like IS NOT MEMBER but it will not pass eighter.
Beta 2 maybee?

/Erik