From the SQL, i have a feeling that somehow Toplink is not able to
detect that you are running against MySQL. Try explicitly specifying the
target-databsae to MySQL4
(
http://www.oracle.com/technology/products/ias/toplink/JPA/essentials/toplink-jpa-extensions.html#TopLinkDBSessionAppServer)
glassfish_at_javadesktop.org wrote:
> Thanks so much for the response. I'm obviously still getting used to all of this. I tried your suggestion, but there still seems to be a problem with the syntax. The query I'm using is as you suggested with comparing entity instances:
>
> @NamedQuery(name = "findObjsByElem", query = "SELECT w FROM MyObj w LEFT JOIN w.elem3 x WHERE (w.elem1 = :elem) OR (w.elem2 = :elem) OR ((x IS NOT NULL) AND (x = :elem))")
>
> But the error I get is:
>
> Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-\
> fcs (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
> Internal Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have a\
> n error in your SQL syntax; check the manual that corresponds to your MySQL server \
> version for the right syntax to use near '* null) AND (23 = t1.ITEMID)))' at line 1
> Error Code: 1064
> Call: SELECT t0.ITEMID, t0.NAME, t0.ELEM1_ITEMID, t0.ELEM2_ITEMID, t0.ELEM3_ITEMID FROM MyObj t0 LEFT OUTER JOIN ElemObj t1 ON (t1.ITEMID = t0.ELEM3_ITEMID) WHERE (((t0.ELEM1_ITEMID = ?) OR (t0.ELEM2_ITEMID = ?)) OR (NOT (t0.ELEM3_ITEMID =* ?) AND (? = t1.ITEMID)))
> bind => [23, 23, null, 23]
>
>
> I just took a guess at this and changed the query to:
>
> @NamedQuery(name = "findObjsByElem", query = "SELECT w FROM MyObj w LEFT JOIN w.elem3 x WHERE (w.elem1 = :elem) OR (w.elem2 = :elem) OR (x = :elem)")
>
> From a short test, this seems to be working, though I'm out of time right now and will have to test it more later.
>
> Thanks so much to both of you for your help!
>
> Renee
> [Message sent by forum member 'drrevis' (drrevis)]
>
> http://forums.java.net/jive/thread.jspa?messageID=286256
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>
>