Parameter compatibility problem in cached Sql command.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

I'm trying to figure out a problem I've found with a cached sql command and am wondering whether anyone can point me to a workaround.

I'm using a table-per-subclass-hierarchy mapping and have two subclasses, each of which has a bag of value elements mapped to the same table. One of the subclasses is using integer values in the bag while the other uses string values in the bag. Both bags are mapped to use the same table in the database. The table has the value column mapped as an NVARCHAR to ensure all values can be stored correctly.

The problem occurs when I try to persist the class that has the integer values in the bag first and then persist the class that has string values - the batcher sees the same SQL command and assumes that it's a candidate for reuse. The result is a casting problem because the prepared command initially used SQL parameters for integer values and when re-used by the batcher attempts to cast the strings to integers (which will fail unless the string value represents an int).

If the class with the string values is persisted first then there's no problem because the integers can be cast to string parameter values, however this is not an option in my code.

Is there a way to tell Nhibernate not to try to reuse the command?

Cheers,

Symon.

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Also, here's some supporting documenation of the problem...

The mapping:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping
   xmlns="urn:nhibernate-mapping-2.2"
   namespace="Core.Domain.Parameter"
   assembly="Core"
   default-access="field.camelcase-underscore"
   default-cascade="save-update">

   <!-- Maps the base interface for all Profile parameter types -->
   <class name="ProfileParameterDefinition" table="ApplicationProfileDefinitions" discriminator-value="0">

      <id name="Id" column="ApplicationProfileDefinitionId" unsaved-value="0" access="nosetter.camelcase-underscore">
         <generator class="native" />
      </id>

      <discriminator column="Type" type="Int32" />

      <property name="Name" column="Name" />
      <property name="Category" column="Category" />
      <property name="Description" column="Description" />

      <many-to-one name="Application" column="ApplicationId" class="Core.Domain.Apps.Application" />

   </class>

   <!-- Maps the integer subclass -->
   <subclass name="IntegerProfileParameterDefinition" extends="ProfileParameterDefinition" discriminator-value="5">

      <property name="MinValue" column="Minimum" />
      <property name="MaxValue" column="Maximum" />
      <property name="RestrictToPicklist" column="RestrictToPicklist" />

      <bag name="PicklistValues" table="ApplicationProfilePicklist">
         <key column="ApplicationProfileDefinitionId" />
         <element column="PicklistValue" type="Int64" />
      </bag>

   </subclass>

   <!-- Maps the string subclass -->
   <subclass name="StringProfileParameterDefinition" extends="ProfileParameterDefinition" discriminator-value="6">

      <property name="MinLength" column="Minimum" />
      <property name="MaxLength" column="Maximum" />
      <property name="RestrictToPicklist" column="RestrictToPicklist" />

      <bag name="PicklistValues" table="ApplicationProfilePicklist"  >
         <key column="ApplicationProfileDefinitionId" />
         <element column="PicklistValue" type="String" />
      </bag>

   </subclass>

</hibernate-mapping>

This is a segment of the debug logs showing that the mapping was parsed correctly and that the types for the collections were read:

Quote:

NHibernate.Cfg.HbmBinder: 2007-09-18 11:40:47,875 [TestRunnerThread] INFO [(null)] <(null)> - mapping collection: Core.Domain.Apps.Application.ProfileParameterDefinitions -> ApplicationProfileDefinitions
NHibernate.Cfg.CollectionSecondPass: 2007-09-18 11:40:47,890 [TestRunnerThread] DEBUG [(null)] <(null)> - Mapped collection key: ApplicationID, one-to-many: ProfileParameterDefinition
NHibernate.Cfg.CollectionSecondPass: 2007-09-18 11:40:47,890 [TestRunnerThread] DEBUG [(null)] <(null)> - Second pass for collection: Core.Domain.Parameter.IntegerProfileParameterDefinition.PicklistValues
NHibernate.Cfg.CollectionSecondPass: 2007-09-18 11:40:47,906 [TestRunnerThread] DEBUG [(null)] <(null)> - Mapped collection key: ApplicationProfileDefinitionId, element: PicklistValue, type: Int64
NHibernate.Cfg.CollectionSecondPass: 2007-09-18 11:40:47,906 [TestRunnerThread] DEBUG [(null)] <(null)> - Second pass for collection: Core.Domain.Parameter.StringProfileParameterDefinition.PicklistValues
NHibernate.Cfg.CollectionSecondPass: 2007-09-18 11:40:47,906 [TestRunnerThread] DEBUG [(null)] <(null)> - Mapped collection key: ApplicationProfileDefinitionId, element: PicklistValue, type: String

And finally the debug logs of the actual inserts into the database. Note that for the IntegerProfileParameterDefinition a new IDbCommand is built but for the StringProfileParameterDefinition the IDbCommand is re-used.

Quote:

NHibernate.Persister.Collection.ICollectionPersister: 2007-09-18 12:41:51,000 [TestRunnerThread] DEBUG [(null)] <(null)> - Inserting collection: [Core.Domain.Parameter.IntegerProfileParameterDefinition.PicklistValues#41]
NHibernate.Impl.BatcherImpl: 2007-09-18 12:41:51,093 [TestRunnerThread] DEBUG [(null)] <(null)> - Opened new IDbCommand, open IDbCommands: 1
NHibernate.Impl.BatcherImpl: 2007-09-18 12:41:51,109 [TestRunnerThread] DEBUG [(null)] <(null)> - Building an IDbCommand object for the SqlString: INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (?, ?)
NHibernate.Type.Int32Type: 2007-09-18 12:41:51,125 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '41' to parameter: 0
NHibernate.Type.Int64Type: 2007-09-18 12:41:51,125 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '5' to parameter: 1
NHibernate.SQL: 2007-09-18 12:41:51,125 [TestRunnerThread] DEBUG [(null)] <(null)> - INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (@p0, @p1); @p0 = '41', @p1 = '5'
NHibernate.Type.Int32Type: 2007-09-18 12:41:51,140 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '41' to parameter: 0
NHibernate.Type.Int64Type: 2007-09-18 12:41:51,140 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '6' to parameter: 1
NHibernate.SQL: 2007-09-18 12:41:51,140 [TestRunnerThread] DEBUG [(null)] <(null)> - INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (@p0, @p1); @p0 = '41', @p1 = '6'
NHibernate.Type.Int32Type: 2007-09-18 12:41:51,156 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '41' to parameter: 0
NHibernate.Type.Int64Type: 2007-09-18 12:41:51,156 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '7' to parameter: 1
NHibernate.SQL: 2007-09-18 12:41:51,156 [TestRunnerThread] DEBUG [(null)] <(null)> - INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (@p0, @p1); @p0 = '41', @p1 = '7'
NHibernate.Persister.Collection.ICollectionPersister: 2007-09-18 12:41:51,156 [TestRunnerThread] DEBUG [(null)] <(null)> - done inserting collection: 3 rows inserted
NHibernate.Persister.Collection.ICollectionPersister: 2007-09-18 12:41:51,171 [TestRunnerThread] DEBUG [(null)] <(null)> - Inserting collection: [Core.Domain.Parameter.StringProfileParameterDefinition.PicklistValues#42]
NHibernate.Impl.BatcherImpl: 2007-09-18 12:41:51,187 [TestRunnerThread] DEBUG [(null)] <(null)> - reusing command INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (@p0, @p1)
NHibernate.Type.Int32Type: 2007-09-18 12:41:51,187 [TestRunnerThread] DEBUG [(null)] <(null)> - binding '42' to parameter: 0
NHibernate.Type.StringType: 2007-09-18 12:41:51,218 [TestRunnerThread] DEBUG [(null)] <(null)> - binding 'orb' to parameter: 1
NHibernate.SQL: 2007-09-18 12:41:51,218 [TestRunnerThread] DEBUG [(null)] <(null)> - INSERT INTO Tests.dbo.ApplicationProfilePicklist (ApplicationProfileDefinitionId, PicklistValue) VALUES (@p0, @p1); @p0 = '42', @p1 = 'orb'
NHibernate.Impl.BatcherImpl: 2007-09-18 12:41:51,265 [TestRunnerThread] DEBUG [(null)] <(null)> - Closed IDbCommand, open IDbCommands: 0
NHibernate.Util.ADOExceptionReporter: 2007-09-18 12:41:51,281 [TestRunnerThread] DEBUG [(null)] <(null)> - could not insert collection: [Core.Domain.Parameter.StringProfileParameterDefinition.PicklistValues#42]
System.FormatException: Failed to convert parameter value from a String to a Int64. ---> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

jlockwood · **Posted:** Tue Sep 18, 2007 9:33 am

You said table-per-subclass? What you have is the mapping I would use if subclasses were in the same table. Could you please give an example of your table structure?

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

jlockwood wrote:

You said table-per-subclass? What you have is the mapping I would use if subclasses were in the same table. Could you please give an example of your table structure?

Nope, I said "table-per-subclass-hierarchy" - both subclasses use the same table.

Symon,

jlockwood · **Posted:** Tue Sep 18, 2007 1:19 pm

Does the base class also use the same table as the subclasses?

jlockwood · **Posted:** Tue Sep 18, 2007 1:32 pm

If so, that's a table_per_class heirarchy.
If the abstract base is in one table and concrete classes in another, that's a table-per-concrete-class heirarchy.
If the abstract base is in one table and each subclass has it's own table, that's a table-per-subclass heirarchy.
See chapter 8 in docs for more information about inheritance, polymorhpic tables and NHibernate

I was addressing table-per-subclass heirarchy. Apprently that's not what you have. I know the others too so if you'll clarify whether the concrete classes are in a separate table or the base and all derived classes are in a single table I can help you. I was confused by the parity mismatch between the request for help for a table_per_subclass heirarchy and an hbm for a table_per_class heirarchy.

jlockwood · **Posted:** Tue Sep 18, 2007 2:09 pm

Okay, looking at the problem directly and given that you are trying to map one column to two different types in two separate subclasses...

I'm not aware off hand of a way to tell NHibernate not to reuse the command. Maybe you could save one object, flush and clear the session, then save the other...somehow I don't think that would help either. I would probably first add the NHibernate source to my project and debug it to see what's going on under the covers.

Also, couldn't you define a new mapping type to use it instead of int32? It shouldn't be much unlike custom types for stuff like Addresses, PhoneNumbers, etc (although it is a bit of a pain in the rear).

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Apologies - I mean table-per-class hierarchy.

I've looked at the NH source and it's basically caused because when NH is flushing changes it checks whether another SQL command with the same SQL *string* has already been executed and, if so, reuses that command. Really it needs to evaluate the string and the parameter types to make sure the type of problem I'm experiencing doesn't happen.

I've been looking at how I could patch the NH source, but I'm a little concerned that I'll break something else by trying to introduce the additional checks.

As for defining a new mapping, are you suggesting a create a custom UserType for both mappings that will ensure the data is marshalled from the correct type? The problem there is that I'd need to explicitly store the type in the DB which is what I'm trying to avoid doing - the current mapping is there to ensure that whatever string is in the DB will be converted to the correct type for the runtime object and that the runtime object's value will be persisted as a string...it just doesn't work, that's all. :)

I'll keep examining the NH source and see what I can come up with, but if there are any other suggestions out there I'd be very happy to hear them.

Cheers,

Symon.

jlockwood · **Posted:** Wed Sep 19, 2007 4:09 pm

Ah, okay...wel that does stink a bit. If you attempt a patch just make new tests for your situation and ensure all the other unit tests run. Hopefully current coverage will protect your change.

Good luck :)

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Jira issue created:

http://jira.nhibernate.org/browse/NH-1160

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Just in case anyone else hits the same problem, I've submitted a patch that's associated with the JIRA issue.