Sony Arouje

I am working on a project to create a webservice on top of a Legacy Oracle db. As usual I used NHibernate as the ORM. Every thing is working as expected until I hit a table with a field type of ‘Long’. NHibernate freezes whenever it try to insert records to this table. NHibernate logging was enabled and I could see no action is taking place after issuing the Insert command to this table. So to pin point the issue I commented out the filed with LONG type from the hbm mapping. Then I repeated the test, this time insertion went well without any freezing.

I confirmed that the issue is with this particular Oracle Long datatype field. I guessed that parameter type NHibernate set for this filed is the culprit. I explicitly set the type in hbm file and tried many types supported by NHibernate, but in vain. I wasted so much time trying different types in hbm and googling for a solution, at last I decided to come up with a custom type to solve this issue. I got a starting point from a blog post of nforge.

Let’s see the Custom type I created for Oracle Long.

using System;
using System.Data;
using NHibernate.UserTypes;
using NHibernate;
using Oracle.DataAccess.Client;
namespace Net.Infrastructure.Persistence
{
    public class OracleLongVarcharType:IUserType
    {
        private static readonly NHibernate.SqlTypes.SqlType[] SQL_TYPES =

            { NHibernate.NHibernateUtil.StringClob.SqlType };

        public object Assemble(object cached, object owner)
        {
            return owner;
        }

        public object DeepCopy(object value)
        {
            return value;
        }

        public object Disassemble(object value)
        {
            return value;
        }

        public bool Equals(object x, object y)
        {
            if ( object.ReferenceEquals(x,y) ) return true;
            if (x == null || y == null) return false;
            return x.Equals(y);
        }

        public int GetHashCode(object x)
        {
            if (x == null)
                return 0;

            return x.GetHashCode();
        }

        public bool IsMutable
        {
            get { return false; }
        }

        public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
        {
            object obj = NHibernateUtil.String.NullSafeGet(rs, names[0]);
            if (obj == null) return null;
            string val = Convert.ToString(obj);
            return val;
        }

        public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
        {
            if (value == null)
                ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
            else
            {
                OracleCommand oraCmd = cmd as OracleCommand;
                if (oraCmd != null)
                {
                    oraCmd.Parameters[index].OracleDbType = Oracle.DataAccess.Client.OracleDbType.Long;
                    oraCmd.Parameters[index].Value = Convert.ToString(value);
                }
                else
                {
                    ((IDataParameter)cmd.Parameters[index]).Value = Convert.ToString(value);
                }

            }
        }

        public object Replace(object original, object target, object owner)
        {
            return original;
        }

        public Type ReturnedType
        {
            get { return typeof(string); }
        }

        public NHibernate.SqlTypes.SqlType[] SqlTypes
        {
            get { return SQL_TYPES; }
        }
    }
}

I don’t think the implementation need much explanation. Pay attention to NullSafeGet and NullSafeSet. In NullSafeSet I cast the IDBCommand to Oracle command, then reassign the parameter type as OracleDbType.Long. That’s it, we created a new custom type to handle Oracle Long type.

Let’s see how to add this new type to the mapping in hbm file.

<property name="TextData" column="TEXT_DATA" not-null="false" access="property" 

type="Net.Infrastructure.Persistence.OracleLongVarcharType,Net.Infrastructure.Persistence"/>

The Insert command NHibernate create before adding the custom type is as shown below

INSERT INTO TEXT_STORAGE (TEXT_DATA, CHANGE_DATE, CHANGE_USER, TEXT_NUMBER) VALUES (:p0, :p1, :p2, :p3);:p0 = ‘Purchase order text’ [Type: String], :p1 = 1352287328 [Type: Int64 (0)], :p2 = ‘sarouje’ [Type: String (0)], :p3 = 100588726 [Type: Int64 (0)]

after adding the Custom type

INSERT INTO TEXT_STORAGE (TEXT_DATA, CHANGE_DATE, CHANGE_USER, TEXT_NUMBER) VALUES (:p0, :p1, :p2, :p3);:p0 = ‘Purchase order text’ [Type: String (0)], :p1 = 1352287328 [Type: Int64 (0)], :p2 = ‘sarouje’ [Type: String (0)], :p3 = 100588726 [Type: Int64 (0)]

Update

Once I did some testing I realize that NHibernate is not fetching data from the table with Long type. After some search I came to know that, while creating the Oracle Command we should set InitialLONGFetchSize to a non zero value. By default OracleCommand set InitialLONGFetchSize to zero. If the InitialLONGFetchSize is zero then data retrieval is deferred until that data is explicitly requested by the application. Even I request explicitly nothing is returning. So only option left is set  InitialLONGFetchSize to a non zero. Then the question is how will I get the Command to set this property. I researched for Listeners, Interceptors, etc, nothing helped me. So I decided to inherit from OracleDataClientDriver and create my own as shown below.

using NHibernate.Driver;
using Oracle.DataAccess.Client;
namespace Net.Infrastructure.Persistence
{
    public class OracleDriverExtended:OracleDataClientDriver 
    {
        public override void AdjustCommand(System.Data.IDbCommand command)
        {
            OracleCommand cmd = command as OracleCommand;
            if (cmd != null)
                cmd.InitialLONGFetchSize = -1;
        }
    }
}

Now we have to instruct NHibernate to use the new driver I created, update the driver info in hibernate.cfg.xml as shown below.

<property name="connection.driver_class">Net.Infrastructure.Persistence.OracleDriverExtended,

Net.Infrastructure.Persistence</property>

I reran the test and now I could see the data from the Long field. Relieved….

Hope this post will help some one. Happy coding.