views:

2681

answers:

4

Hi, I've been racking my head trying to get Nhibernate to work with a byte array as version mapping to an sql timestamp. I'd implemented an IUserVersionType but Nhibernate was creating varbinary in the database rather than timestamp. Inspired by a blog post by Ayende recently on concurrency, I changed my mapping to specify the sql-type to timestamp which worked perfectly. However I now face a rather curious problem wherein Nhibernate does an insert, gets the new version and then immediately tries to do an update and attempts to set the version column, which being an sql timestamp fails.

This is my mapping:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" namespace="Core.Domain.Entities"
default-lazy="false">
 <class name="Contact" table="Contacts" xmlns="urn:nhibernate-
mapping-2.2" optimistic-lock="version" dynamic-insert="true" dynamic-
update="true">
   <id name="Id" type="Int32" column="Id">
     <generator class="identity" />
   </id>
   <version name="Version" type="BinaryBlob" generated="always"
unsaved-value="null">
     <column name="Version" sql-type="timestamp" not-null="false" />
   </version>
   <property name="Title" type="String">
     <column name="Title" length="5" />
   </property>
   <property name="FirstName" type="String">
     <column name="FirstName" not-null="true" length="50" />
   </property>
   <property name="MiddleName" type="String">
     <column name="MiddleName" length="50" />
   </property>
   <property name="LastName" type="String">
     <column name="LastName" not-null="true" length="50" />
   </property>
   <property name="Suffix" type="String">
     <column name="Suffix" length="5" />
   </property>
   <property name="Email" type="String">
     <column name="Email" length="50" />
   </property>
   <bag name="PhoneNumbers" inverse="true" cascade="all-delete-
orphan">
     <key foreign-key="FK_Contacts_PhoneNumbers_ContactId" on-
delete="cascade" column="ContactId" />
     <one-to-many class="Core.Domain.Entities.PhoneNumber,
Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" />
   </bag>
   <property name="DateCreated" type="DateTime">
     <column name="DateCreated" />
   </property>
   <property name="DateModified" type="DateTime">
     <column name="DateModified" />
   </property>
   <property name="LastModifiedBy" type="String">
     <column name="LastModifiedBy" />
   </property>
 </class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" namespace="Core.Domain.Entities"
default-lazy="false">
 <class name="Customer" table="Customers" xmlns="urn:nhibernate-
mapping-2.2" optimistic-lock="version" dynamic-insert="true" dynamic-
update="true">
   <id name="Id" type="Int32" column="Id">
     <generator class="identity" />
   </id>
   <version name="Version" type="BinaryBlob" generated="always"
unsaved-value="null">
     <column name="Version" sql-type="timestamp" not-null="false" />
   </version>
   <property name="AccountNumber" access="nosetter.pascalcase-
underscore" type="String">
     <column name="AccountNumber" unique="true" length="25" />
   </property>
<!-- other mappings... -->
   <property name="DateCreated" type="DateTime">
     <column name="DateCreated" />
   </property>
   <property name="DateModified" type="DateTime">
     <column name="DateModified" />
   </property>
   <property name="LastModifiedBy" type="String">
     <column name="LastModifiedBy" />
   </property>
   <joined-subclass name="Core.Domain.Entities.Individual,
Core.Domain, Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" table="Individuals">
     <key column="CustomerId" />
     <many-to-one fetch="join" lazy="false" not-null="true"
cascade="all" unique="true" not-found="exception" name="Contact"
column="ContactID" />
     <bag name="Addresses" table="Addresses_Individuals">
       <key column="AddressId" foreign-
key="FK_Addresses_Individuals_Addresses_AddressId" />
       <many-to-many column="IndividualId"
class="Core.Domain.Entities.Address, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" foreign-
key="FK_Addresses_Individuals_Individuals_IndividualId" />
     </bag>
   </joined-subclass>
   <joined-subclass name="Core.Domain.Entities.Store, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" table="Stores">
     <key column="CustomerId" />
     <many-to-one unique="true" cascade="save-update" fetch="join"
not-null="true" not-found="exception" name="Address"
column="AddressId" />
     <many-to-one lazy="proxy" not-null="true" cascade="all" not-
found="exception" name="Client" column="ClientId" />
     <property name="StoreName" type="String">
       <column name="StoreName" not-null="true" length="50" />
     </property>
     <bag name="Contacts" table="Contacts_Stores">
       <key column="ContactId" foreign-
key="FK_Contacts_Stores_Contacts_ContactId" />
       <many-to-many column="StoreId"
class="Core.Domain.Entities.Contact, Core.Domain,
Version=0.1.3397.31993, Culture=neutral,
PublicKeyToken=94dc7dc697cfcfc0" foreign-
key="FK_Contacts_Stores_Stores_StoreId" />
     </bag>
   </joined-subclass>
 </class>
</hibernate-mapping>

Calling Session.Save on an Individual with associated Contact results in the following error:

NHibernate: INSERT INTO Addresses (Line1, PostalCode, Country,
DateCreated, DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3,
@p4, @p5); select SCOPE_IDENTITY(); @p0 = 'Order Address Line 1', @p1
= 'CV31 6BW', @p2 = 'United Kingdom', @p3 = '20/04/2009 19:45:32', @p4
= '20/04/2009 19:45:32', @p5 = ''
NHibernate: SELECT address_.Version as Version22_ FROM Addresses
address_ WHERE address_.Id=@p0; @p0 = '1'
NHibernate: INSERT INTO Contacts (FirstName, LastName, DateCreated,
DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3, @p4); select
SCOPE_IDENTITY(); @p0 = 'Joe', @p1 = 'Bloggs', @p2 = '20/04/2009
19:45:34', @p3 = '20/04/2009 19:45:34', @p4 = ''
NHibernate: SELECT contact_.Version as Version33_ FROM Contacts
contact_ WHERE contact_.Id=@p0; @p0 = '1'
NHibernate: INSERT INTO Customers (AccountNumber, DateCreated,
DateModified, LastModifiedBy) VALUES (@p0, @p1, @p2, @p3); select
SCOPE_IDENTITY(); @p0 = '', @p1 = '20/04/2009 19:45:34', @p2 =
'20/04/2009 19:45:34', @p3 = ''
NHibernate: INSERT INTO Individuals (ContactID, CustomerId) VALUES
(@p0, @p1); @p0 = '1', @p1 = '1'
NHibernate: SELECT individual_1_.Version as Version2_ FROM Individuals
individual_ inner join Customers individual_1_ on
individual_.CustomerId=individual_1_.Id WHERE
individual_.CustomerId=@p0; @p0 = '1'
NHibernate: UPDATE Contacts SET Version = @p0 WHERE Id = @p1 AND
Version = @p2; @p0 = 'System.Byte[]', @p1 = '1', @p2 = 'System.Byte[]'

System.Data.SqlClient.SqlException: Cannot update a timestamp column.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning
(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds
(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery
(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
in c:\CSharp\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:
line 203
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object
id, Object[] fields, Object[] oldFields, Object rowId, Boolean[]
includeProperty, Int32 j, Object oldVersion, Object obj,
SqlCommandInfo sql, ISessionImplementor session) in c:\CSharp\NH
\nhibernate\src\NHibernate\Persister\Entity
\AbstractEntityPersister.cs: line 2713
NHibernate.Exceptions.GenericADOException: could not update:
[Core.Domain.Entities.Contact#1][SQL: UPDATE Contacts SET Version =
@p0 WHERE Id = @p1 AND Version = @p2]

Any ideas why NHibernate is attempting to update the version column for Contact, even though it didn't for the Address?

+1  A: 

The Address doesn't have a version column I assume.

I wonder where you have the sql-type from. Why not this way?

   <version name="Version" type="Timestamp" generated="always" unsaved-value="null">
     <column name="Version" not-null="false" />
   </version>

You need of course a DateTime in the entity.

Stefan Steinegger
Address does have a version column.I can't use the above mapping because I need to leverage SQL Server's timestamp data type which is binary, not datetime.
Jimit
I don't know, didn't know that this works. I suggest you to ask Ayende himself, either on NHibernate's user group (http://groups.google.com/group/nhusers), or post a comment on Ayendes blog.
Stefan Steinegger
A: 

http://ayende.com/Blog/archive/2009/04/15/nhibernate-mapping-concurrency.aspx

I'd already seen that post by Ayende. In fact, that's the post I was referring to in my question above. The mapping I'm using above is identical to the one he used in that post for mapping a version column leveraging MS SQL Server's timestamp data type.
Jimit
+2  A: 

I have found that using dynamic-insert="true" on the class along with causes this issue. I use the following mapping successfully:

...
    <class name="Contact" table="Contact">
     <id name="ID" column="ID" type="int">
      <generator class="identity" />
     </id>
     <version name="Version" generated="always" unsaved-value="null" type="BinaryBlob"/>
...
Scott M
Thanks for that Scott. It seems you were write. For some reason enabling either dynamic update or dynamic insert causes NHibernate to attempt to update the version column. Disabling them fixes the issue. Unfortunately, I do require dynamic updates - I don't want NHibernate updating columns needlessly as it messes up our audit trails; only actually modified columns need to be updated. Any pointers on achieving this given the above limitation?
Jimit
Jimit, I found this issue when trying to solve the version column issue not trying to address a dynamic-insert issue so I didn't need to accomplish what it sounds like you do. I just let NHibernate update every column as it normally does without dynamic-insert = true. You say this messes up your audit trails... are you using DB triggers to track audit trails? If so, can you make them a bit smarter to only log actual changes to the data?
Scott M
A: 

Sorry, I don't have an answer, but I'm having the same issue with an Oracle ora_rowscn column that servers the same purpose. When I update a column, the SQL generated by NHibernate tries to update the ORA_ROWSCN column too:

NHibernate: UPDATE ACIM_ACTIONS SET ORA_ROWSCN = :p0, ACIM_ACTION_DESCRIPTION =:p1 WHERE ACIM_ACTION_CODE = :p2 AND ORA_ROWSCN = :p3; :p0 = '165260330', :p1 = 'updated @13/05/2009 15:15:26', :p2 = '1', :p3 = '165260329' System.Collections.ListDictionaryInternal could not update: [ConsoleApplication3.Domain.NHAcimActions#1][SQL: UPDATE ACIM_ ACTIONS SET ORA_ROWSCN = :p0, ACIM_ACTION_DESCRIPTION = :p1 WHERE ACIM_ACTION_CO DE = :p2 AND ORA_ROWSCN = :p3]

Gazmo
Scotts's suggestion above worked for me: disabling dynamic insert and dynamic update. If, like me, however, you need dynamic update (or insert) your guess is as good as mine.
Jimit