I have a problem where I read an object from the database that contains a column representing a boolean. In MS SQL it works fine but in Oracle it fails with the exception detailed below.

I debugged the code and found that the problem is when calling .NET's Convert.ToBoolean method inside NHibernate's BooleanType.Get(IDataReader rs, int index). The problem is that Oracle is returning "1" or "0" as strings and when Convert.ToBoolean is called it ends up calling Boolean.Parse. Inside this method a case insensitive comparison is made to Boolean.TrueString or Boolean.FalseString, which are set to True and False, respectively. Therefore, an exception is thrown.

I tried using substitutions but I don't seem to get it to work correctly. Any ideas?

-----------------------------------------------------------------------------------

Hibernate version: NHibernate 1.0.3

Full stack trace of any exception that occurs:
NHibernateProvider: unable to get user; operation failed with error "Could not execute query".

Base Exception Message: "String was not recognized as a valid Boolean."

Base Exception Stack Trace: at System.Boolean.Parse(String value)
at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
at System.Convert.ToBoolean(Object value)
at NHibernate.Type.BooleanType.Get(IDataReader rs, Int32 index) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Type\BooleanType.cs:line 40
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Type\NullableType.cs:line 265
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Type\NullableType.cs:line 207
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Type\AbstractType.cs:line 127
at NHibernate.Loader.Loader.Hydrate(IDataReader rs, Object id, Object obj, ILoadable persister, ISessionImplementor session, String[][] suffixedPropertyColumns) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 824
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, Key key, String suffix, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 751
at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, String suffix, Key key, LockMode lockMode, Key optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 709
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, String[] suffixes, Key[] keys, Object optionalObject, Key optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 631
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, IList hydratedObjects, Object optionalObject, Object optionalId, Key[] keys, Boolean returnProxies) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 253
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 348
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 168
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 1311
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Loader\Loader.cs:line 1299
at NHibernate.Hql.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Hql\QueryTranslator.cs:line 1006
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters) in D:\TeamProjects\A\Areas\DD\Src\NHibernateProvider\NHibernate\Impl\SessionImpl.cs:line 1750

Name and version of the database you are using: MS SQL 2000, 2005 & Oracle 10g

You will have to write your own usertype to do the conversion on Oracle.

Instead of using CHAR(1) to represent a boolean I opted to use NUMBER(1,0). Essentially, it gives me the same result except that now it is treated as a number and not as a string. Doing so NHibernate is able to parse it accordingly without errors.

I still think there should be an easier way to get around this problem. Even a user type may cause more problems in the long run to ensure portability. Don't you think this is a general problem?

The reason I was in this situation is because I am using MS Visio for Enterprise Architects to model the database using MS SQL as the default driver. When I switch drivers the model is automatically converted to the Oracle equivalent. I'm sure others will see the same and I'm surprised I could not find anything about it. I guess many NHibernate users stick to databases other than Oracle. ;)

The problem with using NUMBER(1,0) is that it cannot be done in the Viso model. It must be done in the DDL script afterwords, which causes more maintenance work. But at least it works for now for what I need to test.

Thanks for your response.

I thought more about the pros and cons of forcing a change to the database to handle the scenario I described above. Although changing the boolean representation from CHAR(1) to NUMBER(1,0) works, what if you cannot make that change?

So, I followed your suggestion and created a user type. It works fine since I am able to do the check myself before delegating to the BooleanType class to do the get operation. However, I am faced with another dilema. I now have to go throughout my mapping and specify this user type for every instance of a boolean. Is there a way to register the user type to replace all cases of a specific NHibernate or base type? Perhaps in the configuration file? Thanks again.