Skip to main content

[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.



Back to the top