UPDATE instead of INSERT on child table with Oracle sequence

w_p · **Joined:** Tue Apr 29, 2008 3:28 am **Posts:** 3

I extended the QuickStart example (http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/quickstart.html#quickstart-intro) with a detail table (Toy, each cat can own n toys), and tried it with Oracle XE.
When I use an "uuid.hex" generator to generate the primary key, everything works fine.
When I use a "native" generator I get this error message when I call session.Save(...) on a new cat with a new toy:
NHibernate.StaleStateException: Unexpected row count: 0; expected: 1

Looking at the SQL, I can see that the INSERT on cat happens, and then an UPDATE (instead of an INSERT) on toy happens.
Using <generator class="native"> in both mapping files (cat and toy) results in getting the primary key values from an Oracle sequence.

Code:

ISession session = NHibernateHelper.GetCurrentSession();
ITransaction tx = session.BeginTransaction();
Cat princess = new Cat();
...
Toy ribbon = new Toy();
...
ribbon.Cat = princess;
princess.Toys.Add(ribbon);
session.Save(princess);
tx.Commit();

The exception is thrown on tx.Commit();

The class Cat uses Iesi.Collections.Generic<T> in order to hold its toys:

Code:

public virtual ISet<Toy> Toys
{
 get { return toys; }
 set { toys = value; }
}

The tables seem to be OK (in the working example - if I use uuid.hex instead of native - the IDs are NVARCHAR2, resp. string in C#):

Quote:

alter table Toy drop constraint FK9B030089FA0CBF7D;
drop table Cat cascade constraints;
drop table Toy cascade constraints;
drop sequence hibernate_sequence;
create table Cat (
Id NUMBER(20,0) not null,
Name NVARCHAR2(255),
Sex NCHAR(1),
Weight FLOAT(24),
primary key (Id)
);
create table Toy (
Id NUMBER(20,0) not null,
Name NVARCHAR2(255),
Color NVARCHAR2(255),
CatId NUMBER(20,0) not null,
primary key (Id)
);
alter table Toy add constraint FK9B030089FA0CBF7D foreign key (CatId) references Cat ;
create sequence hibernate_sequence INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

Hibernate version: NHibernate 1.2.1 GA

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="QuickStart.Cat, QuickStart">
    <id name="Id" type="long" unsaved-value="-1" column="Id">
      <generator class="native">
      </generator>
    </id>
    <property name="Name" />
    <property name="Sex" />
    <property name="Weight" />
    <set name="Toys" cascade="all-delete-orphan">
      <key column="CatId" />
      <one-to-many class="QuickStart.Toy, QuickStart" />
    </set>
  </class>
</hibernate-mapping>

Code:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="QuickStart.Toy, QuickStart">
    <id name="Id" type="long" unsaved-value="-1" column="Id">
      <generator class="native">
      </generator>
    </id>
    <property name="Name" />
    <property name="Color" />
    <many-to-one name="Cat" class="QuickStart.Cat, QuickStart" column="CatId" not-null="true" />
  </class>
</hibernate-mapping>

Full stack trace of any exception that occurs:
[StaleStateException: Unexpected row count: 0; expected: 1]
NHibernate.AdoNet.BasicExpectation.VerifyOutcomeNonBatched(Int32 rowCount, IDbCommand statement) +237
NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation) +48
NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) +1067
NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, ISessionImplementor session) +275
NHibernate.Impl.ScheduledUpdate.Execute() +214
NHibernate.Impl.SessionImpl.Execute(IExecutable executable) +112
NHibernate.Impl.SessionImpl.ExecuteAll(IList list) +72
NHibernate.Impl.SessionImpl.Execute() +204
NHibernate.Impl.SessionImpl.Flush() +42
NHibernate.Transaction.AdoTransaction.Commit() +160
QuickStart._Default.createCatButton_Click(Object sender, EventArgs e) in E:\csharp\qwe\QuickStart\QuickStart\Default.aspx.cs:72
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Name and version of the database you are using:
Oracle Database 10g Express Edition (Oracle XE)

w_p · **Joined:** Tue Apr 29, 2008 3:28 am **Posts:** 3

Can anyone provide a (simple) master detail (parent child) example with primary keys from DB sequences (mappings + source code which uses the created classes)?

The only way I get it to work is:

Code:

ISession session = NHibernateHelper.GetCurrentSession();
ITransaction tx = session.BeginTransaction();
Cat princess = new Cat();
...
Toy ribbon = new Toy();
...
session.Save(princess);
ribbon.Cat = princess;
princess.Toys.Add(ribbon);
session.Save(ribbon);
tx.Commit();

Is it intended to save master-detail data in this way (I don't think so)?
Does NHibernate have an issue with (Oracle) sequences (since my example works with uuid.hex and strings instead of native and long)?
How are these 2 things connected?: use of a sequence (instead of uuid.hex), and the information if a detail must be inserted (instead of updated).
What am I doing wrong?

wolli · **Posted:** Fri May 02, 2008 7:02 am

According to your mapping, the association is owned by the parent and the child. Try inverse="true" on the set. With that, at least this should work and result in a insert without an explicit update for the foreign key:

Code:

Cat princess = new Cat();
...
Toy ribbon = new Toy();
...
ribbon.Cat = princess;
princess.Toys.Add(ribbon);

session.Save(princess);

I think that will also solve your problem with the native id.

w_p · **Joined:** Tue Apr 29, 2008 3:28 am **Posts:** 3

Thanks for idea, Wolfgang. I already tried it with inverse="true", but unfortunately it did not work.

Removing unsaved-value="-1" (at least in the toy mapping) solved the problem.

Also see http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/mapping.html:
"The unsaved-value attribute is almost never needed in NHibernate 1.0."
And http://www.hibernate.org/hib_docs/nhibernate/html/example-parentchild.html:
"In NHibernate it is not necessary to specify unsaved-value explicitly."