Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » NamedQuery generates invalid SQL(NamedQuery Question)
NamedQuery generates invalid SQL [message #552619] Thu, 12 August 2010 21:17 Go to next message
Eberto is currently offline Eberto
Messages: 2
Registered: August 2010
Junior Member
I have a NamedQuery, which I believe is correct in terms of syntax. However, whenever I execute it, I get a MySQL exception that the SQL is invalid (which it is). I was wondering if it looks like I am doing something wrong with my named query.

@NamedQuery(name = "Container.getFilteredAnnotationsForContainer",
    query = "SELECT a FROM Annotation a "
            + "JOIN a.annotationGeometryList ag "
            + "JOIN ag.geometryPrimitiveList gp  "
            + "WHERE a.container.id=:containerId "
            + "AND gp "
            + "IN(SELECT c.geometryPrimitive FROM Coordinate c WHERE c.latitude "
            + "BETWEEN :minLat AND :maxLat AND c.longitude BETWEEN :minLong AND :maxLong )")


Results in the following exception:

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (SELECT t4.id, t4.extrude, t4.annotation_geometry_id, t4.altitude_mode_id, t4' at line 1
Error Code: 1064
Call: SELECT t1.id, t1.endTimeSpan, t1.beginTimeSpan, t1.importedTimestamp, t1.display_type_id, t1.container_id, t1.icon_id FROM annotation_geometry t3, geometry_primitive t2, annotation t1, container t0 WHERE (((t0.id = ?) AND  IN (SELECT t4.id, t4.extrude, t4.annotation_geometry_id, t4.altitude_mode_id, t4.geometry_type_id FROM coordinate t5 LEFT OUTER JOIN geometry_primitive t4 ON (t4.id = t5.geometry_primitive_id) WHERE ((t5.latitude BETWEEN ? AND ?) AND (t5.longitude BETWEEN ? AND ?)))) AND (((t0.id = t1.container_id) AND (t3.annotation_id = t1.id)) AND (t2.annotation_geometry_id = t3.id)))
Re: NamedQuery generates invalid SQL [message #552817 is a reply to message #552619] Fri, 13 August 2010 15:58 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1019
Registered: July 2009
Senior Member
Hello,

I'm surprised you are not getting a JPQL processing exception, as the JPA specification only allows string, numeric, date, time, timestamp, or enum values for IN, not entities. Try using the id field from geometryPrimitive instead, ie:
"..AND gp.id IN(SELECT c.geometryPrimitive.id FROM .."

Best Regards,
Chris
Re: NamedQuery generates invalid SQL [message #552836 is a reply to message #552817] Fri, 13 August 2010 16:53 Go to previous message
Eberto is currently offline Eberto
Messages: 2
Registered: August 2010
Junior Member
Thanks for the advice, that solved my problem. For some reason that requirement wasn't clear to me.
Previous Topic:Join table with constant value column
Next Topic:An unexpected error > EXCEPTION_ACCESS_VIOLATION
Goto Forum:
  


Current Time: Tue Sep 16 15:41:12 GMT 2014

Powered by FUDForum. Page generated in 0.01931 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software