aspose file tools*
The moose likes EJB and other Java EE Technologies and the fly likes sql conversion to ql for Java/J2EE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "sql conversion to ql for Java/J2EE" Watch "sql conversion to ql for Java/J2EE" New topic
Author

sql conversion to ql for Java/J2EE

chuck keuper
Greenhorn

Joined: Mar 17, 2004
Posts: 22
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
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
Sorry, you are out of luck. This will never convert to EJB-QL since EJB-QL does not support subqueries.
chuck keuper
Greenhorn

Joined: Mar 17, 2004
Posts: 22
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
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql conversion to ql for Java/J2EE
 
Similar Threads
Same old pproblem.. a bit urgent
stuck up in the problem
How to Upload a image or video where it will be attached or keep in email?
Upload and Download files just like attachments in JSP
Array Index Out Of Bounds