0 Replies Latest reply on Nov 4, 2004 6:38 AM by Lame Guy

    HELP! CMR -- generated SQL doesn't look right...

    Lame Guy Newbie

      Hi, really frustating... been trying to get my CMR setup for last three days but without much success (shame).

      My setup is simple. I have three beans:
      1. UserBean
      2. GroupBean
      3. GroupMembershipBean

      Each User (UserBean) can belong to multiple Group(s) (GroupBean).

      1. UserBean maps to table USERS, table schema:
      Primary key: UIN
      Other fields: login, email, ... etc

      2. GroupBean maps to table GROUPS, table schema:
      Primary key: UIN
      Other fields: name, decription, ... etc

      3. GroupMembershipBean maps to table GROUPMEMBERSHIP, table schema:
      Composite Primary Key: {GROUPUIN, USERUIN}
      Foreign key on USERUIN and GROUPUIN that points to table USERS and table GROUPS

      There's a One-To-Many relationship between GroupBean (One-side) and GroupMembership (Many-side).

      1. GroupBean:

      /**
       * @ejb.bean
       * name="GroupEJB"
       * local-jndi-name="ejb/UserGroup"
       * display-name="User Group EJB"
       * description="User Group EJB"
       * cmp-version="2.x"
       * type="CMP"
       * view-type="local"
       * schema="Group"
       * reentrant="false"
       * primkey-field="UIN"
       *
       * @ejb.persistence
       * table-name="GROUPS"
       *
       * @ejb.finder
       * query="SELECT OBJECT(g) FROM Group AS g"
       * signature="java.util.Collection findAllGroups()"
       *
       * @ejb.interface
       * local-extends="javax.ejb.EJBLocalObject, java.lang.Comparable, java.io.Serializable"
       * local-class="com.aa.samples.interfaces.GroupLocal"
       *
       * @ejb.home
       * local-class="com.aa.samples.interfaces.GroupLocalHome"
       *
       * @jboss.persistence
       * datasource="java:/jdbc/dev01"
       * datasource-mapping="mySQL"
       * create-table="false"
       * remove-table="false"
       */
      public abstract class GroupBean implements EntityBean, Serializable, Comparable {
       ... other stuff ...
       /**
       * @ejb.relation
       * name="GroupEJB-GroupMembershipEJB"
       * role-name="GroupEJB-has-GroupMembershipEJB"
       *
       * @ejb.interface-method
       */
       public abstract Collection getGroupMembershipBean();
       public abstract void setGroupMembershipBean(Collection memberships);
       ... other stuff ...
      }
      


      2. GroupMembershipBean:
      /**
       *
       * @ejb.bean
       * name="GroupMembershipEJB"
       * local-jndi-name="ejb/GroupMembership"
       * display-name="Group Membership EJB"
       * description="Group Membership Group EJB"
       * cmp-version="2.x"
       * type="CMP"
       * view-type="local"
       * schema="GroupMembership"
       * reentrant="false"
       *
       * @ejb.persistence
       * table-name="GROUPMEMBERSHIP"
       *
       * @ejb.pk
       * class="com.aa.samples.primarykeys.GroupMembershipKey"
       *
       * @ejb.interface
       * local-extends="javax.ejb.EJBLocalObject, java.lang.Comparable, java.io.Serializable"
       * local-class="com.aa.samples.interfaces.GroupMembershipLocal"
       *
       * @ejb.home
       * local-class="com.aa.samples.interfaces.GroupMembershipLocalHome"
       *
       * @jboss.persistence
       * datasource="java:/jdbc/dev01"
       * datasource-mapping="mySQL"
       * create-table="false"
       * remove-table="false"
       */
      public abstract class GroupMembershipBean implements EntityBean, Serializable, Comparable {
       ... other stuff ...
       /**
       * @ejb.persistent-field
       * @ejb.persistence
       * column-name="GROUPUIN"
       * sql-type="INTEGER"
       * @ejb.pk-field
       * @ejb.interface-method
       *
       */
       public abstract Integer getGroupUIN();
      
       /**
       * @ejb.interface-method
       */
       public abstract void setGroupUIN(Integer groupUIN);
      
       /**
       * @ejb.persistent-field
       * @ejb.persistence
       * column-name="USERUIN"
       * sql-type="INTEGER"
       * @ejb.pk-field
       * @ejb.interface-method
       */
       public abstract Integer getUserUIN();
      
       /**
       * @ejb.interface-method
       */
       public abstract void setUserUIN(Integer userUIN);
      
       /**
       * @ejb.relation
       * name="GroupEJB-GroupMembershipEJB"
       * role-name="GroupMembershipEJB-has-GroupEJB"
       * target-multiple="yes"
       * cascade-delete="yes"
       *
       * @jboss.relation
       * related-pk-field="UIN"
       * fk-column="GROUPUIN"
       *
       * @ejb.interface-method
       */
       public abstract GroupLocal getGroup();
      
       public abstract void setGroup(GroupLocal group);
       ... other stuff ...
      }
      


      I ran XDoclet, BOTH "ejb-jar.xml" and "jbosscmp-jdbc.xml" looks right:

      1. ""ejb-jar.xml"

      ... other stuff ...

      <ejb-relation >
      <ejb-relation-name>GroupEJB-GroupMembershipEJB</ejb-relation-name>

      <ejb-relationship-role >
      <ejb-relationship-role-name>GroupMembershipEJB-has-GroupEJB</ejb-relationship-role-name>
      Many
      <cascade-delete/>
      <relationship-role-source >
      <ejb-name>GroupMembershipEJB</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>group</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role >
      <ejb-relationship-role-name>GroupEJB-has-GroupMembershipEJB</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>GroupEJB</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>groupMembershipBean</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      </ejb-relation>


      ... other stuff ...

      2. "jbosscmp-jdbc.xml"
      ... other stuff ...

      <ejb-relation>
      <ejb-relation-name>GroupEJB-GroupMembershipEJB</ejb-relation-name>
      <ejb-relationship-role> <ejb-relationship-role-name>GroupMembershipEJB-has-GroupEJB</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>GroupEJB-has-GroupMembershipEJB</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>UIN</field-name>
      <column-name>GROUPUIN</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>

      ... other stuff ...


      The problem is,
      ...
      GroupLocal group=groupHome.findByPrimaryKey(groupUIN);
      Collection membershiplist=group.getGroupMembershipBean(); //EXCEPTION THROWN
      ...
      


      And the exception msg is:
      javax.ejb.TransactionRolledbackLocalException: Load relation failed; CausedByException is:
       Syntax error or access violation message from server: "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 'FROM GROUPMEMBERSHIP WHERE (GROUPUIN=1)' at line 1"; CausedByException is:
       Load relation failed; CausedByException is:
       Syntax error or access violation message from server: "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 'FROM GROUPMEMBERSHIP WHERE (GROUPUIN=1)' at line 1"
      


      I used p6spy (http://www.p6spy.com/) to intercept any SQL to database, and I found:

      1099567741656|-1||resultset|SELECT name, description, isSuspended FROM GROUPS WHERE (UIN='1')|description = Administrators Group, name = Administrators
      
      1099567755138|161|0|statement|SELECT FROM GROUPMEMBERSHIP WHERE (GROUPUIN=?)|SELECT FROM GROUPMEMBERSHIP WHERE (GROUPUIN='1')
      


      Notice on second line:
      SELECT FROM (GROUPUIN='1')
      instead of:
      SELECT * FROM (GROUPUIN=1)

      There're two things weird about this:
      a. SELECT * --- The "*" is missing - this is not present in error message when I stepped thru the code.
      b. GROUPUIN='1' --- why's there's single quote around '1'?

      Help! I've been staring at this for sometime now and it's getting desparate. HELP!