NHibernate Oracle - Transaction Commit Issue

Question

0 vote down

Hi,

I am running with same Issue of at the time of Commit transaction it fails. I have changed my all mapping string with AnsiString. But still i am getting error.

Select is working fine, even delete is also working fine.

If my transaction contains only one insert statement or update statement then also it is working fine.

But when there are more than one queries (Insert/Update) then at the time of commit it is giving me error that "Integrity Constraint: Parent Key not found"

Can you please help me out?

Mahesh....

Answer 1

Hi Mahesh,

this error is an integrity constraint error. It means that at the end of your transaction a child that has no parent in a parent-child relationship. Since you are getting this error on commit it means the FOREIGN KEY is declared as DEFERRABLE (initialy deferred) and only checks for integrity at commit time.

You have a problem in your logic, perhaps you could post a simple example (CREATE TABLE parent... CREATE TABLE child... UPDATE/INSERT) and we can help you further.

Answer 2

Hi Vincent,

Thanks for the reply.

You mean to say that the problem is in the database structure. I am using combination of Oracle -> Nhibernate.

if yes,

here a sample example

CREATE TABLE "Employee_TYPE" ( "Employee_TYPE_ID" NUMBER(*,0) NOT NULL ENABLE, CONSTRAINT "Employee_TYPE_PK" PRIMARY KEY ("Employee_TYPE_ID") ENABLE ) ;

CREATE TABLE Employee ( "Employee_ID" NUMBER(*,0) NOT NULL ENABLE, "Employee_TYPE" NUMBER(*,0) NOT NULL ENABLE, "Employee_Name" VARCHAR2(10 BYTE) DEFAULT 'FFFFFF' NOT NULL ENABLE, CONSTRAINT "Employee_PK" PRIMARY KEY ("Employee_ID") ENABLE, CONSTRAINT "Employee_Employee_TYPE_FK1" FOREIGN KEY ("Employee_TYPE") REFERENCES "Employee_TYPE" ("Employee_TYPE_ID") ENABLE ) ;

Anyway, I have checked the constraints type, it is NOT DEFERRABLE. :(

Please let me know your feedback ASAP as i have already waste a lot of time in same :D,

Thanks, Mahesh

Answer 3

NHibernate does the insert (or update) of your employee before inserting the newly created employee_type. The error means that your employee is missing its employee_type in database.

2 choices:

1. Do a ISession.Flush after creating the employee_type and before creating (or updateing) your employee.
2. Change the foreign key to be deferable.

Answer 4

Hi,

Thanks for your response.

I did all the foreign key constraints with deferable. But still no luck :(

My application working nicely with SqlServer. Now i am checking it with Oracle 10g.

I have changed configuration and some of the mapping file changes(Ansistring etc).

But no luck.

I think there will be any problem in the configuration.

"

  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
  <property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>
  <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
  <property name="connection.connection_string">Data Source=ds;User ID=sa;Password=sa;</property>
  <property name="show_sql">true</property>
  <property name="adonet.batch_size">10</property>
  <property name="use_outer_join">true</property>
  <property name="command_timeout">444</property>
  <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>

"

Can u have any thought on that?

Thanks, Mahesh

Answer 5

Hi, I got the solution ....

It is happening with me because of NHibernate Behaviour...

I have explicitely created Sequence and Triggers for autoincrement.

but it will fire two time, first when i did normal save and second when i call transaction complete. So there is miss match between id's actual used and referenced.

So it is giving me error - parent key not found.

I have just removed all the trigger and sequences and in the mapping file i have added generated class as a increment. :D

" generator class="increment" "

It is perfectly works for me...

Thanks, Mahesh