Question about generating <id> value

drmcclelland · **Posted:** Wed Jul 11, 2007 9:39 pm

Hello,

In converting a legacy database application to use NHibernate, I have to cope with its identity generation. It's a MSSQL2000 database, and it uses a one-record table called MISeq to record the last ID generated (very similar to an Oracle sequence), which is a sequentially-incremented integer. This generated ID guarantees uniqueness across several tables, which are distinctly different objects in my new model.

What is the best approach for me to do this? Is there any way to get one of the built-in generators to do this for me?

I tried writing a custom IIdentifierGenerator so that new instances of my objects can get the next value from the MISeq table, but by the time my IIdentifierGenerator.Generate() method gets called, I'm already inside a pending transaction (an ITransaction). This causes an exception in the Generate() method, which is attempting to execute a stored procedure (MISEQ_U) that does the work of incrementing the ID for me. Here's the VB.NET code I'm using to execute the stored procedure:

Code:

Public Class MISeqIdentifierGenerator
    Implements NHibernate.Id.IIdentifierGenerator

    Public Function Generate(ByVal session As NHibernate.Engine.ISessionImplementor, ByVal entity As Object) As Object Implements NHibernate.Id.IIdentifierGenerator.Generate
        If (entity Is Nothing) Then Throw New ArgumentNullException("entity")
        If (Not TypeOf entity Is IMISeqIdentifier) Then Throw New ArgumentException("must be IMISeqIdentifier", "entity")
        MsgBox("generating MISeq ID")
        Dim conn As IDbConnection = session.Connection
        Dim cmd As IDbCommand = conn.CreateCommand()
        cmd.CommandText = "EXEC MISEQ"
        Dim param As IDbDataParameter = cmd.CreateParameter()
        param.Direction = ParameterDirection.Output
        param.DbType = DbType.Int32
        cmd.Parameters.Add(param)
        cmd.ExecuteNonQuery()
        Dim miseq As Integer = CInt(param.Value)
        cmd.Dispose()
        Return miseq
    End Function

End Class

And here's the logged exception and stack trace:

Code:

2007-07-11 18:19:46,317 [1796] WARN  NHibernate.Util.ADOExceptionReporter - System.InvalidOperationException: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NHibernateTest.MISeqIdentifierGenerator.Generate(ISessionImplementor session, Object entity) in C:\data\NHibernateTest\core\MISeqIdentifierGenerator.vb:line 21
   at NHibernate.Impl.SessionImpl.SaveWithGeneratedIdentifier(Object obj, CascadingAction action, Object anything)

In case you need it, here is the mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="NHibernateTest" assembly="NHibernateTest"
   default-access="nosetter.camelcase-underscore" default-lazy="false">
   <class name="MITask" table="TASK">
      <id name="TaskSeq" type="Int32">
         <generator class="NHibernateTest.MISeqIdentifierGenerator, NHibernateTest" />
      </id>
      <property name="TaskDescription" type="String" />
      <property name="TaskResults" type="String" />
      <many-to-one name="Recommendation" class="MIRecommendation" column="RecommendationSeq" />
   </class>
</hibernate-mapping>

Am I going about this the right way? Does one of the built-in generators do what I need (and I missed it!)? Thanks for your help!

David McClelland

Naraen · **Joined:** Thu May 03, 2007 7:24 am **Posts:** 6

I have to solve a similar problem to use. I used the idgenerator code similar to what you did. One different I'm bypassing the stored proc & creating a nhibernate mapping for my keys table & accessing it just like any other object.

I didn't run into the transaction issue in this implementation.

Out of curiosity, I tried using the stored proc like you are & am able to reproduce the problem. It is strange, because that section of the NHibernate code indicates that a seperate session is created specifically for this reason.

I tried modifying your code to use a new session & it worked without a problem. E.g. Change
Dim conn As IDbConnection = session.Connection
>> To
Dim conn As IDbConnection =session.SessionFactory.OpenSession().Connection

DavyBrion · **Posted:** Thu Jul 12, 2007 6:52 am

isn't this normal? you're sharing a connection which is already in a transaction, and then you execute a command on the connection, without specifying a transaction for the command... There's really nothing else the Execute() method could do. Its connection is in a Transaction, but the command does not know about the transaction. Sounds like a valid reason to throw an exception to me.

drmcclelland · **Posted:** Thu Jul 12, 2007 7:42 am

DavyBrion - I agree with you, I would expect this exception to be thrown... my question revolves around the proper approach to getting a new ID from my database table.

Naraen - Thanks for your 2 suggestions! I'll give them a try.

DavyBrion · **Posted:** Thu Jul 12, 2007 8:15 am

drmcclelland wrote:

my question revolves around the proper approach to getting a new ID from my database table.

well, if you want it to work like an oracle sequence, you'd need to make sure that generating a new id is isolated from possible other transactions because you really don't want two records with the same ID... so creating a new ID should completely ignore the current transaction and thus, the current connection. Create and retrieve the ID in it's own isolated operation, and then use the retrieved value when you need to assign your ID to the new record you're creating.

karlchu · **Posted:** Thu Jul 12, 2007 11:39 am

David,

It sounds like the more general problem you are having is to run some arbitrary SQL statements within the same transaction. Here is how you would do it using ITransaction.Enlist():

Code:

IDbCommand cmd = myNhSession.Connection.CreateCommand();
cmd.CommandText = "... whatever ...";
cmd.CommandType = CommandType.Text;
myNhSession.Transaction.Enlist(cmd);
cmd.Execute();

DavyBrion · **Posted:** Thu Jul 12, 2007 12:24 pm

karlchu wrote:

David,

It sounds like the more general problem you are having is to run some arbitrary SQL statements within the same transaction. Here is how you would do it using ITransaction.Enlist():

Code:

IDbCommand cmd = myNhSession.Connection.CreateCommand();
cmd.CommandText = "... whatever ...";
cmd.CommandType = CommandType.Text;
myNhSession.Transaction.Enlist(cmd);
cmd.Execute();

in most cases, this is what you want to do... but not when you're emulating an oracle sequence. The new ID value really needs to be created outside of the transaction. Suppose you have 2 users who are concurrently creating a new record in the same table. You really need the generated ID to be unique in both cases, and thus not a part of the transaction which creates the record itself. If that were the case, both records could end up with the same ID and one of the users will get a constraint violation

karlchu · **Posted:** Thu Jul 12, 2007 12:37 pm

Good point, Davy. Thanks.