Page 1
I just have to write this down. Especially for reminding me about this “obstacle” I had and maybe someone will read it and will saves him or her 3 days of his/her life (and project).
In my current project, I’m using NHibernate for mapping an Oracle database towards objects that later will be bound to an WPF application. All three technologies are completely new to me so you can imagine that I’m learning every day new things at a very high pace. The past 2 weeks I was very successful in mapping the tables to objects and writing small Unit Tests that retrieves the objects and their relations from the DB.
Everything moved forward at a steady pace until last Friday when I started to write my first Insert and Updates. Suddenly, things just got on a hold. When trying to Insert (Save) a new object, the test just kept on running (read hanging). When trying to Update an object, it threw me an Exception (Rollback fails with sql error – or something similar).
First, no problem. After all, it was Friday afternoon, let’s try a few more things, if it doesn’t work then pick it up on Monday (I’m the only developer here so no concern about leaving a broken build over the week-ends, though it’s still best practices NOT to do this ;).
Monday morning, renewed effort in trying to solve the problem. Look at simple insert examples, search the entire web for solutions, post on forums, try to create basic inserts using plain ADO.NET, try finding other people in the building who have some kind of .NET knowledge to help me out… Turned out, I spend 2 whole days doing all of this, without much progress at all. Though, following constatations where made.
- Select queries do work. After all, I’ve been retrieving objects through Unit Tests for 2 weeks now.
- This means that the DB connection should be OK. Could it be that Oracle can block Insert and Update statements from .NET apps?
- A quick example using plain ADO.NET (System.Data.OracleClient) showed that an insert was possible through .NET on this server. ==> so it’s NHibernate?
Frustrations are starting to come up.
Debugging
One thing I was trying during these 2 days without success was hooking up the NHibernate code to the debugger so I could step into the code.
Unsuccessful, until today, when I finally managed to hook up the NHibernate code to my debugger. (I still don’t know exactly how I managed to do it, but all of a sudden I was inside the code). And this is where it became interesting.
After a few minutes going through the code, I was able to locate very quickly the point of failure…
...
try
{
return cmd.ExecuteNonQuery();
}
...
Great, so it’s at the ADO.NET level and NOT NHibernate. And I do have a working example using plain System.Data.OracleClient objects, so let’s compare both Command objects before execution.
After a while, it turned out that the difference was in the DbType of the string parameter.
My “not working” command had the type set to “String”…
while my working command had the type to “AnsiString”
My original test looked like this:
cmd.Parameters.Add(":p0", System.Data.OracleClient.OracleType.VarChar);
cmd.Parameters[":p0"].Value = "MyValue";
I used a VarChar as OracleType which was bound to a DbType.AnsiString.
When I changed the OracleType to NVarChar…
cmd.Parameters.Add(":p0", System.Data.OracleClient.OracleType.NVarChar);
cmd.Parameters[":p0"].Value = "MyValue";
… the DbType turned to plain String, and my ADO.NET example failed as well.
For some reason, NHibernate maps all my strings to a DbType.String.
The cause
I actually ran into this problem already when I was mapping a string value to an ID so I knew about it. I think I can better let James Kovac explain it in his post.
The solution
As explained in Jame’s post, the solution is very simple. In your mapping file, set the type property for each String to “AnsiString”. The parameter command will be created correctly and the Insert and Updates will succeed.
<property name="Description" column="DES" type="AnsiString" />
Now why was it always working when I did a select? Because up till now I only did a “Get object by ID”. And the ID was always an Integer (except in 1 case which I solved before by also specifying the string type).
Conclusion
This small thing took me almost 3 whole days to solve. Quite a waste of time if you look at the solution. I must say, you end up with quite some frustrations, after all, the project’s reserved developing time keeps moving forward.
On the other end, it is quite a victory when solving the problem by yourself after such a period. After all, that’s why we de developing. To solve problems and to learn new things along the way.
Now let’s continue writing the data layer and then see how to hook it all up to an WPF applications (which is also completely new to me).