1 2 Previous Next 18 Replies Latest reply on Mar 21, 2003 11:51 AM by reddyn

    duplicate columns created - revisited

      I'm having a problem where JBossCMP is creating (somehow successfully in Hypersonic DB!) duplicate column names. This is using JBoss 3.0.0beta2 from CVS (pulled down late last week) on a Red Hat 7.1 Linux, kernel 2.4.9-31,i686 box using Sun JDK 1.3.1_02.

      I have a bidirectional one-2-many relationship between Groups and Images.
      My jbosscmp-jdbc.xml has this so I can specify my own column names:

      <enterprise-beans>

      <ejb-name>Group</ejb-name>
      <table-name>GROUPS</table-name>
      <cmp-field>
      <field-name>groupKey</field-name>
      <column-name>GRP_KEY</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>ownerKey</field-name>
      <column-name>OWN_KEY</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>name</field-name>
      <column-name>NAME</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>description</field-name>
      <column-name>DESCRIP</column-name>
      </cmp-field>


      <ejb-name>Image</ejb-name>
      <table-name>IMAGES</table-name>
      <cmp-field>
      <field-name>imageKey</field-name>
      <column-name>IMG_KEY</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>groupKey</field-name>
      <column-name>GRP_KEY</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>name</field-name>
      <column-name>NAME</column-name>
      </cmp-field>
      <cmp-field>
      <field-name>description</field-name>
      <column-name>DESCRIP</column-name>
      </cmp-field>

      </enterprise-beans>


      <ejb-relation>
      <ejb-relation-name>Group-Image</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Group-Has-Images</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>groupKey</field-name>
      <column-name>GRP_KEY</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>Image-Has-Group</ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>


      What I end up with is an IMAGES table with two columns called GRP_KEY!!! (I didn't think a DB would allow this ?!?!) This is the debug in the server.log:

      2002-03-25 13:24:23,535 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.Image] Executing SQL: CREATE TABLE IMAGES (IMG_KEY INTEGER NOT NULL, GRP_KEY INTEGER, NAME VARCHAR(256), DESCRIP VARCHAR(256), GRP_KEY INTEGER, CONSTRAINT pk_IMAGES PRIMARY KEY (IMG_KEY))

      $$ I bought the JBossCMP documentation from Flashline and noticed:

      1) It uses the Alpha dtd for jbosscmp-jdbc.xml, using the old foreign-key-fields and ejb-relationship-role inside a foreign-key-mapping. Also the foreign-key-field entity placement is reversed, again a change from alpha to beta.

      2) The classic Order-LineItem relationship (which is the bidirectional one-2-many I'm emulating in my problem) is different from mine in that it doesn't define the LineItemEJB entity mapping in the jbosscmp-jdbc.xml file - but I want to define my own column names.

      By looking at the SQL CREATE statement, my gut feeling is that this is a bug, but I'm not sure.

      Thanks for any and all help,
      David

      PS: As a side question, why can't I / don't I have to specify the key-field for imageKey in the Image-Has-Group relationship?

        • 1. Re: duplicate columns created - revisited

          Thanks to the help of Jason R., my problem has been resolved. Basically, there was a flaw in my design. I didn't need a cmp groupKey field in my Image entity since it was already being set up as a cmr field - thus the duplication. I didn't need an image.getGroupKey() as now I can just do image.getGroup().getKey(). Still trying to shake off my bad EJB 1.1 habits for a more object-like world in EJB 2.0.

          As an aside, I still don't know why Hypersonic would allow two columns of the same name and type to coexist in the same table...

          • 2. Re: duplicate columns created - revisited
            xopher

            I have run into a similar scenario where the same column name was being set in create-table SQL script for a CMP entity bean. The problem seemed to be related to having a field that is defined both as a persistent field for the bean and as a foreign-key in an ejb-relation. If you do not define the groupKey as a persistent field in the Image ejb, do you still get the same behavior? This worked for me. In my case, I wanted to have the foreign-key accessible to the bean, but could not find a way to configure jboss to make this work correctly. Perhaps this is bad practice or something that is not supported in the ejb-spec. When I created the table myself jboss would start up, but I encountered cases where the foreign-key would be set to null. Looking in the log, the insert statement would correctly insert a row in the table, but then the following SQL statement would set the foreign-key to null. Hope this helps...

            • 3. Re: duplicate columns created - revisited

              Yes, not having groupKey as both a cmp field and a foreting-key worked for me.

              I guess if you really wanted to expose the foreign key you could just add a business method that grabs the foreign-related object's key and returns it.

              • 4. Re: duplicate columns created - revisited
                laha

                Thank You. After have read this thread I finally could deploy some simple entity beans with some simple relations.


                I you can not have cmr-columns as cmp-fields how will this work with weblogic and other "ejb 2.0 standard comforming" products?

                I am going to try..............

                • 5. Re: duplicate columns created - revisited
                  dsundstrom

                  All persistence mappings are outside the spec, so what ever a vendor does is ok.

                  • 6. Re: duplicate columns created - revisited
                    marcz99

                    Hi all,

                    Finders being the useful tools they are, sorting and limiting resulsets and all, how do you suggest to replace them if the column used for CMR is not available in EJB-QL?

                    Apart from specifying the physical column name that is?

                    Seems to me that a field should be BOTH a cmr field and a cmp field, so that all available features be available.

                    Any other approach seems unnatural to me. It forces our hand into selecting a single approach to access data, one which is much less optimizable (limit clauses etc).

                    I use a workarond around the whole thing, but that is all it as, a workaround.

                    My 2 cents.

                    Thanks in advance

                    Marc Villeneuve

                    • 7. Re: duplicate columns created - revisited
                      marcz99

                      Hi all,

                      Finders being the useful tools they are, sorting and limiting resulsets and all, how do you suggest to replace them if the column used for CMR is not available in EJB-QL?

                      Apart from specifying the physical column name that is?

                      Seems to me that a field should be BOTH a cmr field and a cmp field, so that all available features be available.

                      Any other approach seems unnatural to me. It forces our hand into selecting a single approach to access data, one which is much less optimizable (limit clauses etc).

                      I use a workarond around the whole thing, but that is all it as, a workaround.

                      My 2 cents.

                      Thanks in advance

                      Marc Villeneuve

                      • 8. Re: duplicate columns created - revisited
                        rupinder

                        i am using JBoss 3.0.3 and was getting the duplicate column error.
                        After going through this thread, i made the changes to define the field as cmr-field and not cmp-field.
                        But now the error i am getting is : cannot insert NULL into the foreign key field in the related table, going by the example mentioned in this thread, error will be : cannot insert NULL into IMAGES.GRP_KEY.
                        any solution?
                        can you please include your ejb-jar.xml, jbosscmp-jdbc.xml and the bean classes for Group and Image as reference.

                        thanks
                        rupinder

                        • 9. Re: duplicate columns created - revisited
                          rupinder

                          please refer my above query.
                          When i got the NULL error, in my database table the constraint for the foreign-key was NOT NULL. After removing this constraint it worked.
                          1. Does that mean when using cmr's, NOT NULL constraint can never be applied to the foreign-key field in the related table?

                          2.But my foreign key is a part of the composite primary key.To make it nullable i have to remove it from the primary key combination.Please note i am using JBoss 3.0.3, is it a limitation of this version. If yes with which version of JBoss the NOT NULL constraint for foreign keys is supported?

                          thanks
                          rupinder

                          • 10. Re: duplicate columns created - revisited
                            agoretti

                            Maybe, the "duplicate column name" problem goes away if you use the <read-only>true</read-only> in the <ejb-relation> of the jbosscmp-jdbc.xml file. Try to use this tag like the code below.


                            <ejb-relation>
                            <ejb-relation-name>master-detail</ejb-relation-name>
                            <read-only>true</read-only>
                            <foreign-key-mapping />
                            <ejb-relationship-role>
                            <ejb-relationship-role-name>
                            MasterRelationshipRole
                            </ejb-relationship-role-name>
                            <key-fields>
                            <key-field>
                            <field-name>codMaster</field-name>
                            <column-name>CO_MASTER_KEY</column-name>
                            </key-field>
                            </key-fields>
                            </ejb-relationship-role>
                            <ejb-relationship-role>
                            <ejb-relationship-role-name>
                            DetailRelationshipRole
                            </ejb-relationship-role-name>
                            </ejb-relationship-role>
                            </ejb-relation>


                            With this tag, the container doesn't put the column of the relationship in the insert sql. It is useful for one-to-many, many-to-one and one-to-one relationships. The many-to-many relationship doesn't cause the "duplicate column name" problem.

                            The read-only relationships cause a problem: the impossibilty of set the cmr field of the relationship.

                            I don't know if it is correct, but maybe this idea helps someone.

                            • 11. Re: duplicate columns created - revisited
                              cobraflow

                              ...removing the cmp- field from the EJB on the many side of the relationship produces incorrect WHERE clauses if you use a multi-part primary key.

                              eg:-

                              T1
                              id
                              name

                              T2
                              id
                              ref
                              description

                              in T2 the id+ref give the unique key. The WHERE clause provides WHERE ref=? which will not have the correct outcome as ref is not unique!

                              I am going try the 'read-only' idea above as it seems logical that the T2EJB has read-only access to the id field !!??


                              Lewis

                              • 12. Re: duplicate columns created - revisited
                                erik777

                                Let me know if you find a resolution. I cannot figure out how to get it to work when the foreign key is part of the primary key. Removing the abstract get/sets for the foreign key columns creates a problem when those columns are also part of the primary key.

                                How did the read-only solution work for you?

                                • 13. Re: duplicate columns created - revisited
                                  erik777

                                  There appears to be a patch for this posted in this thread:

                                  http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/ can say that this patch is not included in 3.0.4, unfortuantely. I don't know how to go about finding out when there will be a release with it in there. I definately do not want to spend days learning how to obtain the source code, apply the patch, compile it, obtain Tomcat's source code, compile it, and bind the two together under one JVM.

                                  If anyone else goes through this, could you post a link to where we could download it, or at least detailed instructions on how we could do it?

                                  I'm currently using JBoss 3.0.4-Tomcat 4.0.6. Tomcat 4.1.12 didn't work for me because it kept giving an error on a DTD that wouldn't go away even when I deleted all instances from the file system. The problem went away when I used 4.0.6 instead.

                                  • 14. Re: duplicate columns created - revisited
                                    ravidwivedi

                                    Hi Rupinder,
                                    Please note that i am also facing same problem i.e. the foreignKeys are also part of primary key and I can not remove them from the CMP mapping.
                                    Please let me know if you were able to resolve the problem .


                                    thanks in advance
                                    with best regards
                                    ravi

                                    1 2 Previous Next