4 Replies Latest reply on Oct 24, 2002 11:19 PM by satria

    Bug SQL statement generated by JBoss-CMR

    Wallace Zou Newbie

      JDK1.4 + JBoss3.0.0 + MySql

      I bought CMP2.0 doc two weeks ago, I wrote my xml files carefully following the doc's guide and examples. My case is simple, two tables:

      Office ( oid VARCHAR(4), name VARCHAR(30));
      Employee ( eid VARCHAR(8), ename VARCHAR(30), oid VARCHAR(4));

      I use oid as fk to establish relation bewteen office and employee. Hot deployment is cool and error-less. But I found a strange SQL statement in the log files:

      INSERT INTO employee (eid, oid, ename, oid) VALUES (?, ?, ?, ?)

      Also when I run the client try to add employee to office it prompt a exception:
      -----------------------------------------------------
      20:44:32,500 ERROR [Employee] Could not create entity
      java.sql.SQLException: General error: Column 'oid' specified twice
      -----------------------------------------------------

      I tried several times to change the jbosscmp-jdbc.xml to find out the ill elements. Finally when I change the column name of fk in relationship mapping section, the SQL statement is changed as well, so I guess the problem is here. I attached the three main xml file with a part of the log file. Any comments and advices are very very welcome!

      I am appreciated your reply! Thanks.

      a13519

        • 2. Re: Bug SQL statement generated by JBoss-CMR
          Dain Sundstrom Master

          You can't have a CMP field and CMR field mapped to the same column. Remove the following from the Employee entity and it should start working: [pre]
          <cmp-field>
          <field-name>oid</field-name>
          <column-name>oid</column-name>
          </cmp-field>
          [/pre]

          • 3. Re: Bug SQL statement generated by JBoss-CMR
            Wallace Zou Newbie

            Thanks, right now it is working properly.

            • 4. Re: Bug SQL statement generated by JBoss-CMR
              satria Newbie

              hi, i'm having the same problem too, and i have tried some possible combination changin the jbossjdbc-cmp.xml, and the sql statement in log file changed, but still error (10:08:39,708 ERROR [ProductInsEJB] Could not create entity java.sql.SQLException: Column not found: Unknown column 'thePrincipal' in 'field list'),
              here are my descriptors and log file:

              server.log:
              2002-10-25 10:08:39,588 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Executing SQL: SELECT COUNT(*) FROM tbl_prod_ins WHERE prod_ins_id=?
              2002-10-25 10:08:39,698 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Executing SQL: INSERT INTO tbl_prod_ins (prod_ins_id, invst_typ_id, principal_id, prod_ins_nm, thePrincipal, investmentType) VALUES (?, ?, ?, ?, ?, ?)
              2002-10-25 10:08:39,708 ERROR [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ProductInsEJB] Could not create entity
              java.sql.SQLException: Column not found: Unknown column 'thePrincipal' in 'field list'
              at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)

              ejb-jar.xml:

              ProductIns CMP EJB
              ....
              <cmp-version>2.x</cmp-version>
              <abstract-schema-name>ProductIns</abstract-schema-name>
              <cmp-field><field-name>productInsId</field-name></cmp-field>
              <cmp-field><field-name>investmentTypeId</field-name></cmp-field>
              <cmp-field><field-name>principalId</field-name></cmp-field>
              <cmp-field><field-name>productInsName</field-name></cmp-field>
              ....

              <ejb-relation>
              <ejb-relation-name>ProductIns-InvestmentType</ejb-relation-name>
              <ejb-relationship-role>
              <ejb-relationship-role-name>ProductIns-typeof-InvestmentType</ejb-relationship-role-name>
              Many
              <cascade-delete />
              <relationship-role-source>
              <ejb-name>ProductInsEJB</ejb-name>
              </relationship-role-source>
              <cmr-field>
              <cmr-field-name>investmentType</cmr-field-name>
              <cmr-field-type>java.util.Collection</cmr-field-type>
              </cmr-field>
              </ejb-relationship-role>

              <ejb-relationship-role>
              <ejb-relationship-role-name>InvestmentType-has-ProductIns</ejb-relationship-role-name>
              One
              <relationship-role-source>
              <ejb-name>InvestmentTypeEJB</ejb-name>
              </relationship-role-source>
              </ejb-relationship-role>
              </ejb-relation>

              <ejb-relation>
              <ejb-relation-name>ProductIns-ThePrincipal</ejb-relation-name>
              <ejb-relationship-role>
              <ejb-relationship-role-name>ProductIns-owned-by-ThePrincipal</ejb-relationship-role-name>
              Many
              <cascade-delete />
              <relationship-role-source>
              <ejb-name>ProductInsEJB</ejb-name>
              </relationship-role-source>
              <cmr-field>
              <cmr-field-name>thePrincipal</cmr-field-name>
              <cmr-field-type>java.util.Collection</cmr-field-type>
              </cmr-field>
              </ejb-relationship-role>

              <ejb-relationship-role>
              <ejb-relationship-role-name>ThePrincipal-has-ProductIns</ejb-relationship-role-name>
              One
              <relationship-role-source>
              <ejb-name>ThePrincipalEJB</ejb-name>
              </relationship-role-source>
              </ejb-relationship-role>
              </ejb-relation>

              jbossjdbc-cmp.xml:

              <ejb-name>ProductInsEJB</ejb-name>
              <table-name>tbl_prod_ins</table-name>
              <cmp-field><field-name>productInsId</field-name><column-name>prod_ins_id</column-name></cmp-field>
              <cmp-field><field-name>investmentTypeId</field-name><column-name>invst_typ_id</column-name></cmp-field>
              <cmp-field><field-name>principalId</field-name><column-name>principal_id</column-name></cmp-field>
              <cmp-field><field-name>productInsName</field-name><column-name>prod_ins_nm</column-name></cmp-field>



              <ejb-relation>
              <ejb-relation-name>ProductIns-InvestmentType</ejb-relation-name>
              <foreign-key-mapping/>
              <ejb-relationship-role-name>ProductIns-typeof-InvestmentType</ejb-relationship-role-name>
              <key-fields>
              <key-field>
              <!--<field-name>invst_typ_id</field-name>
              <column-name>invst_typ_id</column-name>-->
              </key-field>
              </key-fields>

              <ejb-relationship-role-name>InvestmentType-has-ProductIns</ejb-relationship-role-name>
              <key-fields>
              </key-fields>
              </ejb-relation>

              <ejb-relation>
              <ejb-relation-name>ProductIns-ThePrincipal</ejb-relation-name>
              <foreign-key-mapping/>
              <ejb-relationship-role-name>ProductIns-owned-by-ThePrincipal</ejb-relationship-role-name>
              <key-fields>
              <key-field>
              <!--<field-name>principal_id</field-name>
              <column-name>principal_id</column-name>-->
              </key-field>
              </key-fields>

              <ejb-relationship-role-name>ThePrincipal-has-ProductIns</ejb-relationship-role-name>
              <key-fields>
              </key-fields>
              </ejb-relation>


              .satria.