8 Replies Latest reply on Oct 3, 2002 10:45 AM by Alexey Loubyansky

    one-one relationship problem

    gateway87 Newbie

      Hi, gurus :-)

      I have a one-to-one relationship with two tables. UserInfo(userId, username, password) and UserStat(userId, firstname, lastname, phone, fax, address).

      ejb-jar.xml:

      <ejb-relation >
      <ejb-relation-name>USER_INFO-cmp20-USER_STAT-cmp20</ejb-relation-name>
      <!-- bidirectional -->
      <ejb-relationship-role >
      <ejb-relationship-role-name>USER_INFO-cmp20-has-USER_STAT-cmp20</ejb-relationship-role-name>
      One
      <relationship-role-source >
      <ejb-name>UserInfo</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>userStat</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role >
      <ejb-relationship-role-name>USER_STAT-cmp20-has-USER_INFO-cmp20</ejb-relationship-role-name>
      One
      <cascade-delete />
      <relationship-role-source >
      <ejb-name>UserStat</ejb-name>
      </relationship-role-source>
      <cmr-field >
      <cmr-field-name>userInfo</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>

      jbosscmp-jdbc.xml:

      <ejb-relation>
      <ejb-relation-name>USER_INFO-cmp20-USER_STAT-cmp20</ejb-relation-name>
      <foreign-key-mapping/>
      <ejb-relationship-role>
      <ejb-relationship-role-name>USER_INFO-cmp20-has-USER_STAT-cmp20</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>USER_STAT-cmp20-has-USER_INFO-cmp20</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      </ejb-relation>

      When I want to get the UserInfoLocal and UserStatLocal, I get an exception caused by "java.sql.SQLException: ORA-00936: missing expression".

      2002-09-30 17:09:16,312 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.UserInfo.findByPrimaryKey] Executing SQL: SELECT userId FROM USER_INFO WHERE userId=?
      2002-09-30 17:09:16,421 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.UserInfo] Executing SQL: SELECT userName, password FROM USER_INFO WHERE (userId=?)
      2002-09-30 17:09:16,453 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.UserInfo] Executing SQL: SELECT FROM USER_STAT WHERE (userId=?)
      2002-09-30 17:09:16,484 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException, causedBy:
      java.sql.SQLException: ORA-00936: missing expression

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
      at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1597)
      ........

      Please notice the SQL script: SELECT <blank?> FROM USER_STAT WHERE (userId=?)

      Could you please tell me what's wrong with it? I'm using Jboss3.02. Thanks in advance.

      Gateway

        • 1. Re: one-one relationship problem
          gateway87 Newbie

          Hi,

          It seems every table must has a primary key, is this right? Because table User_stat has no primary key but a foreign key (userId) to table User_Info. Is this the reason for my exception?

          Thanks a lot for your help.

          Gateway

          • 2. Re: one-one relationship problem
            Alexey Loubyansky Master

            Perhaps, you answered yourself the question.
            Is the table created by JBoss? I guess, no. Because, JBoss creates correct tables. Let it create the table and see the difference.

            • 3. Re: one-one relationship problem
              gateway87 Newbie

              Hi, Loubyansky,

              Thanks for your answer. As you guessed, I created tables myself. However, later I modified the attribute to let JBoss create tables for me, after I set the "pk-constraint" attribute of User_Stat to "false", the tables can be created successfully.

              But the problem is still exist. After I deployed, when I want to get the UserStatLocal from UserStatLocal (they have bidirectional one-one relationship), I still get the exception caused by "java.sql.SQLException: ORA-00936: missing expression".

              ...........
              2002-09-30 17:09:16,453 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadRelationCommand.UserInfo] Executing SQL: SELECT FROM USER_STAT WHERE (userId=?)
              2002-09-30 17:09:16,484 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException, causedBy:
              java.sql.SQLException: ORA-00936: missing expression

              at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
              at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:1597)
              ........

              Please notice the SQL script: SELECT <blank?> FROM USER_STAT WHERE (userId=?)

              How do you think for the problem? Thanks again for your help.

              Gateway

              • 4. Re: one-one relationship problem
                Alexey Loubyansky Master

                The problem is, obviously, with primary key of UserStat. Make sure it is set up correctly in DDs, classes and table.
                If you are in a deep frustration, post relevant to pk fields parts of DDs and we'll continue.

                • 5. Re: one-one relationship problem
                  gateway87 Newbie

                  Table user_stat has no primary key. So there is no "<primkey-field>" element in DD. I wonder if this is the reason for the problem. If all tables must have a promary key? Seems ejb specs does not require this.

                  Thanks again for your help.

                  Gateway

                  • 6. Re: one-one relationship problem
                    Alexey Loubyansky Master

                    primkey-field is optional element but prim-key-class is mandatory. If you deployed successfully a bean w/o specifying prim-key-class then it's a bug in verifier.

                    10.6.13 Entity bean’s primary key class:
                    "...The Bean Provider must specify a primary key class in the deployment descriptor.
                    The primary key type must be a legal Value Type in RMI-IIOP.
                    The class must provide suitable implementation of the hashCode() and equals(Object
                    other) methods to simplify the management of the primary keys by the Container..."

                    • 7. Re: one-one relationship problem
                      gateway87 Newbie

                      Yes, I do have prim-key-class as following:

                      <ejb-class>user.ejb.UserStatCMP</ejb-class>
                      <persistence-type>Container</persistence-type>
                      <prim-key-class>java.lang.Integer</prim-key-class>
                      False
                      <cmp-version>2.x</cmp-version>
                      <abstract-schema-name>UserStat</abstract-schema-name>
                      <cmp-field><field-name>firstname</field-name></cmp-field>
                      <cmp-field><field-name>lastname</field-name></cmp-field>
                      <cmp-field><field-name>sex</field-name></cmp-field>
                      <cmp-field><field-name>phone</field-name></cmp-field>
                      <cmp-field><field-name>fax</field-name></cmp-field>
                      <cmp-field><field-name>email</field-name></cmp-field>
                      <cmp-field><field-name>address</field-name></cmp-field>

                      <resource-ref>
                      ........

                      • 8. Re: one-one relationship problem
                        Alexey Loubyansky Master

                        Since you are using CMP, you must specify primkey-field. It's simple pk class. primkey-field is omitted if pk class is composite.