[Hibernate-devel] Update problems

[Urberg, J. <ju...@ve...>]

I have a couple of problems trying to do updates thru Hibernate.

1) I have a class with a collection.  When I load the class, hibernate tries
to update the foreign key on the collection table (and tries to set it to
NULL no less).  Here's the mapping:

  <class name="MessageBoxEntry" table="message_box_entry">
    <id name="id" column="id" type="long">
      <generator class="native"/>
    </id>
	<many-to-one 
        name="messageBox"
        class="MessageBox" 
        column="message_box_id" 
        cascade="none"
        outer-join="true"/>
	<many-to-one 
        name="message"
        class="Message" 
        column="message_id" 
        cascade="none"
        outer-join="true"/>
	<many-to-one 
        name="staff"
        class="StaffImpl" 
        column="staff_id" 
        cascade="none"
        outer-join="true"/>
    <property name="read" column="read_flag" type="yes_no"/>
  </class>
  <class name="Message" table="message_table">
    <id name="id" column="id" type="long">
      <generator class="native"/>
    </id>
    <property name="subject"/>
    <property name="text" column="message_text"/>
    <property name="dateSent" column="date_sent"/>
	<many-to-one 
        name="sender"
        class="com.idexx.cstone.core.domain.StaffImpl" 
        column="staff_id" 
        cascade="none"
        outer-join="true"/>
	<set role="addresseeEntries" lazy="true" cascade="all">
		<jcs-cache usage="read-write"/>
    	      <key column="message_id"/>
		<one-to-many class="MessageAddressee"/>
	</set>
  </class>
  <class name="MessageAddressee" table="message_addressee">
	<jcs-cache usage="read-write"/>
    	<id name="id" column="id" type="long">
      		<generator class="native"/>
    	</id>
	<many-to-one 
	        name="owner"
	        class="Message" 
	        column="message_id" 
	        cascade="none"
	        outer-join="true"/>
	<many-to-one 
	        name="groupEntry"
	        class="AddresseeGroup" 
	        column="addressee_group_id" 
	        cascade="none"
	        outer-join="true"/>
	<many-to-one 
	        name="staffEntry"
	        class="StaffImpl" 
	        column="staff_id" 
	        cascade="none"
	        outer-join="true"/>
  </class>

The code to read a message looks like this:

	Message message = null;
	MessageBoxEntry entry =
(MessageBoxEntry)session.load(MessageBoxEntry.class, aMessageBoxEntryId);
	if (entry != null) {
		message = entry.getMessage();
	}

Which produces the following SQLs:

[INFO,Default] Hibernate: SELECT message_box1.id AS id0,
message_box1.description AS description0, message_table2.id AS id1,
message_table2.subject AS subject1,
 message_table2.message_text AS message_text1, message_table2.date_sent AS
date_sent1, message_table2.staff_id AS staff_id1, staff3.id AS id2,
staff3.staffid AS
 staffid2, staff3.firstname AS firstname2, staff3.middleinitial AS
middleinitial2, staff3.lastname AS lastname2, staff3.privilege_level AS
privilege_level2, staff4.id AS id3, staff4.staffid AS staffid3,
staff4.firstname AS firstname3, staff4.middleinitial AS middleinitial3,
staff4.lastname AS lastname3, staff4.privilege_level AS privilege_level3,
message_box_entry0.id AS id4, message_box_entry0.message_box_id AS
message_box_id4, message_box_entry0.message_id AS message_id4,
message_box_entry0.staff_id AS staff_id4, message_box_entry0.read_flag AS
read_flag4 FROM message_box_entry message_box_entry0 LEFT OUTER JOIN
message_box message_box1 ON
message_box_entry0.message_box_id=message_box1.id LEFT OUTER JOIN
message_table message_table2 ON
message_box_entry0.message_id=message_table2.id LEFT OUTER JOIN staff staff3
ON message_table2.staff_id=staff3.id LEFT OUTER JOIN staff staff4 ON
message_box_entry0.staff_id=staff4.id WHERE message_box_entry0.id= ?
[INFO,Default] Hibernate: SELECT y.id, y.message_id, y.addressee_group_id,
y.staff_id FROM message_addressee y, message_table x WHERE x.id =
y.message_id AND (x
.id=? )
[INFO,Default] Hibernate: select id from message_addressee where message_id
= ?
[INFO,Default] Hibernate: update message_addressee set message_id = null
where message_id = ?
[WARN,JDBCExceptionReporter] SQL Error: 233, SQLState: 23502
[ERROR,JDBCExceptionReporter] ASA Error -195: Column 'message_id' in table
'message_addressee' cannot be NULL

Why is it trying to set message_id to null?  Why is it trying to update
message_id on message_addressee at all?

2) Build a message with multiple addressees.  I try to save the message and
get a "com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE:
BatchUpdateException: Error occurred while executing batch statement: ASA
Error -143: Column '@p1' not found" SQL Exception.  Here is the SQL:

[INFO,Default] Hibernate: insert into message_table ( subject, message_text,
date_sent, staff_id ) values ( ?, ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: insert into message_addressee ( message_id,
addressee_group_id, staff_id ) values ( ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: insert into message_addressee ( message_id,
addressee_group_id, staff_id ) values ( ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: insert into message_box_entry ( message_box_id,
message_id, staff_id, read_flag ) values ( ?, ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: insert into message_box_entry ( message_box_id,
message_id, staff_id, read_flag ) values ( ?, ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: insert into message_box_entry ( message_box_id,
message_id, staff_id, read_flag ) values ( ?, ?, ?, ? )
[INFO,Default] Hibernate: select @@identity
[INFO,Default] Hibernate: update staff set staffid = ?, firstname = ?,
middleinitial = ?, lastname = ?, privilege_level = ? where id = ?
[INFO,Default] Hibernate: update staff set staffid = ?, firstname = ?,
middleinitial = ?, lastname = ?, privilege_level = ? where id = ?
[INFO,Default] Hibernate: update message_addressee set message_id = ? where
id = ?
[WARN,JDBCExceptionReporter] SQL Error: 0, SQLState: JZ0BE
[ERROR,JDBCExceptionReporter] JZ0BE: BatchUpdateException: Error occurred
while executing batch statement: ASA Error -143: Column '@p1' not found

How does one fix this?  Have I mapped the collection incorrectly?

My current hack work around is to not include the set on Message and
manually load and save the MessageAddressees.  Yuck.

Thanks,
John