5 Replies Latest reply on Feb 20, 2002 1:29 AM by Thomas Phan

    JBossCMP2 Bug - Generated insert stmt incorrect

    Thomas Phan Newbie

      Having problem with JBoss 3 generated insert stmt, not sure why it generates "INSERT INTO ViewShortcut (username, line, resourcesProperties, url, username) VALUES (?, ?, ?, ?, ?)" when I execute:

      Collection c = viewSettingLocal.getViewShortcuts();
      c.add(getViewShortcutLocalHome().create(username, line, resourcesProperties, url));

      Note: it thinks that the table has two username columns
      ---
      Error:

      [Default] 2446400 [RMI TCP Connection(22)-] DEBUG org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ViewShortcut ViewFacade ViewShortcut - Executing SQL: SELECT COUNT(*) FROM ViewShortcut WHERE username=? AND line=?
      ...
      [Default] 2446412 [RMI TCP Connection(22)-] DEBUG org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.ViewShortcut ViewFacade ViewShortcut - Executing SQL: INSERT INTO ViewShortcut (username, line, resourcesProperties, url, username) VALUES (?, ?, ?, ?, ?)
      ...
      [ViewShortcut] Could not create entity
      java.sql.SQLException: General error: Column 'username' specified twice

      ---
      Background info:

      ViewSetting is the parent, and ViewShortcut is the child
      ViewSetting.username is the primary key for the parent table
      ViewShortcut.username and ViewShortcut.line for the child table
      ViewShortcut.username is the foreign key

      ---
      ejb-jar.xml


      <![CDATA[No Description.]]>
      <ejb-name>ViewSetting</ejb-name>
      <local-home>esb.interfaces.ViewSettingLocalHome</local-home>
      esb.interfaces.ViewSettingLocal
      <ejb-class>esb.ejb.ViewSettingCMP</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.String</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>ViewSetting</abstract-schema-name>
      <cmp-field>
      <![CDATA[]]>
      <field-name>username</field-name>
      </cmp-field>
      ...
      <primkey-field>username</primkey-field>



      <![CDATA[No Description.]]>
      <ejb-name>ViewShortcut</ejb-name>
      <local-home>esb.interfaces.ViewShortcutLocalHome</local-home>
      esb.interfaces.ViewShortcutLocal
      <ejb-class>esb.ejb.ViewShortcutCMP</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>esb.interfaces.ViewShortcutPK</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>ViewShortcut</abstract-schema-name>
      <cmp-field>
      <![CDATA[]]>
      <field-name>username</field-name>
      </cmp-field>
      <cmp-field>
      <![CDATA[]]>
      <field-name>line</field-name>
      </cmp-field>
      <cmp-field>
      <![CDATA[]]>
      <field-name>resourcesProperties</field-name>
      </cmp-field>
      <cmp-field>
      <![CDATA[]]>
      <field-name>url</field-name>
      </cmp-field>


      <ejb-relation>
      <ejb-relation-name>setting-shortcut</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-setting-has-many-shortcuts</ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>ViewSetting</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>viewShortcuts</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-shortcut-belongs-to-one-setting</ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>ViewShortcut</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      </ejb-relation>
      ---
      jbosscmp-jdbc.xml:


      <ejb-name>ViewShortcut</ejb-name>
      <table-name>ViewShortcut</table-name>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      <tuned-updates>true</tuned-updates>
      <read-only>false</read-only>
      <time-out>100</time-out>
      <cmp-field>
      <field-name>line</field-name>
      <column-name>line</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>resourcesProperties</field-name>
      <column-name>resourcesProperties</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>url</field-name>
      <column-name>url</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>username</field-name>
      <column-name>username</column-name>
      </cmp-field>


      <ejb-relation>
      <ejb-relation-name>setting-shortcut</ejb-relation-name>
      <foreign-key-mapping>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-setting-has-many-shortcuts</ejb-relationship-role-name>
      <foreign-key-fields/>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>one-shortcut-belongs-to-one-setting</ejb-relationship-role-name>
      <fk-constraint>false</fk-constraint>
      <foreign-key-fields>
      <foreign-key-field>
      <field-name>username</field-name>
      <column-name>username</column-name>
      </foreign-key-field>
      </foreign-key-fields>
      </ejb-relationship-role>
      </foreign-key-mapping>
      </ejb-relation>

      ---
      The extra username field may be created after JBoss reads <foreign-key-field/> in the jbosscmp-jdbc.xml

      Is this a JBoss bug or I misunderstand something?

      Thanks

        • 1. Re: JBossCMP2 Bug - Generated insert stmt incorrect
          Dain Sundstrom Master

          I just fixed some bugs int the autogeneration code for relationship keys. I wouldn't recomend updating to cvs as it is still slightly broken. In the mean time just full specify the column mapping of your relation keys.

          • 2. Re: JBossCMP2 Bug - Generated insert stmt incorrect
            Thomas Phan Newbie

            Thx Dain

            Where may I find the instructions to fully specify the column mapping of my relation keys?

            And should I watch for changes to JDBCCreateEntityCommand.java and SQLUtil.java in the CVS. When will u plan to update it?

            Thx very much :)

            • 3. Re: JBossCMP2 Bug - Generated insert stmt incorrect
              Thomas Phan Newbie

              Hi, I wonder if I can add a new CMR now, i.e. has the generated insert statement fixed yet?

              In addition, I saw:

              public void removeLineItem(long lineItemID) {
              Collection col = getLineItems();
              Iterator iter = col.iterator();
              while (iter.hasNext()) {
              LineItem li = (LineItem) iter.next();
              if (li.getId() == lineItemID) {
              iter.remove();
              return;
              }
              }
              }

              on http://javaboutique.internet.com/resources/books/EJB/ejb2_3.html

              and attempted to do the same thing in JBoss, i.e. deleting a child row using the CMR returned collection iterator's remove method. But JBoss gaves javax.ejb.RemoveException: java.lang.IllegalStateException

              From your JBossCMP-Docs, you use a finder method to get the collection (children) to be deleted, and call their interfaces' remove method to remove them one by one. Is it possible to remove all children without using a finder method? (in most cases, the get method returns the same rows as the finder method. However, if I apply the remove/clear method to the collection returned by get, JBoss just change their keys to 0/null. If I apply remove to the returned collection's elements, i.e. the local interface or the same element as the finder returns, I got some transaction error)

              Thanks

              • 4. Re: JBossCMP2 Bug - Generated insert stmt incorrect
                Georg Schmid Newbie


                The IllegalStateException is required by the EJB2.0 spec.

                Please read section 10.3.6.1. It states:

                "If elements are added or removed from the underlying
                container-managed collection used by an iterator other
                than by the java.util.Iterator.remove() method, the
                container should throw the java.lang.IllegalStateException
                on the next operation on the iterator."

                Do you call the iterator's remove() method (legal)
                or that of LineItem (illegal) ?

                Maybe you have to use the sequence (inside the loop)

                iter.remove();
                li.remove();

                • 5. Re: JBossCMP2 Bug - Generated insert stmt incorrect
                  Thomas Phan Newbie

                  Hi,

                  My code uses lterator.remove(), it works fine without looking at the data in the database. What I see is that JBoss sets my key (which relates to its parent table) to 0 or null; in this case, the record won't be queried out (since the parent-child doesn't have the relation), but it exists. What I want is to have the record to be deleted!

                  so I tried:

                  for (Iterator i = obj1Local.getObj2s().iterator(); i.hasNext();) {
                  obj2Local = (Obj2Local) i.next();
                  i.remove();
                  obj2Local.remove(); // this line causes javax.ejb.RemoveException: Could not remove entity
                  }

                  where obj1 is the parent, and obj2 is the child

                  without obj2Local.remove(), I cannot think of any code to delete the record; unless i.remove() does that (instead of setting the key)

                  Any suggestion? Thx