4 Replies Latest reply on Oct 25, 2002 6:06 PM by alexis bergue

    Get foreign key value

    alexis bergue Newbie

      Hi all,

      I have an One to Many relationship:

      ejb-jar.xml:
      <ejb-relation>
      <ejb-relation-name>User-GalleryImage</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      User-has-many-GalleryImages
      </ejb-relationship-role-name>
      One
      <relationship-role-source>
      <ejb-name>User</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      GalleryImage-has-a-User
      </ejb-relationship-role-name>
      Many
      <relationship-role-source>
      <ejb-name>GalleryImage</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>user</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>

      jbosscmp-jdbc.xml:
      <ejb-relation>
      <ejb-relation-name>User-GalleryImage</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      User-has-many-GalleryImages
      </ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>userID</field-name>
      <column-name>userID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>
      GalleryImage-has-a-User
      </ejb-relationship-role-name>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>


      My tables look like:

      CREATE TABLE user (
      userID int(11) NOT NULL default '0',
      email char(64) NOT NULL default '',
      password char(64) NOT NULL default '',
      PRIMARY KEY (userID),
      UNIQUE KEY email_idx (email)
      ) TYPE=InnoDB;

      CREATE TABLE galleryimage (
      imageID int(11) NOT NULL default '0',
      file char(64) NOT NULL default '',
      userID int(11) default NULL,
      PRIMARY KEY (imageID),
      KEY userID_idx (userID)
      ) TYPE=InnoDB;


      When I run query using the foreign key like:
      SELECT OBJECT(gi) FROM galleryimage gi WHERE gi.user.file=?1
      My relationship is working fine.

      But when I try to insert a new row into galleryimage via GalleryImage Bean, JBoss executes the following query to do the job:
      INSERT INTO galleryimage (imageID, file, userID) VALUES (?, ?, ?)

      But JBoss always put NULL into userID column. How can JBoss put the correct foreign key value?

      Thanks,
      Alexis