Monthly Archives: June 2008

Columns’ case-sensitivity in NHibernate

The other day I wanted to create an HQL query to retrieve data from one object (WorkOrderFault) that has many-to-many relation with another. so I created the following:

ISession session = NHibernateOrmSessionFactory.CurrentNHibernateSession;

IQuery query = session.CreateQuery(

   “select wof from WorkOrderFault wof join wof.WorkOrderTechnicians as tech where tech.Id = 43334”);

IList<WorkOrderFault> objects = query.List<WorkOrderFault>();

The query ran successfully, and I got my results.

Then I wanted to use paging and get certain amount of results starting from certain record, so I added these two lines directly after I instantiated the IQuery object :

query.SetMaxResults(10);

query.SetFirstResult(0);

Simple and nice, but instead I got the following error:

System.Data.SqlClient.SqlException : The column ‘FaultId10_’ was specified multiple times for ‘query’.

When I checked my mapping file of WorkOrderFault, it had the following lines (I am including the lines we are interested in only):  

<property name=FaultId type=System.Int32 column=FaultID not-null=false access=field.camelcase-underscore/> 

<many-to-one name=Fault class=GRP.Maintenance.Domain.Settings.Faults column=FaultId

                fetch=select insert=false update=false not-found=exception

                access=field.camelcase-underscore/>

Ok, I know it’s wrong to map the same column for two different properties (don’t ask about the reason), but this is the current situation; one property to hold the Id (as an integer), and another property to hold everything. There might be more justifying situations where you want to map two properties to one column, so let’s assume it’s ok.

NHibernate is smart enough, when using queries, to query the database field only once; in situations like this NHibernate figures out that there are two properties mapped to one column so it shouldn’t retrieve it twice (i.e select columnx as x1, columnx as x2…..).
But not in this case!! It just didnt’ work!

I had no explanation for this, except when I looked closely to the map file, I noticed that the field that was causing the problem “FaultID” was written once with capital d (D), and the other with small d (d)!

So as it appears, NHibernate has schizophrenia when it comes to database columns case sensitivity; because SQL itself is case insensitive, but NHibernate code distinguishes between the uppercase and lowercase.

Keep an eye on your map files, try to make them EXACTLY the case like the database is, and unify that through all your map files.

UPDATE:
the effect SetMaxResults() produced is it wrapped the original SQL sentace with “WITH query AS (…“, and only then the SQL refused the duplicate columns in the result, hence the SQLException took place.

original SQL: “select workorderf0_.RecID as RecID10_, workorderf0_.WorkOrderID….

SQL after SetMaxResults: “WITH query AS (SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__,  workorderf0_.RecID as RecID10_…

Suspecting already used code

In one of the modules I am working on, my unit tests used to take tremendous amount of time (4 minutes per test  clip_image001). I inherited the NHibernate config file from previous NHibernate test module that was used somewhere before!

At the beginning I thought that this is something common when you use NHibernate with rich applications, so I didn’t pay much attention, but then it became a real headache.

One of the best colleagues notified me about an important note, which is that I was loading other domain objects from a shared  module (which I didn’t really need).
I checked the NHibernate config file, and removed the mapping line that includes those shared domain objects, and the time shrank to 15 seconds!clip_image002

The moral of the story is that it’s ok to suspect code we inherit from others, other modules or projects, we might not gain anything by doing that, but I am almost sure that we won’t lose!

What is next

Ok, now I am facing a hard decision; what to do next in my technical life?

In order to reach for better decision, I drew a mind map using this wonderful online tool Mindomo, it’s like  the following:

most important

Those nodes are the things I think I want to study/do most.
I marked the most important ones with the red, yellow and then blue flags. Then marked the ones I need to do most urgently with 1 being the most urgent, 2 less urgent and so on.

We use NHibernate at work, it’s interesting technology and very much talked about recently, knowing it in details helps to be more productive and give my experience real credit for it.
So I thought about doing some session about it with anyone interested about it here at work (we need to refresh out technical enthusiasm),  my plan is to give a brief introduction on it with some samples, then have a longer discussion about it.
I gave it the highest mark of urgency because we usually are pressured at work and we don’t have the luxury to “sharpen our saws” by having such sessions (I know…don’t look at me like that!). so it takes the first place.

Second in  place, studying for the ASP.NET exam of Microsoft (which is something really cared about here), and I better take the exam before it’s too late.

Thy cycle goes on clock-wise, till it reaches this charity web application, then I thought why not use this chance of creating web application to learn the “Javascript in depth”, “Ajax”, and  “jQuery”?!
and this is what I will do by god willing.

If I want to do that I better commit to what i have just posted, so wish me luck…and if you think you can enjoy open source project, contact me and we might do the charity web application together :)

Builds while deployment

In my company, we are working on this big GRP project, lots of pages, projects and workflows, soon we are delivering some modules to the customer.
So today, while trying to test a workflow that we have been working for days, something weird happened; the users of the workflow didn’t exist anymore!!

After searching for a while, it appeared that we are delivering some modules, and real data needed to be deployed on a database especially for deployment, basically the Users table was the primary table! so as simple as it gets…they created the new database and changed the users we used for the real users (along with the hierarchical structure of the employees).
Ok…that’s fine for now, but the problem is that they wanted us to work on the new data; the action was that they changed the users on the development database on our development environment!

That took place with late alert, not only that, but I believe that the change should always start from the development database (not the opposite),  notifying all developers of the plan and including them in the change, and only then…the change is reflected to the deployment database.

The team I work with was halted for more than half a day, trying to reorganize the users with the proper structure to start the workflow again!

How do you handle deployment issues when it comes to real data deployment?