After my first correction of the original post, on 11/2, Naz replied to the NH thread. He clarified that prepare_sql is in fact NOT defaulted to true in NH 2.1, and so the users will still have to make this configuration change by hand.
The same day, Trent Niemeyer in a comment to my blog post corrected another Carsten Hess's statement and informed me that (at least in v2.1) NHibernate does NOT ignore length attribute in the mappings and that undocumented type="String(100)" syntax is no longer the only way to effectively specify the length of query parameters.
I just verified both news and found them to be correct (and their original counterparts, correspondingly, wrong). As V.I. Lenin once said: "Trust, but verify!"
To summarize, all we need to do is:
- set prepare_sql to true
- (optional) add type="AnsiString" and length="LENGTH" to mappings of string properties to make NH use varchar(LENGTH) instead of nvarchar(4000) for most basic queries
- Keep an eye on more complex queries and be ready to use the overload of SetParameter that takes TypeFactory.GetAnsiStringType(LENGTH)
The reason prepare_sql is an opt-in feature is because it may change behavior of existing applications. Still, it is highly recommended that users of MS SQL Server set it to true, to avoid the query cache pollution. The alternative solution -- the SqlDriverOverride hack -- can be employed to keep the NH-generated SQL queries visible in SQL Profiler every time they are executed, and not once per connection, which is what happens when prepare_sql is set to true.
Tags: