Combination of "normal" insert und sql-insert with

telly · **Joined:** Tue Mar 25, 2008 11:48 am **Posts:** 12

Ola!

First my Mapping:

Code:

   <class name="Class1" table="AdressBuch">
      <id name="Id">
         <generator class="assigned"/>
      </id>
      <array name="Adresse" cascade="all">
         <key column="Id_key"/>
         <index column="Id_Index" type="int"/>
         <one-to-many class="Class2"/>
      </array>
   </class>
   <class name="Class2" table="AdressBuchAdresse">
      <id name="Id">
         <generator class="assigned"/>
      </id>
      <property name ="Vorname"/>
      <property name="Telefon"/>
      <property name="Geburtsdatum"/>
      <sql-insert check="none">exec SetDocument ?,?,?</sql-insert>
  </class>
</hibernate-mapping>

Now the Problen:

Target: The Array in Class1 has a one-to-many relationship with Class2. I want to map these in my Database.

If i remove the <sql-insert> tag in Class2, NHibernate create Table, Foreign Keys and all work how it should work.
But i need an additional check when data of Class2 inserted into the Database thats why i must create a stored procedure.
But if i use these stored Procedure my Apllication crashed. I find out, that it will be crashed when NHibernate calls an Update to the table off Class2 (i think he inserted the Keys first and update the table then).

Can someone help me?

I cannot post Logs because i don't understand how i enable Logging. If someone can help me there, please post in my other thread.

Many Tnx 4 help,

teLLy

wolli · **Posted:** Thu Mar 27, 2008 3:43 am

Hibernate consider the association to be part of the owning entity, in your case Class1. So Class2 gets first inserted and then updated with the association which results in a foreign key violation.
You have to do two things:

1) Mark the collection as inverse
2) map the other end of the association in Class2

Have a look here for a detailed example and explanation:
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/example-parentchild.html

This should work:

<class name="Class1" table="AdressBuch">
...
<array name="Adresse" cascade="all" inverse="true">
...
</array>
</class>
<class name="Class2" table="AdressBuchAdresse">
...
<many-to-one name="Class1" column=".." not-null="true"/> <sql-insert check="none">exec SetDocument ?,?,?</sql-insert>
</class>

The only thing I'm wonderung about ist, that you say it was working without the stored procedure, because the problem mentioned above has nothing to do with that.

telly · **Joined:** Tue Mar 25, 2008 11:48 am **Posts:** 12

[quote
<array name="Adresse" cascade="all" inverse="true">
...
</array>

[/quote]

I cannot set Inverse=true because Array has no Attribut Inverse

Quote:

</class>
<class name="Class2" table="AdressBuchAdresse">
...
<many-to-one name="Class1" column=".." not-null="true"/> <sql-insert check="none">exec SetDocument ?,?,?</sql-insert>
</class>

I try to change, but there are a few differences. In the related document you posted above stands:

Code:

<many-to-one name="Parent" column="parent_id" not-null="true"/>

(We also need to add the Parent property to the Child class.) 

So i change my Mapping to

Code:

<many-to-one name="Parent" column="Id_key" not-null="true"/>

and create the Property Parant with type Class1.

If i call the .Save() Method, the Compiler says, that the Property Parent is null and that this property shouldnt be null.

So the Questions:
1) How can i activate Inverse?
2) Any other Information for me?

wolli · **Posted:** Thu Mar 27, 2008 9:28 am

You have to set the parent !

class2.Parent = class1;

According to the doc array should have inverse. What hibernate version do you use.

telly · **Joined:** Tue Mar 25, 2008 11:48 am **Posts:** 12

I have Schema-Version "urn:nhibernate-mapping-2.2"" and DLL 1.2.1.4000.

Espicially i used the NHibernate.Mapping.Attributes.dll to generate the .hbm from the sourcecode. Maybe this DLL not support all XML Elements??

teLLy

wolli · **Posted:** Fri Mar 28, 2008 4:29 am

You are right, schema does not contain inverse for array (unlike what's written in the doc). So let's see if we can find a way. Do you really need an array ? Can't you work with a bag or a list ? Thex definitely have the inverse.

telly · **Joined:** Tue Mar 25, 2008 11:48 am **Posts:** 12

Ok, i try and serach and try again, and now its nearly as it should be.

One Problem are still exist:

I insert with a store procedure (SP) into my Database.

Code:

<sql-insert check="none">exec SetDocument ?,?,?,?</sql-insert>

Nhibernate insert first with the SP and update the the insertet row with the reference form the "array above"

Code:

exec SetDocument ?,?,?,?
UPDATE AdressBuchAdresse SET Id_key = ?, Id_Index = ? WHERE Id = ?

Here ist the Problem: if a row with the special Value (for example Vorname) exist in the database, the store procedure doesnt insert a new one. Insted of insert the SP return the ID value from the special row.

This returned ID should NHibenate use in the next steps instead off the originally inserted value in the SP. Especially in the Update statement NHibernate should use these value.

Now the Apllication crashs because NHibernates SQL-Update expected 1 but return 0 rows(because the ID not exist).

The two alternative sollutions...(second is the better one)

1) how can i use a return Value from a SP in a sql-insert tag
2) Cann i say NHibernate, that it is ok when an update return o rows.

Greetz and many thnx for your support!!

teLLy

wolli · **Posted:** Fri Mar 28, 2008 8:37 am

How does your mapping look now ? If the insert is properly cascaded, there shouldn't be an update. But I don't know, if cascading works with custom sql at all !?

wolli · **Posted:** Fri Mar 28, 2008 8:39 am

And I don't think, that there is a possibility to get back the id, if you do an update instead of an insert inside your stored procedure.

telly · **Joined:** Tue Mar 25, 2008 11:48 am **Posts:** 12

My hbm

Code:

<hibernate-mapping default-access="field.lowercase-underscore" xmlns="urn:nhibernate-mapping-2.2" >
   <class name="Class1" table="AdressBuch">
      <id name="Id">
         <generator class="native"/>
      </id>
      <array name="Adresse" cascade="all">
         <key column="Id_key"/>
         <index column="Id_Index" type="int"/>
         <one-to-many class="Class2"/>
      </array>
   </class>
   <class name="Class2" table="AdressBuchAdresse">
      <id name="Id" type="Guid">
         <generator class="guid"  /> 
      </id>
      <property name="Vorname"/>
      <property name="Telefon"/>
      <property name="Geburtsdatum"/>
      <array name="Name" cascade="all" >
         <key column="Id_key"/>
         <index column="Id_Index" type="int"/>
         <one-to-many class="Class3"/>
      </array>
      <sql-insert check="none">exec SetDocument ?,?,?,?</sql-insert>
   </class>
   <class name="Class3" table="AdressBuchAdresseName">
      <id name="Id" type="Guid">
         <generator class="guid"  /> 
      </id>
      <property name="Important"/>
      <property name="Value"/>
      <sql-insert check="none">exec SetAdresseName ?,?,?</sql-insert>
   </class>
</hibernate-mapping>

And this hbm creates the following SQL

Code:

create table AdressBuchAdresseName (
  Id UNIQUEIDENTIFIER not null,
   Important BIT null,
   Value NVARCHAR(255) null,
   Id_key INT null,
   Id_Index INT null,
   primary key (Id)
)
create table AdressBuch (
  Id INT IDENTITY NOT NULL,
   primary key (Id)
)
create table AdressBuchAdresse (
  Id UNIQUEIDENTIFIER IDENTITY NOT NULL,
   Vorname NVARCHAR(255) null,
   Telefon BIGINT null,
   Geburtsdatum DATETIME null,
   Id_key INT null,
   Id_Index INT null,
   primary key (Id)
)
alter table AdressBuchAdresseName  add constraint FK56238844F414E699 foreign key (Id_key) references AdressBuchAdresse 
alter table AdressBuchAdresse  add constraint FK4A3ADD80F414E699 foreign key (Id_key) references AdressBuch

teLLy

wolli · **Posted:** Fri Mar 28, 2008 9:51 am

In the stored procedures you check if an AddressBuchAddress entry already exists with the given values and do an update instead of an insert if that's the fact, right ? Same for AdressBuchAdresseName ?

The only way I can think of letting hibernate know the correct id, is using an id generator where the actual id is generated in the database. With guid generator I'm not sure what is really done internally. The strange thing is that die generated id columns differ in your sql, although the mappings are identical. Can you try to set the id column of AdressbuchAdresseName like in AdressBuchAdresse.

But I'm not really sure, if the whole approach is possible with hibernate. Another possibility would be