7 Replies Latest reply on Jun 2, 2004 3:10 AM by pockey

    column "XXX" of relation "YYY" does not exist with CMR, JBOS

    pockey Newbie

      Hi

      I'm new to CMR. I tried to create a one-to-many relationship between CompanyBean entity bean and UserGroupBean entity bean by XDoclet.
      I'm using JBOSS3.2.3 and Postgresql 7.4.2.

      Without creating the relationship between them, I was able to insert records into Postgresql database by both beans successfully.

      After I specified the relationship, I was able to create records in the database from the CompanyBean.
      However, when I tried to create a record by UserGroupBean, I was not able to create any record in the database.
      I received the following error message all the time.

      15:33:07,030 ERROR [UserGroup] Could not create entity
      org.postgresql.util.PSQLException: ERROR: column "company" of relation "usergroup" does not exist

      at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
      at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
      at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
      at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
      at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
      at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:324)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.executeInsert(JDBCAbstractCreateCommand.java:326)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(JDBCAbstractCreateCommand.java:287)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:138)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
      at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)...


      It seems like JBOSS tried to persist the data with column "company" in usergroup table, because UserGroupBean has the following methods.

      public abstract CompanyLocal getCompany();
       public abstract void setCompany(CompanyLocal companyLocal);
      


      I also looked at jbosscmp-jdbc.xml, the <cmp-field>s of UserGroup do not contains "company" field. <--I think this is how it should be.

      I think I must have specified something wrong, and I don't think I need to build a column for the relationship because of the error message.
      I just needed to create a foreign key constraint, and to specify correct Xdoclet tags. Please correct me, if I'm wrong.

      Could anyone guide me to solve this problem please. I have not been able to solve this problem for days.

      Thanks you very much
      Kay

      PS: Part of my codes and database structure are shown below.


      The Database structure of the two relationships are below

      Table "public.usergroup"
      Column | Type | Modifiers
      ----------------+------+-----------
      usergroupid | text | not null
      companyid | text |
      modifiedbyuser | text |
      Indexes:
      "pk_usergroup" primary key, btree (usergroupid)
      Foreign-key constraints:
      "company" FOREIGN KEY (companyid) REFERENCES company(companyid)




      Table "public.company"
      Column | Type | Modifiers
      -------------------+----------------+-----------
      companyid | charprimarykey | not null
      modifiedbyuser | charprimarykey |
      companypreference | charprimarykey |
      Indexes:
      "company_pkey" primary key, btree (companyid)



      List of domains
      Schema | Name | Type | Modifier
      --------+------------------+-----------------------------+----------
      public | charprimarykey | character(32) |




      CompanyBean.java contains

      /**
       * @ejb.bean name = "Company"
       * type = "CMP"
       * cmp-version = "2.x"
       * display-name = "CompanyBean"
       * description = "Company EJB"
       * view-type = "both"
       * jndi-name = "ejb/CompanyBeanHome"
       * local-jndi-name = "ejb/CompanyBeanLocalHome"
       * primkey-field = "companyID"
       * schema="companyItem"
       *
       * @ejb.persistence table-name="company"
       *
       * @ejb.ejb-ref ejb-name="UserGroup"
       * view-type="local"
       * ref-name="UserGroupLocalHome"
       * @jboss.ejb-ref-jndi ref-name = "UserGroupLocalHome"
       * jndi-name = "ejb/UserGroupBeanLocalHome"
       * @ejb:util
       * generate="physical"
       */
      public abstract class CompanyBean implements EntityBean {
      
       private CompanyDetailsLocalHome companyDetailsLocalHome;
      
       /** The EntityContext */
       private EntityContext context;
      
       /**
       * @ejb.create-method
       */
       public String ejbCreate(CompanyFullDetailsValue companyFullDetailsValue) throws CreateException {
       //Set the values for this company table
       setCompanyID(CompanyUtil.generateGUID(this));
       setModifiedByUser(companyFullDetailsValue.getModifiedByUser());
       setCompanyPreference(companyFullDetailsValue.getCompanyPreference());
       return getCompanyID();
       }
      
       public void ejbPostCreate(CompanyFullDetailsValue companyFullDetailsValue) throws CreateException {
       }
       public void ejbActivate() throws EJBException {
       }
       public void ejbPassivate() throws EJBException {
       }
       public void ejbLoad() throws EJBException {
       }
       public void ejbStore() throws EJBException {
       }
       public void ejbRemove() throws EJBException, RemoveException {
       }
       public void setEntityContext(EntityContext newContext) throws EJBException {
       context = newContext;
       }
       public void unsetEntityContext() throws EJBException {
       context = null;
       }
      
       /**
       * Returns the companyID
       * @return the companyID
       *
       * @ejb.persistence
       * column-name="companyID"
       * sql-type="character(32)"
       * @ejb.pk-field
       * @ejb.interface-method
       */
       public abstract String getCompanyID();
      
       /**
       * Sets the companyID
       *
       * @param java.lang.String the new companyID value
       *
       * @ejb.interface-method
       */
       public abstract void setCompanyID(String companyID);
      
       /**
       * Returns the person who modified the record of the company
       * @return userID
       *
       * @ejb.persistent-field
       * @ejb.persistence
       * column-name="modifiedByUser"
       * sql-type="character(32)"
       *
       * @ejb.interface-method
       *
       */
       public abstract String getModifiedByUser();
      
       /**
       * Sets the person who modified company record
       *
       * @param java.lang.String userID of the user
       *
       * @ejb.interface-method
       */
       public abstract void setModifiedByUser(String userID);
      
       /**
       * Returns the preference fo the company
       * @return java.lang.String companyPreferenceID
       *
       * @ejb.persistence
       * column-name="companypreference"
       * sql-type="character(32)"
       *
       * @ejb.interface-method
       *
       */
       public abstract String getCompanyPreference();
      
       /**
       * Sets the person who modified company record
       *
       * @param java.lang.String userID value
       *
       * @ejb.interface-method
       */
       public abstract void setCompanyPreference(String PreferenceID);
      
       /**
       * @return Returns the collection of the local interface of UserGroupBean
       * @ejb.interface-method
       * @ejb.relation name="Company-UserGroup"
       * role-name="CompanyHasUserGroups"
       */
       public abstract Collection getUserGroup();
      
       /**
       * @param the collectino of the local interface of UserGroupBean
       * @ejb.interface-method
       */
       public abstract void setUserGroup(Collection userGroupLocal);
      }




      UserGroupBean.java contains

      /**
       * @ejb.bean name = "UserGroup"
       * type = "CMP"
       * cmp-version = "2.x"
       * display-name = "UserGroup"
       * description = "UserGroup EJB"
       * view-type = "both"
       * jndi-name = "ejb/UserGroupBeanHome"
       * local-jndi-name = "ejb/UserGroupBeanLocalHome"
       * primkey-field = "userGroupID"
       * schema="UserGroupItem"
       *
       * @ejb.persistence table-name="usergroup"
      
       * @ejb.ejb-ref ejb-name="Company"
       * view-type="local"
       * ref-name="CompanyBeanLocalHome"
       * @jboss.ejb-ref-jndi ref-name = "CompanyBeanLocalHome"
       * jndi-name = "ejb/CompanyBeanLocalHome"
       * @ejb:util
       * generate="physical"
       */
      public abstract class UserGroup implements EntityBean {
      
       private UserGroupDetailsLocalHome userGroupDetailsLocalHome;
      
       /** The EntityContext */
       private EntityContext context;
      
       /**
       * @ejb.create-method
       */
       public String ejbCreate(UserGroupFullDetailsValue userGroupFullDetailsValue) throws CreateException {
      
       setUserGroupID(UserGroupUtil.generateGUID(this));
       setCompanyID(userGroupFullDetailsValue.getCompanyID());
       setModifiedByUser(userGroupFullDetailsValue.getModifiedByUser());
       userGroupFullDetailsValue.setUserGroupID(getUserGroupID());
      
       return getUserGroupID();
       }
       public void ejbPostCreate(UserGroupFullDetailsValue userGroupFullDetailsValue) throws CreateException {
       }
       public void ejbActivate() throws EJBException {
       }
       public void ejbPassivate() throws EJBException {
       }
       public void ejbLoad() throws EJBException {
       }
       public void ejbStore() throws EJBException {
       }
       public void ejbRemove() throws EJBException, RemoveException {
       }
       public void setEntityContext(EntityContext newContext) throws EJBException {
       context = newContext;
       }
       public void unsetEntityContext() throws EJBException {
       context = null;
       }
      
       /**
       * @return Returns the userGroupID.
       * @ejb.persistence column-name="usergroupid"
       * sql-type="character(32)"
       * @ejb.pk-field
       * @ejb.interface-method
       */
       public abstract String getUserGroupID();
      
       /**
       * @param userGroupID The userGroupID to set.
       * @ejb.interface-method
       */
       public abstract void setUserGroupID(String userGroupID);
      
       /**
       * @return Returns the companyID.
       * @ejb.interface-method
       * @ejb.persistence column-name="companyID"
       * sql-type="character(32)"
       */
       public abstract String getCompanyID();
      
       /**
       * @param companyID The companyID to set.
       */
       public abstract void setCompanyID(String companyID);
      
       /**
       * @return Returns the ModifiedByUser.
       * @ejb.interface-method
       * @ejb.persistence column-name="modifiedbyuser"
       * sql-type="character(32)"
       */
       public abstract String getModifiedByUser();
      
       /**
       * @param ModifiedByUser The ModifiedByUser to set.
       * @ejb.interface-method
       */
       public abstract void setModifiedByUser(String modifiedByUser);
      
       /**
       * @return Returns the local interface of CompanyBean
       * @ejb.interface-method
       * @ejb.relation name="Company-UserGroup"
       * role-name="UserGroupBelongsToACompany"
       * @jboss.relation fk-constraint="false"
       * related-pk-field="companyID"
       * fk-column="companyID"
       */
       public abstract CompanyLocal getCompany();
      
      
       /**
       * @param the local interface of CompanyBean
       * @ejb.interface-method
       */
       public abstract void setCompany(CompanyLocal companyLocal);
      



      ejb-jar.xml contains
       <entity >
       <description><![CDATA[Company EJB]]></description>
       <display-name>CompanyBean</display-name>
      
       <ejb-name>Company</ejb-name>
      
       <home>com.j2ee.app.cmp.interfaces.CompanyHome</home>
       <remote>com.j2ee.app.cmp.interfaces.Company</remote>
       <local-home>com.j2ee.app.cmp.interfaces.CompanyLocalHome</local-home>
       <local>com.j2ee.app.cmp.interfaces.CompanyLocal</local>
      
       <ejb-class>com.j2ee.app.cmp.ejb.CompanyCMP</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.String</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>companyItem</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[Returns the companyID]]></description>
       <field-name>companyID</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Returns the person who modified the record of the company]]></description>
       <field-name>modifiedByUser</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[Returns the preference fo the company]]></description>
       <field-name>companyPreference</field-name>
       </cmp-field>
       <primkey-field>companyID</primkey-field>
      
       <ejb-local-ref >
       <ejb-ref-name>UserGroupLocalHome</ejb-ref-name>
       <ejb-ref-type>Entity</ejb-ref-type>
       <local-home>com.j2ee.app.cmp.interfaces.UserGroupLocalHome</local-home>
       <local>com.j2ee.app.cmp.interfaces.UserGroupLocal</local>
       <ejb-link>UserGroup</ejb-link>
       </ejb-local-ref>
      ...
      ...
      
       <entity >
       <description><![CDATA[UserGroup EJB]]></description>
       <display-name>UserGroup</display-name>
      
       <ejb-name>UserGroup</ejb-name>
      
       <home>com.j2ee.app.cmp.interfaces.UserGroupHome</home>
       <remote>com.j2ee.app.cmp.interfaces.UserGroup</remote>
       <local-home>com.j2ee.app.cmp.interfaces.UserGroupLocalHome</local-home>
       <local>com.j2ee.app.cmp.interfaces.UserGroupLocal</local>
      
       <ejb-class>com.j2ee.app.cmp.ejb.UserGroupCMP</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.String</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>UserGroupItem</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>userGroupID</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>companyID</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>modifiedByUser</field-name>
       </cmp-field>
       <primkey-field>userGroupID</primkey-field>
      
       <ejb-local-ref >
       <ejb-ref-name>CompanyBeanLocalHome</ejb-ref-name>
       <ejb-ref-type>Entity</ejb-ref-type>
       <local-home>com.j2ee.app.cmp.interfaces.CompanyLocalHome</local-home>
       <local>com.j2ee.app.cmp.interfaces.CompanyLocal</local>
       <ejb-link>Company</ejb-link>
       </ejb-local-ref>
      
      
      ...
      ...
      
      
       <ejb-relation >
       <ejb-relation-name>Company-UserGroup</ejb-relation-name>
      
       <ejb-relationship-role >
       <ejb-relationship-role-name>UserGroupBelongsToACompany</ejb-relationship-role-name>
       <multiplicity>Many</multiplicity>
       <relationship-role-source >
       <ejb-name>UserGroup</ejb-name>
       </relationship-role-source>
       <cmr-field >
       <cmr-field-name>company</cmr-field-name>
       </cmr-field>
       </ejb-relationship-role>
      
       <ejb-relationship-role >
       <ejb-relationship-role-name>CompanyHasUserGroups</ejb-relationship-role-name>
       <multiplicity>One</multiplicity>
       <relationship-role-source >
       <ejb-name>Company</ejb-name>
       </relationship-role-source>
       <cmr-field >
       <cmr-field-name>userGroup</cmr-field-name>
       <cmr-field-type>java.util.Collection</cmr-field-type>
       </cmr-field>
       </ejb-relationship-role>
      
       </ejb-relation>
      




      jbosscmp-jdbc.xml contains
       ...
       <ejb-relation>
       <ejb-relation-name>Company-UserGroup</ejb-relation-name>
      
       <ejb-relationship-role>
       <ejb-relationship-role-name>UserGroupBelongsToACompany</ejb-relationship-role-name>
       <fk-constraint>false</fk-constraint>
       <key-fields/>
      
       </ejb-relationship-role>
       <ejb-relationship-role>
       <ejb-relationship-role-name>CompanyHasUserGroups</ejb-relationship-role-name>
       <key-fields>
       <key-field>
       <field-name>companyID</field-name>
       <column-name>companyID</column-name>
       </key-field>
       </key-fields>
      
       </ejb-relationship-role>
       </ejb-relation>
       ...
      




      jboss.xml contains
      <jboss>
      
       <enterprise-beans>
      ...
       <entity>
       <ejb-name>Company</ejb-name>
       <jndi-name>ejb/CompanyBeanHome</jndi-name>
       <local-jndi-name>ejb/CompanyBeanLocalHome</local-jndi-name>
       <ejb-local-ref>
       <ejb-ref-name>ejb/UserGroupLocalHome</ejb-ref-name>
       <jndi-name>ejb/UserGroupBeanLocalHome</jndi-name>
       </ejb-local-ref>
      
       <method-attributes>
       </method-attributes>
      
       </entity>
       <entity>
       <ejb-name>UserGroup</ejb-name>
       <jndi-name>ejb/UserGroupBeanHome</jndi-name>
       <local-jndi-name>ejb/UserGroupBeanLocalHome</local-jndi-name>
       <ejb-local-ref>
       <ejb-ref-name>ejb/CompanyBeanLocalHome</ejb-ref-name>
       <jndi-name>ejb/CompanyBeanLocalHome</jndi-name>
       </ejb-local-ref>
      
       <method-attributes>
       </method-attributes>
      
       </entity>
      ...
      
       </enterprise-beans>
      
       <resource-managers>
       </resource-managers>
      
      </jboss>
      


        • 1. Re: column
          ironbird Newbie

          Like you, I cannot see any errors in your descriptors. If you are sure there is no cmp-field in jbosscmp-jdbc.xml named "company", we are facing up to a big mystery ?

          Activate cmp engine traces in log4j.xml to see what happens:
          Simply adds:

           <category name="org.jboss.ejb.plugins.cmp">
           <priority value="TRACE"/>
           </category>
          

          in the appropriate section.


          • 2. Re: column
            Alexey Loubyansky Master

            It should be

             <category name="org.jboss.ejb.plugins.cmp">
             <!-- <priority value="DEBUG"/> -->
             <priority value="TRACE" class="org.jboss.logging.XLevel"/>
             </category>
            


            • 3. Re: column
              Mark Miller Newbie

              I think you're problem may be that you have a cmp-field of companyId and a cmr-field of company both trying to use the same companyID column of the table. I am not sur eif this is allowed, I am using cmr with postgres and xdoclet and the only major difference between my code and yours is that I do not have cmp-fields for the foreign key columns.

              • 4. Re: column
                ironbird Newbie

                No millerm1, you can do that without any problem, I use it myself when I want to create entity beans which have non null foreign keys (setting up a dummy value in ejbCreate, the settting-up the cmr field in ejbPostCreate)

                • 5. Re: column
                  pockey Newbie

                  Hi

                  Thanks you very much for your help.

                  The problem is solved. Now it does not show the error message.
                  The solution that I took was

                  1. to take out

                  public abstract String getCompanyID()
                  public abstract void setCompanyID(String companyID).

                  because of the conflict between CMP-fiend and CMR-field like Millerm1 suggested.

                  2. I changed the name of the method


                  public abstract CompanyLocal getCompany()
                  public abstract void setCompany(CompanyLocal companyLocal);

                  to

                  public abstract CompanyLocal getCompanyID()
                  public abstract void setCompanyID(CompanyLocal companyLocal);

                  Without changing the name from "Company" to "CompanyID", I received an error message that "company" table does not exist.

                  3. Set the companyID from ejbPostCreate()

                  public void ejbPostCreate(CompanyLocal company, UserGroupValue userGroupValue) throws CreateException {
                   setCompanyID(company);
                  }
                  

                  It looks like specifying


                  * @jboss.relationfk-constraint="true"
                  * related-pk-field="companyID"
                  * fk-column="companyID"


                  is not enough to tell JBOSS that companyID is the foreign key column.

                  I still don't understand why I need to change, in this case,


                  public abstract CompanyLocal getCompany()

                  to

                  public abstract CompanyLocal getCompanyID()


                  when XDOCLET is specify the foreign key column already.

                  I looked at some sample codes. The names of their set and get methods of CMR do not match the name of the foreign key columns. But in my case, I need to make it matched.




                  • 6. Re: column
                    Alexey Loubyansky Master

                    The source code from your original post worked for me.

                     UserGroupUtil.getLocalHome().create("1", "2", "3");
                     CompanyUtil.getLocalHome().create("1", "2", "3");
                    


                    10:16:46,020 DEBUG [UserGroup] Executing SQL: SELECT COUNT(*) FROM USERGROUP WHERE usergroupid=?
                    10:16:46,020 DEBUG [UserGroup] Executing SQL: INSERT INTO USERGROUP (usergroupid, companyID, modifiedbyuser) V
                    ALUES (?, ?, ?)
                    10:16:46,040 DEBUG [Company#findByPrimaryKey] Executing SQL: SELECT t0_Company.companyID FROM COMPANY t0_Compa
                    ny WHERE t0_Company.companyID=?
                    10:16:46,040 DEBUG [Company] Executing SQL: SELECT COUNT(*) FROM COMPANY WHERE companyID=?
                    10:16:46,040 DEBUG [Company] Executing SQL: INSERT INTO COMPANY (companyID, modifiedByUser, companypreference)
                     VALUES (?, ?, ?)
                    


                    Am I missing something?

                    • 7. Re: column
                      pockey Newbie

                      If it worked for you, then it should not miss anything?

                      So far, I don't see that you miss anything.