DB2 Issues

GuidoTapia · **Posted:** Mon Jul 18, 2005 6:10 pm

Hi All,

Just trying to migrate from MS SQL Server -> DB2 and having a few issues. I seem to be able to use Objects without boolean mappings fine but booleans seem to cause a few headaches.

My hib.cfg.xml is pretty straight forward:

Code:

<session-factory name="SF">
   <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>   
   <property name="connection.driver_class">NHibernate.Driver.DB2Driver</property>
   <property name="dialect">NHibernate.Dialect.DB2Dialect</property>
   <property name="connection.connection_string">PWD=db2admin;UID=db2admin;Database=RSv3_0</property>      
   <property name="show_sql">true</property>      
   <property name="hibernate.cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider,NHibernate.Caches.SysCache</property>
   <property name="hibernate.cache.use_query_cache">true</property>      
</session-factory>   

My Object mapping file:

Code:

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="PicNet.RiskShield.DAL.User, RiskShieldDAL" table="`User`">
      <jcs-cache usage="read-write"/>
      
      <id name="ID" column="`UserID`" unsaved-value="0">
         <generator class="native"/>
      </id>
      
      <many-to-one name="BusinessUnit" column="`BusinessUnitID`" not-null="false"/>
      <property name="UserPassword" column="`UserPassword`" type="String" not-null="true" length="25"/>
      <property name="FirstName" column="`FirstName`" type="String" not-null="true" length="50"/>
      <property name="LastName" column="`LastName`" type="String" length="50"/>
      <property name="Email" column="`Email`" type="String" not-null="true" length="150"/>
      <property name="IsEnterprise" column="`IsEnterprise`" type="Boolean" not-null="true"/>
      <property name="IsSystemAdministrator" column="`IsSystemAdministrator`" type="Boolean" not-null="true"/>
      <property name="IsActive" column="`IsActive`" type="Boolean" not-null="true"/>
   </class>
</hibernate-mapping>

An excerpt from the hib.log:

Code:

2005-07-18 17:25:53,483 [1660] DEBUG NHibernate.Engine.Cascades [] <> - unsaved-value: 0
2005-07-18 17:25:53,483 [1660] DEBUG NHibernate.Impl.SessionImpl [] <> - SaveOrUpdate() unsaved instance
2005-07-18 17:25:53,483 [1660] DEBUG NHibernate.Impl.SessionImpl [] <> - calling OnSave()
2005-07-18 17:25:53,499 [1660] INFO NHibernate.Impl.SessionImpl [] <> - executing insertions
2005-07-18 17:25:53,499 [1660] DEBUG NHibernate.Persister.EntityPersister [] <> - Inserting entity: PicNet.RiskShield.DAL.User (native id)
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - Building an IDbCommand object for the SqlString: INSERT INTO "User" ("LastName", "IsSystemAdministrator", "FirstName", "Email", "UserPassword", "IsActive", "IsEnterprise", "BusinessUnitID", "UserID") VALUES (:"LastName", :"IsSystemAdministrator", :"FirstName", :"Email", :"UserPassword", :"IsActive", :"IsEnterprise", :"BusinessUnitID", default)
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - about to open: 0 open IDbCommands, 0 open DataReaders
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - Building an IDbCommand object for the SqlString: values IDENTITY_VAL_LOCAL()
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Persister.EntityPersister [] <> - Dehydrating entity: PicNet.RiskShield.DAL.User#
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'Last Name' to parameter: 0
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'True' to parameter: 1
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'First Name' to parameter: 2
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding '[email protected]' to parameter: 3
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'pwd' to parameter: 4
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'True' to parameter: 5
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding 'True' to parameter: 6
2005-07-18 17:25:53,514 [1660] DEBUG NHibernate.Type.NullableType [] <> - binding '1' to parameter: 7
2005-07-18 17:25:53,514 [1660] INFO NHibernate.Impl.BatcherImpl [] <> - Preparing INSERT INTO "User" ("LastName", "IsSystemAdministrator", "FirstName", "Email", "UserPassword", "IsActive", "IsEnterprise", "BusinessUnitID", "UserID") VALUES (?, ?, ?, ?, ?, ?, ?, ?, default)
2005-07-18 17:25:53,546 [1660] ERROR NHibernate.Persister.EntityPersister [] <> - Specified cast is not valid.
Exception: System.InvalidCastException
Message: Specified cast is not valid.
Source: IBM.Data.DB2
at IBM.Data.DB2.DB2Parameter.b(h& A_0)
at IBM.Data.DB2.DB2Parameter.c(h& A_0)
at IBM.Data.DB2.DB2ParameterCollection.g()
at IBM.Data.DB2.DB2Command.b()
at IBM.Data.DB2.DB2Command.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)

2005-07-18 17:25:53,546 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - done closing: 0 open IDbCommands, 0 open DataReaders
2005-07-18 17:25:53,546 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - done closing: -1 open IDbCommands, 0 open DataReaders
2005-07-18 17:25:53,546 [1660] ERROR NHibernate.ADOException [] <> - Could not save object
Exception: System.InvalidCastException
Message: Specified cast is not valid.
Source: IBM.Data.DB2
at IBM.Data.DB2.DB2Parameter.b(h& A_0)
at IBM.Data.DB2.DB2Parameter.c(h& A_0)
at IBM.Data.DB2.DB2ParameterCollection.g()
at IBM.Data.DB2.DB2Command.b()
at IBM.Data.DB2.DB2Command.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)
at NHibernate.Persister.EntityPersister.Insert(Object[] fields, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledIdentityInsertion.Execute()
at NHibernate.Impl.SessionImpl.DoSave(Object obj, Key key, IClassPersister persister, Boolean replicate, Boolean useIdentityColumn, CascadingAction cascadeAction, Object anything)
at NHibernate.Impl.SessionImpl.DoSave(Object obj, Object id, IClassPersister persister, Boolean useIdentityColumn, CascadingAction cascadeAction, Object anything)
at NHibernate.Impl.SessionImpl.SaveWithGeneratedIdentifier(Object obj, CascadingAction action, Object anything)

2005-07-18 17:25:53,546 [1660] DEBUG NHibernate.Transaction.AdoTransaction [] <> - rollback
2005-07-18 17:25:53,561 [1660] DEBUG NHibernate.Impl.SessionImpl [] <> - transaction completion
2005-07-18 17:25:53,561 [1660] DEBUG NHibernate.Impl.SessionImpl [] <> - running ISession.Dispose()
2005-07-18 17:25:53,561 [1660] DEBUG NHibernate.Connection.ConnectionProvider [] <> - Closing connection
2005-07-18 17:25:53,561 [1660] DEBUG NHibernate.Transaction.AdoTransaction [] <> - running AdoTransaction.Dispose()
2005-07-18 17:25:53,561 [1660] DEBUG NHibernate.Impl.BatcherImpl [] <> - running BatcherImpl.Dispose()

Any help greatly appreciated.

Thanks All

Guido Tapia

cmaslen · **Posted:** Wed Jul 20, 2005 1:06 am

Hi Guido,

What DB2 data type are you using to store your booleans? I have only been able to get this working using CHAR(1)

A sample property mapping would be:

<property name="IsEnterprise" column="`IsEnterprise`" type="YesNo" not-null="true"/>

This will map back to 'Y' or 'N' being a valid value in your column

or you could use:
<property name="IsEnterprise" column="`IsEnterprise`" type="TrueFalse" not-null="true"/>

To map 'T' or 'F'.

Either way this maps back to a boolean property in your class.

gatapia · **Joined:** Sat Jun 11, 2005 1:56 am **Posts:** 19

Thanks Christian,

I had played around with this option, but having to support existing clients using SQLServer with bit data types for booleans causes issues with this solution. I really dont want to loose my database independance and changing existing clients DB structure is out of the question.

I used the SchemaExport utility to create the DB2 tables and it mapped booleans to smallint which is really what I would like to get to work.

Thanks Anyways,

Guido

timw · **Joined:** Thu Aug 14, 2008 6:17 pm **Posts:** 1

So I realize this happened years ago, but I'm experiencing almost the exact same problem where we're trying to migrate from sql server to DB2 and have an existing table already using bit that we don't want to change, but the boolean type doesn't seem to correctly map to smallint for DB2. (looking at the nHibernate source code, the DB2 dialect does register DbType.Boolean as a smallint)

I may have to just use the 'TrueFalse' type and then also change the existing sqlserver databases to have a char(1) field, but if there's any way to avoid that it'd be preferable. Does anyone have a better solution to this? One thing I considered is creating a user type that would act like boolean, but map to adifferent db type when I am using DB2, but I wasn't quite sure how to do that.

My web.config:

Code:

      <property name="hibernate.dialect">NHibernate.Dialect.DB2Dialect</property>
      <property name="hibernate.connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
      <property name="hibernate.connection.driver_class">NHibernate.Driver.OdbcDriver</property>
      <property name="hibernate.connection.connection_string">Driver={IBM DB2 ODBC DRIVER}; Database=db; hostname=host; port=port;protocol=TCPIP; uid=db2admin; pwd=db2admin; </property>
      <property name="hibernate.use_outer_join">true</property>
      <property name="hibernate.default_schema">VHSysConfig</property>

Mapping file:

Code:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

<class name="A" table="B" lazy="true">
<composite-id name="Id" class="A">
<key-property name="C" column="C" type="string" />
<key-property name="D" column="D" type="string" />
</composite-id>
<property name="E" column="E" type="boolean" not-null="true" />
</class>
</hibernate-mapping>

scue · **Joined:** Mon May 17, 2010 4:15 am **Posts:** 1

So I realize this happened years ago ... too. As this bug in version 2.1.2.4000 currently exists, so I work around by not accessing the property but the field and changed datatype from bool to Int16 within mapping, but let a bool exists as public property on entity-class:

within hbm.xml.
<property name="_activeUserInt16" column="ACTIVE_USER" type="Int16" not-null="true" access="field" />

entity-class:
public class user
{
//...
Int16 _activeUserInt16;
//...

public virtual bool ActiveUser
{
get { return _activeUserInt16 != 0 ? true : false; }
set { _activeUserInt16 = value == true ? ( Int16 ) 1 : (Int16 ) 0; }
}
//...
}

So the bool-property is visible within UI and DB2 gets satisfied with its Int16, becaase NHibernate access the field, not the property.
Imho its a ugly bug. Within NHibernate.Dialect.Db2Dialect the mapping is declared (RegisterColumnType(DbType.Boolean, "SMALLINT");) and works well for schema generaton (bool columns are created as SMALLINT). But an update is running into an error because the bool is pass through. Perhaps this bug is out in version 3.0. Hopefully.