NamedQuery generates invalid SQL [message #552619] |
Thu, 12 August 2010 21:17 |
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 |
Chris Delahunt Messages: 1389 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
|
|
|
|
Powered by
FUDForum. Page generated in 0.03285 seconds