NHibernate Transaction fails with Insert and Update on Oracle

Question

Hi,

Post Update: I have tracked down the problem at the command "ExecuteNonQuery". That's the one that fails during an update or hangs during an insert. Trying a simple example using plain ADO.NET and their transactions works perfect. Also... it works great on my local home computer connection an Oracle Express edition. Pointing it again in some kind of server config?? It would be nice to step into the NHibernate code while debuging, but so far I'm still not able to set this up, even if I have rebuild the source and use those dll and pdb files. Was anyone able to do this before?

I've been scratching my head on this for a while now. I've been developing with NHibernate and an Oracle 10g database for a few days now, so far only using select statements which are all working great with the mapping.

I now started to implement my first insert (save) and update statements, but the tests all fail. They all fail on the transaction.commit() part.

When performing an INSERT (Save), the code reaches transaction.commit(), but then got "stucked". The test keeps on running without moving forward. This is the output of the test (note that the test keeps running)

NHibernate: select hibernate_sequence.nextval from dual
NHibernate: INSERT INTO MOB_PL_MAPPING_TEST (DES, TEST_ID) VALUES (:p0, :p1);:p0 = 'This is a test!', :p1 = 161

When performing an UPDATE, the transaction.commit() fails and I receive following error stack:

NHibernate: SELECT test0_.TEST_ID as TEST1_10_0_, test0_.DES as DES10_0_ FROM MOB_PL_MAPPING_TEST test0_ WHERE test0_.TEST_ID=:p0;:p0 = 61
NHibernate: UPDATE MOB_PL_MAPPING_TEST SET DES = :p0 WHERE TEST_ID = :p1;:p0 = 'Changed!', :p1 = 61
TestCase 'Data.Tests.Test_Update_on_Test_Table'
failed: NHibernate.TransactionException : Rollback failed with SQL Exception
  ----> System.InvalidOperationException : This OracleTransaction has completed; it is no longer usable.
   c:\CSharp\NH\nhibernate\src\NHibernate\Transaction\AdoTransaction.cs(260,0): at NHibernate.Transaction.AdoTransaction.Rollback()
   E:\SubVersion\Application\Src\Data\UnitOfWork\Data.UnitOfWork\GenericTransaction.cs(26,0): at Data.UOW.GenericTransaction.Rollback()
   E:\SubVersion\Application\Src\Data\UnitOfWork\Data.UnitOfWork\UnitOfWorkImplementor.cs(49,0): at Data.UOW.UnitOfWorkImplementor.TransactionFlush(IsolationLevel isolationLevel)
   E:\SubVersion\Application\Src\Data\UnitOfWork\Data.UnitOfWork\UnitOfWorkImplementor.cs(36,0): at Data.UOW.UnitOfWorkImplementor.TransactionFlush()
   E:\SubVersion\Application\Src\Data\Data.Tests\Repositories\LoyaltyRepositoryTests.cs(159,0): at Data.Tests.Test_Update_on_Test_Table()
   --InvalidOperationException
   at System.Data.OracleClient.OracleTransaction.AssertNotCompleted()
   at System.Data.OracleClient.OracleTransaction.Rollback()
   c:\CSharp\NH\nhibernate\src\NHibernate\Transaction\AdoTransaction.cs(246,0): at NHibernate.Transaction.AdoTransaction.Rollback()

I must say I'm unknown to oracle, but it seems that establishing the transaction causes the problem. Though the same code (using transactions) for a select statement (GET) works fine. Could this be an oracle config problem (blocking insert/update transactions) or do I have to configure something else at application level?

Can anybody help me out here or shed more light on the problem that may occure?

Thanks in advance.

Answer 1

After managing to hook up the NHibernate code to my debuger, I was able to step through the code up to the point where the Command object is executed. There, the problem was to be found in the parameter types. Parameters that where a string had the type set to "String", where they where suposed to be "AnsiString".

Turned out, I already ran into this article when I was mapping a string to an id http://www.jameskovacs.com/blog/NHibernateAndTheCaseOfTheCrappyOracleErrorMessage.aspx but didn't thought more of it.

Either way, adding the type to each string property in the mapping resolved the problem.

<property name="Description" column="DES" type="AnsiString" />

A hectic 3 days... but it's solved :D

Answer 2

Hi,

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

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 then at the time of commit it is giving me error that "Integrity Constraint: Parent Key not found"

Can you please help me out?