7 Replies Latest reply on Jun 13, 2004 10:34 AM by pumuckel

    EJB-QL Finder method error

    pumuckel Newbie

      Hello

      I use JBoss-3.2.3.

      I have set up a many to many relationship:

      <ejb-relation>
       <ejb-relation-name>student_module_relation</ejb-relation-name>
       <ejb-relationship-role>
       <ejb-relationship-role-name>student-takes-modules</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source>
       <ejb-name>UserBean</ejb-name>
       </relationship-role-source>
       <cmr-field>
       <cmr-field-name>takenModules</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>module-is-taken-by-students</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source>
       <ejb-name>ModuleBean</ejb-name>
       </relationship-role-source>
       <cmr-field>
       <cmr-field-name>students</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
       </ejb-relation>
      


      I have also defined a query to get all students which are in a given module:
      <query>
       <query-method>
       <method-name>findByModule</method-name>
       <method-params>
       <method-param>moduledb.ejb.ModuleLocal</method-param>
       </method-params>
       </query-method>
       <ejb-ql>SELECT OBJECT(u) FROM User AS u WHERE ?1 MEMBER OF u.takenModules</ejb-ql>
      </query>
      


      I have not defined a table mapping for this relation.

      When I now invoke this finder method I get the error:
      19:53:41,664 INFO [PropertyMessageResources] Initializing, config='org.apache.struts.taglib.html.LocalStrings', returnNull=true
      19:53:43,156 ERROR [LogInterceptor] EJBException, causedBy:
      javax.ejb.FinderException: Find failed: java.sql.SQLException: General error, message from server: "Unknown table 't2_u_takenModules_RELATION_TABLE' in where clause"
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:238)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:111)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntitiesCommand.execute(JDBCFindEntitiesCommand.java:38)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntities(JDBCStoreManager.java:579)
       at org.jboss.ejb.plugins.CMPPersistenceManager.findEntities(CMPPersistenceManager.java:311)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntities(CachedConnectionInterceptor.java:322)
       at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:613)
      


      Can anybody help me or is this a JBoss bug, is there a workaround?

        • 1. Re: EJB-QL Finder method error
          gorano Newbie

          No bug. No workaround. You just have to get the syntax right.

          If you want someone to help you out, you have to list the related parts from both jboss.xml and jbosscmp-jdbc.xml.

          /G

          • 2. Re: EJB-QL Finder method error
            pumuckel Newbie

            Thanks for your fast reply.

            The jboss.xml:

            <?xml version="1.0" encoding="UTF-8"?>
            <jboss>
             <security-domain>java:/jaas/moduledb2</security-domain>
             <enterprise-beans>
             <session>
             <ejb-name>UserManager</ejb-name>
             <jndi-name>moduledb/UserManager</jndi-name>
             <ejb-local-ref>
             <ejb-ref-name>ejb/UserBean</ejb-ref-name>
             <jndi-name>moduledb/UserBean</jndi-name>
             </ejb-local-ref>
             <ejb-local-ref>
             <ejb-ref-name>ejb/ModuleBean</ejb-ref-name>
             <jndi-name>moduledb/ModuleBean</jndi-name>
             </ejb-local-ref>
             </session>
             <session>
             <ejb-name>Administration</ejb-name>
             <jndi-name>moduledb/Administration</jndi-name>
             <ejb-local-ref>
             <ejb-ref-name>ejb/UserBean</ejb-ref-name>
             <jndi-name>moduledb/UserBean</jndi-name>
             </ejb-local-ref>
             <ejb-local-ref>
             <ejb-ref-name>ejb/ModuleBean</ejb-ref-name>
             <jndi-name>moduledb/ModuleBean</jndi-name>
             </ejb-local-ref>
             </session>
             <session>
             <ejb-name>ModuleManager</ejb-name>
             <jndi-name>moduledb/ModuleManager</jndi-name>
             <ejb-local-ref>
             <ejb-ref-name>ejb/UserBean</ejb-ref-name>
             <jndi-name>moduledb/UserBean</jndi-name>
             </ejb-local-ref>
             <ejb-local-ref>
             <ejb-ref-name>ejb/ModuleBean</ejb-ref-name>
             <jndi-name>moduledb/ModuleBean</jndi-name>
             </ejb-local-ref>
             </session>
             <entity>
             <ejb-name>UserBean</ejb-name>
             <jndi-name>moduledb/UserBean</jndi-name>
             </entity>
             <entity>
             <ejb-name>ModuleBean</ejb-name>
             <jndi-name>moduledb/ModuleBean</jndi-name>
             </entity>
             <entity>
             <ejb-name>RatingBean</ejb-name>
             <jndi-name>moduledb/RatingBean</jndi-name>
             </entity>
             <entity>
             <ejb-name>FieldOfStudyBean</ejb-name>
             <jndi-name>moduledb/FieldOfStudyBean</jndi-name>
             </entity>
             </enterprise-beans>
            </jboss>
            


            jbosscmp-jdbc.xml:
            <?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN"
             "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">
            <jbosscmp-jdbc>
             <defaults>
             <datasource>java:/MySqlDS</datasource>
             <datasource-mapping>mySQL</datasource-mapping>
             <create-table>true</create-table>
             <remove-table>false</remove-table>
             <!--
             <datasource>java:/DefaultDS</datasource>
             <datasource-mapping>Hypersonic SQL</datasource-mapping>
             <create-table>true</create-table>
             <remove-table>false</remove-table>
            -->
             </defaults>
             <enterprise-beans>
             <entity>
             <ejb-name>ModuleBean</ejb-name>
             <cmp-field>
             <field-name>description</field-name>
             <!--column-name>DESCRIPTION</column-name-->
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>content</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>content</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>literature</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>objectives</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>remarks</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>activityConfirmation</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>learnType</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             <cmp-field>
             <field-name>link</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             </entity>
             <entity>
             <ejb-name>FieldOfStudyBean</ejb-name>
             <cmp-field>
             <field-name>description</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             </entity>
             <entity>
             <ejb-name>RatingBean</ejb-name>
             <cmp-field>
             <field-name>comment</field-name>
             <jdbc-type>VARCHAR</jdbc-type>
             <sql-type>TEXT</sql-type>
             </cmp-field>
             </entity>
             </enterprise-beans>
            </jbosscmp-jdbc>
            
            


            • 3. Re: EJB-QL Finder method error
              Alexey Loubyansky Master

              What tables do you have in the database?
              You have create-table=true so if you enable DEBUG log level for org.jboss.ejb.plugins.cmp.jdbc (have a look at server/default/conf/log4j.xml), you will see DDL and SQL executed.

              • 4. Re: EJB-QL Finder method error
                pumuckel Newbie

                I have go tables for the entity beans and the relationship.
                But no table "t2_u_takenModules_RELATION_TABLE" is created.
                The table created from jboss for this relation is called "Module_taken_Student_takes".

                Is there a possibility that i can specifiy the sql query by my own instead letting it create via QL?

                • 5. Re: EJB-QL Finder method error
                  Alexey Loubyansky Master

                  What query is generated/executed?
                  You could use declared-sql in jbosscmp-jdbc.xml. It's covered in CMP docs. But this is a configuration problem.

                  • 6. Re: EJB-QL Finder method error
                    pumuckel Newbie

                    Sorry that I waited that long for an answer (I did not notice your answer loubyansky)

                    The output in DEBUG mode is as follows :

                    2004-06-12 23:00:52,057 DEBUG [org.apache.struts.action.RequestProcessor] Looking for Action instance for class moduledb.actions.ModStudAction
                    2004-06-12 23:00:52,057 DEBUG [org.apache.struts.action.RequestProcessor] Creating new Action instance
                    2004-06-12 23:00:52,067 TRACE [org.jboss.system.Registry] lookup -1275511181=jboss.j2ee:jndiName=moduledb/ModuleManager,service=EJB
                    2004-06-12 23:00:52,067 TRACE [org.jboss.system.Registry] lookup -1275511181=jboss.j2ee:jndiName=moduledb/ModuleManager,service=EJB
                    2004-06-12 23:00:52,077 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.ModuleBean#findByPrimaryKey] Executing SQL: SELECT id FROM ModuleBean WHERE id=?
                    2004-06-12 23:00:52,107 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.ModuleBean] Executing SQL: SELECT name, description, ectsCredits, content, grade, link, literature, objectives, semester, abbreviation, remarks, activityConfirmation, examType, learnType FROM ModuleBean WHERE (id=?)
                    2004-06-12 23:00:52,207 TRACE [org.jboss.system.Registry] lookup -1275511181=jboss.j2ee:jndiName=moduledb/ModuleManager,service=EJB
                    2004-06-12 23:00:52,207 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.ModuleBean#findByPrimaryKey] Executing SQL: SELECT id FROM ModuleBean WHERE id=?
                    2004-06-12 23:00:52,237 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserBean#findByModule] Executing SQL: SELECT t0_u.id FROM UserBean t0_u WHERE ((t2_u_takenModules_RELATION_TABLE.UserBean IS NOT NULL AND t2_u_takenModules_RELATION_TABLE.ModuleBean=?))
                    2004-06-12 23:00:52,297 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.UserBean#findByModule] Find failed
                    java.sql.SQLException: General error, message from server: "Unknown table 't2_u_takenModules_RELATION_TABLE' 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)
                    


                    Thanks for any help in advance

                    • 7. Re: EJB-QL Finder method error
                      pumuckel Newbie

                      I think I solved the problem.
                      I used a MySQL 4.0.15 DB. This DB does not support subqueries.
                      MySQL 4.1.x should do it, but at this is at the moment at alpha stage.
                      I tried it with MSSQL DB and it worked.

                      Does anybody know if Postgres does support subqueries?