-
1. Re: CMR / EJB-QL problem?
aloubyansky Jun 19, 2004 5:01 AM (in response to martingx)What is the generated SQL? Isn't it using subquery? What SQL is generated qith SQL92 compiler?
-
2. Re: CMR / EJB-QL problem?
martingx Jun 19, 2004 6:28 AM (in response to martingx)I think its not using subquery because mysql 3.x doesn't support it.
Anyway, here is an example of the same problem (slightly more complicated example, but all I have at hand right now)
For this query, using using jboss with mysql 3.23.x:
DYNAMIC-QL: SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE (
(hierarchy.partyGroup IS NULL) OR (NOT (hierarchy.mainInternalHierarchy IS
NULL)) ) ORDER BY hierarchy.code
JBoss 3.2.1 did this (working):
SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
FROM cmp_hierarchy t0_hierarchy LEFT JOIN MainInternalHierarchy_Hierarchys t2_hierarchy_mainInternalHierarc ON t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK LEFT JOIN PartyGroup_Hierarchys t1_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK
WHERE ((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR (NOT (t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS NULL))) ORDER BY t0_hierarchy.CODE ASC
;
JBoss 3.2.4RC2 does this (doesn't fall over, but appears not correct):
SELECT DISTINCT t0_hierarchy.id, t0_hierarchy.CODE
FROM cmp_hierarchy t0_hierarchy, cmp_main_internal_hierarchy t3_hierarchy_mainInternalHierarc, maininternalhierarchy_hierarchys t2_hierarchy_mainInternalHierarc, cmp_party_group t4_hierarchy_partyGroup, partygroup_hierarchys t1_hierarchy_partyGroup_RELATION
WHERE (((t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK IS NULL) OR ( NOT (t2_hierarchy_mainInternalHierarc.HIERARCHY_FK IS
NULL))) AND t0_hierarchy.id=t2_hierarchy_mainInternalHierarc.HIERARCHY_FK AND t3_hierarchy_mainInternalHierarc.id=t2_hierarchy_mainInternalHierarc.MAIN_INTERNAL_HIERARCHY_FK AND t0_hierarchy.id=t1_hierarchy_partyGroup_RELATION.HIERARCHY_FK AND t4_hierarchy_partyGroup.id=t1_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK) ORDER BY t0_hierarchy.CODE ASC
;
I did also test with 3.2.4 final and it still didn't work. I can't be 100% sure its the same generated sql as for 3.2.4rc2 above because I don't have the output here right now.
I also don't have access to the output of the sql92 compiler. I will post it when I do. -
3. Re: CMR / EJB-QL problem?
aloubyansky Jun 20, 2004 5:10 AM (in response to martingx)Use SQL92 compiler. Current default compiler will be deprecated. The only reason sql92 compiler was not the default one was that HSQLDB (which is the default JBoss datasource) didn't support outer/inner joins and subqueries. Now it does. So sql92 compiler will be the default one.
-
4. Re: CMR / EJB-QL problem?
martingx Jun 22, 2004 4:51 AM (in response to martingx)using sql92 compiler generates:
SELECT t0_hierarchy.id, t0_hierarchy.CODE, t0_hierarchy.TOP_LEVEL_NAME FROM cmp_hierarchy t0_hierarchy LEFT OUTER JOIN partygroup_hierarchys t2_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t2_hierarchy_partyGroup_RELATION.HIERARCHY_FK LEFT OUTER JOIN cmp_party_group t1_hierarchy_partyGroup ON t1_hierarchy_partyGroup.id=t2_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK WHERE t1_hierarchy_partyGroup.HIERARCHY_FK IS NULL
which giives:
java.sql.SQLException: Column not found, message from server: "Unknown column 't1_hierarchy_partyGroup.HIERARCHY_FK' in 'where clause'"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2090)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1496)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:198)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCDynamicQLQuery.execute(JDBCDynamicQLQuery.java:138)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:587)
at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:285)
(etc)
It seems to think we are using foreign key but we are using relation table mapping. -
5. Re: CMR / EJB-QL problem?
martingx Jun 22, 2004 6:43 AM (in response to martingx)sorry, my last post was a slightly different query of:
SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE hierarchy.partyGroup IS NULL
but its the same issue. -
6. Re: CMR / EJB-QL problem?
aloubyansky Jun 23, 2004 4:19 AM (in response to martingx)Could you replace the SQL92 compiler in 3.2.4 with the current version in Branch_3_2? I think, this bug was fixed.
-
7. Re: CMR / EJB-QL problem?
martingx Jun 24, 2004 10:02 AM (in response to martingx)Are there daily builds of Branch_3_2 available?
Branch_3_2 from cvs fails to build for me and I won't have time this week to look into why.
Thanks for your help so far.
Regards,
Martin. -
8. Re: CMR / EJB-QL problem?
aloubyansky Jun 25, 2004 4:50 AM (in response to martingx)Here is what you need http://cvs.sourceforge.net/viewcvs.py/*checkout*/jboss/jboss/src/main/org/jboss/ejb/plugins/cmp/jdbc/EJBQLToSQL92Compiler.java?rev=1.6.2.6
What was your cvs command to check out? The correct one is
cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r Branch_3_2 jboss-3.2 -
9. Re: CMR / EJB-QL problem?
martingx Jun 25, 2004 6:14 AM (in response to martingx)thats the command I used.
I followed the FAQ and did:
cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r JBoss_3_2_3 jboss-3.2
cd jboss-3.2/build
build
(except i replaced the final "build" with "./build.sh"
and got:
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/tools/ant/launch/Launcher
A quick look at build.sh shows it messing with my ANT_HOME and I don't really know what its trying to do.
I could do with help compiling though because I'm also trying to look at a different possible bug in dynamic-ql -
10. Re: CMR / EJB-QL problem?
martingx Jun 25, 2004 6:16 AM (in response to martingx)sorry - ignore my typo. the checkout command i used was exactly the one you gave, not the one from the FAQ.
-
11. Re: CMR / EJB-QL problem?
martingx Jun 25, 2004 6:17 AM (in response to martingx)sorry - ignore my typo. the checkout command i used was exactly the one you gave, not the one from the FAQ.
-
12. Re: CMR / EJB-QL problem?
aloubyansky Jun 28, 2004 4:33 AM (in response to martingx)You should be able to build JBoss w/o any additional setup steps if the right command was used and check out went ok. The Ant version which is distributed with JBoss should be used.
-
13. Re: CMR / EJB-QL problem?
martingx Jun 28, 2004 10:11 AM (in response to martingx)okay, i deleted everything and tried again in case I did something wrong before.
here is _exactly_ what I did:
[gartonm@dev JBOSS_CVS]$ cvs -z3 -d:pserver:anonymous@cvs.sourceforge.net:/cvsroot/jboss co -r Branch_3_2 jboss-3.2
[*snipped long cvs output - checkout went okay*]
[gartonm@dev JBOSS_CVS]$ cd jboss-3.2/build/
[gartonm@dev build]$ ./build.sh
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/tools/ant/launch/Launcher
[gartonm@dev build]$
There must be something else I need to do?
Is there a jboss developer newbie list or somewhere I can ask? -
14. Re: CMR / EJB-QL problem?
martingx Jun 28, 2004 11:46 AM (in response to martingx)I got jboss to build by ignoring the FAQ and just using my own installed version of ant.
So I tested again using the sql92 compiler with todays CVS version of Branch_3_2 and got the same result. Here is the log.
The reason it can't find "t1_hierarchy_partyGroup.HIERARCHY_FK" is because we are using <preferred-relation-mapping>relation-table</preferred-relation-mapping> in jbosscmp-jdbc.xml
16:34:29,018 DEBUG [Hierarchy#findDynamic] DYNAMIC-QL: SELECT OBJECT(hierarchy) FROM Hierarchy hierarchy WHERE hierarchy.partyGroup IS NULL
16:34:29,020 DEBUG [Hierarchy#findDynamic] Executing SQL: SELECT t0_hierarchy.id, t0_hierarchy.CODE, t0_hierarchy.TOP_LEVEL_NAME FROM cmp_hierarchy t0_hierarchy LEFT OUTER JOIN partygroup_hierarchys t2_hierarchy_partyGroup_RELATION ON t0_hierarchy.id=t2_hierarchy_partyGroup_RELATION.HIERARCHY_FK LEFT OUTER JOIN cmp_party_group t1_hierarchy_partyGroup ON t1_hierarchy_partyGroup.id=t2_hierarchy_partyGroup_RELATION.PARTY_GROUP_FK WHERE t1_hierarchy_partyGroup.HIERARCHY_FK IS NULL
16:34:29,025 DEBUG [Hierarchy#findDynamic] Find failed
java.sql.SQLException: Column not found, message from server: "Unknown column 't1_hierarchy_partyGroup.HIERARCHY_FK' in 'where clause'"