[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
[eclipselink-users] Wrong SQL from EJBQL
|
Hi,
I have the following problem. I try to execute this EJBQL query: (on MySQL)
Query query = super.entityManager.createQuery(
"delete from NodeEntity n " +
"where n.nodeId in " +
"(select c.childNodeEntity.nodeId from CachedNodeEntity c "
+
"where c.nodeEntity = :nodeEntity)");
Well, i try to delete all NodeEntity elements which are in the
CachedNodeEntity table where the parameter "nodeEntity" is the parent. I
think this is a legitimate piece of EJBQL?
unfortunately, EclipseLink tries to generate a temporary table and tries to
insert the "nodeId" in this table. It takes the "LIKE" statement and copies
the cw_node table. It inserts only the "nodeId" property, so all other
"not-null" field without a default are not copied, leading to this error.
CREATE TEMPORARY TABLE IF NOT EXISTS TL_cw_node LIKE cw_node
INSERT INTO TL_cw_node (node_id) SELECT t0.node_id FROM cw_node t0 WHERE
t0.node_id IN (SELECT t1.node_id FROM cw_cache_child_node t2, cw_node t1
WHERE (t2.node_id = ?))
bind => [404]
SELECT 1
DELETE FROM TL_cw_node
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0.2 (Build
20081024)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Field 'date_creation' doesn't
have a default value
Error Code: 1364
It works in TopLink. There the table is created (without the constraints).
But WHY does Eclipse/TopLink use a temporary table with ALL fields?
By the way, rewriting the query as:
Query query = super.entityManager.createQuery(
"delete from NodeEntity n " +
"where n in " +
"(select c.childNodeEntity from CachedNodeEntity c " +
"where c.nodeEntity = :nodeEntity)");
does NOT function. The generated EJBQL is invalid: it has " .... WHERE ( IN
(....)". It should be something like "WHERE t0 IN (....) ".
But hey, the SQL is very easy: "delete from cw_node where node_id in (select
c.child_node_id from cw_cache_child_node c where c.node_id = 11000)". Why
can't the persistence layers create this code?
Thank you
Jan
--
View this message in context: http://www.nabble.com/Wrong-SQL-from-EJBQL-tp20361408p20361408.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.