sql conversion to ql for Java/J2EE

[chuck keuper]

I have this sql statement that I am trying to convert to a ql statement.

This is the sql statement

SELECT * FROM Text
WHERE tagListId IN
(SELECT tagListId FROM TagList
WHERE startPos>=
(SELECT startPos FROM TagList WHERE tagDefTypId=25
AND startPos<=(SELECT startPos FROM TagList WHERE tagListId=821) AND
endPos>=(SELECT endPos FROM TagList WHERE tagListId=821))
AND endPos<=
(SELECT endPos FROM TagList WHERE tagDefTypId=25
AND startPos<=(SELECT startPos FROM TagList WHERE tagListId=821) AND
endPos>=(SELECT endPos FROM TagList WHERE tagListId=821))
)
I have converted it to this ql statment
SELECT OBJECT(tl) FROM TagListSchema tl, TextSchema t WHERE t.data LIKE ?1 AND tl.tagListId IN
(SELECT tlist.tagListId FROM TagListSchema tlist WHERE tlist.startPos>=
(SELECT tlst.startPos FROM TagListSchema tlst WHERE tlst.tagDefTypId=25
AND tlst.startPos<=(SELECT tagl.startPos FROM TagListSchema tagl WHERE
tagl.tagListId=821) AND tlst.endPos>=(SELECT tglst.endPos FROM
TagListSchema tglst WHERE tglst.tagListId=821)) AND
tlist.endPos<=(SELECT tgl.endPos FROM TagListSchema tgl WHERE
tgl.tagDefTypId=25 AND tgl.startPos<=(SELECT tlt.startPos FROM
TagListSchema tlt WHERE tlt.tagListId=821) AND tgl.endPos>=(SELECT
tglt.endPos FROM TagList tglt WHERE tglt.tagListId=821)))

My problem is the IN statement in sql. I try to put it in ql language and I receive errors stating that a string is exspected by an integer found. Error below

ejbcontainer: Throwing System exception for the container-started transaction for method: public abstract java.util.Collection doccontents.ContentsTagListRemoteHome.findByContentsData(java.lang.String) throws javax.ejb.FinderException,java.rmi.RemoteException
ejbcontainer: com.borland.ejb.ql.QueryError: [line 1, col 83] Operator IN expects a <String> type, but a <integer> type was found
ejbcontainer: at com.borland.ejb.ql.QueryError.raiseQueryError(QueryError.java:154)
ejbcontainer: at com.borland.ejb.ql.QueryError.expectedType(QueryError.java:49) ...

I have looked in O'reilly ejb book, suns site and they seem to have very simple examples. How do I apply this in operator???

[Chris Mathews]

Sorry, you are out of luck. This will never convert to EJB-QL since EJB-QL does not support subqueries.

[chuck keuper]

I found an article at http://info.borland.com/techpubs/bes/v6/html_books/developersguide/ejb-ql.html that talks about sun-queries.


Sub-Queries
Sub-queries are permitted as deep as the database implementation being queried allows. For example, you could use the following sub-query (in bold) specified in ejb-jar.xml. Note that the sub-query includes ORDER BY as well, and the results are to be returned in descending (DESC) order.

<query>
<query-method>
<method-name>findApStatisticsWithGreaterThanAverageValue</method-name>
<method-params />
</query-method>
<ejb-ql>SELECT Object(s1) FROM ApStatistics s1 WHERE s1.averageValue > SELECT AVG(s2.averageValue) FROM ApStatistics s2 ORDER BY s1.averageValue DESC</ejb-ql>
</query>
See your database implementation documentation for details on the appropriate use of sub-queries.

[Chris Mathews]

Borland Enterprise may very well support nested queries in EJB-QL. However, it is clearly stated in the EJB-QL grammer definition that sub-queries are not supported. Therefore, any solution using sub-queries in BES will be not portable to other Application Servers.