one-one relationship problem

[gateway87]

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

[gateway87]

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

[aloubyansky]

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.

[gateway87]

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

[aloubyansky]

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.

[gateway87]

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

[aloubyansky]

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..."

[gateway87]

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>
........

[aloubyansky]

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