Sony Arouje

a programmer's log

NHibernate and Oracle Long Varchar field

with 3 comments

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.

Advertisements

Written by Sony Arouje

November 7, 2012 at 5:42 pm

3 Responses

Subscribe to comments with RSS.

  1. a related queiston. we have a parent/child relationship set up between class A and class B.class B has a back-pointer to it’s parent parentA. the mapping for the property in Class B looks like this (sorry for the formatting) As can see, cascade is set to none . So i assumed that when we saved an instance of the child class B nothing would happen to the parent object A. Ah, but it turns out that if you update the instance of class A which is hanging off of class B and the update class B the change to A is persisted to the database and the version number on class A is incremented. Sort of exactly the same behaviour i would have expected if cascase= true . Any thoughts on why this is happening, anyone?thanks,Garth Engdahl

    Jatin

    November 17, 2012 at 3:27 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: