Mapping an Enum to Database?

xelon · **Joined:** Fri Apr 11, 2008 6:13 am **Posts:** 8

I want to map Enum property of an object to a column whose type is integer(32 bit). Enum is defined as

Code:

public Enum Gender{ Male, Female};

As i said above the specified column in table is defined as 32 bit integer. And property is defined in XML mapping file as

Code:

But i'm getting this error

Code:

System.NotSupportedException: Invalid data type.

Then i changed the type of the property in XML mapping file as

Code:

But the same exception is thrown. I tried all of these again changing the type column as varchar. But nothing is changed.

I searched the web about mapping Enum properties to database using NHibernate. But all of the examples maps Enums to database as String values. is there any way to map Enums as integer to database?

Database: IBM DB2
NHibernate: 1.2.1.GA
.NET Framework: 2.0

wolli · **Posted:** Wed May 07, 2008 8:22 am

you can specify the enum as type in the mapping:

Code:

If Gender is not in the same namespace/assembly, you have to use the fully qualified name, like

Code:

xelon · **Joined:** Fri Apr 11, 2008 6:13 am **Posts:** 8

I also tried that the error is not changed

Code:

System.NotSupportedException: Invalid data type.

wolli · **Posted:** Wed May 07, 2008 8:38 am

Then I assume it's related to your database/dialect. I use it in this way with sql server and it's working. If nothing else is working, you can implement your own type with IUserType. Should be pretty simple for an enumeration.

MutantNinjaLoungeSinger · **Joined:** Thu Jun 28, 2007 5:02 pm **Posts:** 12

Do not specify the type in the mapper, and store the value in the database as a string-capable type (such as a varchar)

In this case, the GENDER column would contain the value of either 'Male' or 'Female'

nHibernate will store the ToString() value when persisting during an update, and will do the appropriate Enum.Parse() based on the mapped property's type when retrieving.

xelon · **Joined:** Fri Apr 11, 2008 6:13 am **Posts:** 8

MutantNinjaLoungeSinger,

I already tried that. But it didn't worked.

Is this a bug or problem in DB2?

tniemeyer · **Joined:** Fri May 09, 2008 10:29 am **Posts:** 4

Not sure if this helps, but I had to proxy my enum because I was saving to a character column.

using NHibernate.Type;

namespace blah
{

public enum AnEnum
{
One,
Two
}

public class AnEnumType : EnumStringType
{
public EProductAvailabilityType()
: base(typeof(AnEnum))
{
}
}
}

.... mapping .....

<property name="AnEnumProperty" type="blah.AnEnumType , blah" />

GWBasic · **Posted:** Mon May 12, 2008 3:25 pm

I've had similar problems in the past. In my case; NHibernate couldn't cast a nullable int DB column to a nullable enumeration. I ended up writing a custom implementation of IPropertyAccessor, IGetter, and ISetter to handle the conversion.

You can read my original reply at the bottom of this thread: http://forum.hibernate.org/viewtopic.ph ... highlight=

wolli · **Posted:** Tue May 13, 2008 10:43 am

I double-checked how I'm using enums in our application. With my setup bothone of these are working:

Code:

<property name="Gender" column="GENDER" />
<property name="Gender" column="GENDER" type="MyNamespace.Gender, MyAssembly"/>

Specifiying type="Gender" or type="MyNamespace.Gender" didn't work. But I got a different exception during

Code:

Configuration cfg = new Configuration();
cfg.AddAssembly( "NHibernatePlayground" );

Quote:

could not interpret type: NHibernatePlayground.NamedQuery.Gender

Your exception seems to be caused by something else and I assume, it's thrown during runtime (e.g. after configuration is build) ? I'm not familiar with DB2, but for me it looks like a problem with the column type in the database.

I'm not sure, if SchemaExport works with DB2, but try it. Then you can see what column type hibernate creates for you.

Code:

Configuration cfg = new Configuration();
cfg.AddAssembly( "NHibernatePlayground" );
new SchemaExport( cfg ).Create( false, true );

xelon · **Joined:** Fri Apr 11, 2008 6:13 am **Posts:** 8

Thanks for replies guys,

Yes, the exception is thrown at run-time. I tried with SchemaExport also. But the exception is thrown again.

I added a bug report to jira

http://jira.nhibernate.org:8080/jira/browse/NH-1318

I'm sending NHibernate logs here too

Code:

18:42:02 [10] DEBUG NHibernate.Impl.Printer - WindowsApplication10.Patient{Gender=0, ID=1, LastName=Daniels, FirstName=Jack}
18:42:02 [10] DEBUG NHibernate.Impl.SessionImpl - executing flush
18:42:02 [10] DEBUG NHibernate.Impl.ConnectionManager - registering flush begin
18:42:02 [10] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - Inserting entity: [WindowsApplication10.Patient#1]
18:42:02 [10] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands: 1
18:42:02 [10] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: INSERT INTO S65A2F1D.EMOTDB.PATIENT (FIRSTNAME, LASTNAME, GENDER, ID) VALUES (?, ?, ?, ?)
18:42:02 [10] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - Dehydrating entity: [WindowsApplication10.Patient#1]
18:42:02 [10] DEBUG NHibernate.Type.StringType - binding 'Jack' to parameter: 0
18:42:02 [10] DEBUG NHibernate.Type.StringType - binding 'Daniels' to parameter: 1
18:42:02 [10] DEBUG NHibernate.Type.PersistentEnumType - binding '0' to parameter: 2
18:42:02 [10] DEBUG NHibernate.Type.Int32Type - binding '1' to parameter: 3
18:42:02 [10] DEBUG NHibernate.SQL - INSERT INTO S65A2F1D.EMOTDB.PATIENT (FIRSTNAME, LASTNAME, GENDER, ID) VALUES (?, ?, ?, ?); p0 = 'Jack', p1 = 'Daniels', p2 = 'Male', p3 = '1'
18:42:02 [10] DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
18:42:02 [10] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands: 0
18:42:02 [10] DEBUG NHibernate.Impl.ConnectionManager - skipping aggressive-release due to flush cycle
18:42:02 [10] DEBUG NHibernate.Util.ADOExceptionReporter - could not insert: [WindowsApplication10.Patient#1] [INSERT INTO S65A2F1D.EMOTDB.PATIENT (FIRSTNAME, LASTNAME, GENDER, ID) VALUES (?, ?, ?, ?)]
System.NotSupportedException: Invalid data type.
   at IBM.Data.DB2.iSeries.MPParamConverter.normalizeData(Object& data)
   at IBM.Data.DB2.iSeries.iDB2Command.setRowOfParameterData(MpDcData[]& dcDataRow)
   at IBM.Data.DB2.iSeries.iDB2Command.execute()
   at IBM.Data.DB2.iSeries.iDB2Command.ExecuteNonQuery()
   at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
18:42:02 [10] WARN  NHibernate.Util.ADOExceptionReporter - System.NotSupportedException: Invalid data type.
   at IBM.Data.DB2.iSeries.MPParamConverter.normalizeData(Object& data)
   at IBM.Data.DB2.iSeries.iDB2Command.setRowOfParameterData(MpDcData[]& dcDataRow)
   at IBM.Data.DB2.iSeries.iDB2Command.execute()
   at IBM.Data.DB2.iSeries.iDB2Command.ExecuteNonQuery()
   at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
18:42:02 [10] ERROR NHibernate.Util.ADOExceptionReporter - Invalid data type.
18:42:02 [10] ERROR NHibernate.Impl.SessionImpl - could not synchronize database state with session
NHibernate.ADOException: could not insert: [WindowsApplication10.Patient#1][SQL: INSERT INTO S65A2F1D.EMOTDB.PATIENT (FIRSTNAME, LASTNAME, GENDER, ID) VALUES (?, ?, ?, ?)] ---> System.NotSupportedException: Invalid data type.
   at IBM.Data.DB2.iSeries.MPParamConverter.normalizeData(Object& data)
   at IBM.Data.DB2.iSeries.iDB2Command.setRowOfParameterData(MpDcData[]& dcDataRow)
   at IBM.Data.DB2.iSeries.iDB2Command.execute()
   at IBM.Data.DB2.iSeries.iDB2Command.ExecuteNonQuery()
   at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
   at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   --- End of inner exception stack trace ---
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, Int32 j, SqlCommandInfo sql, Object obj, ISessionImplementor session)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   at NHibernate.Impl.ScheduledInsertion.Execute()
   at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
   at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
   at NHibernate.Impl.SessionImpl.Execute()
18:42:04 [10] DEBUG NHibernate.Impl.ConnectionManager - registering flush end
18:42:04 [10] DEBUG NHibernate.Impl.ConnectionManager - aggressively releasing database connection
18:42:04 [10] DEBUG NHibernate.Connection.ConnectionProvider - Closing connection

xelon · **Joined:** Fri Apr 11, 2008 6:13 am **Posts:** 8

I found the solution. But it is not confirmed yet. You can look the solution at

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