Recently I’ve switched some of my applications from NHibernate 2.1.x to NHibernate 3, everything was working fine until I encountered a ‘strange behavior’ with the mapping of long string fields (those that should be mapped to nvarchar(max) and varchar(max)...yes I use Microsoft Sql Server as my database engine): using the standard mapping the field are correctly mapped to nvarchar(max), but during the saving operation the data gets truncated.
The trouble arise due to some small modifications to the SqlClinetDriver that were made to optimize the reuse of the query plan Sql Server will generate, you can enable the same feature in NHibernate 2.1.x setting the ‘prepare_sql’ configuration option to true.
Basically the problem is in the way the parameters of the SqlCommand are generated: without using prepare_sql=true the length of the string parameter is computed on the the data you pass in (ie: ‘aaa’ is represented by string(3)) and this is limiting the database engine capability of reusing query plans (more info in a link later on); but you can map an nvarchar(max) column like this:
<property name="StringHugeLength" column="StringHugeLength" type="string" length="10000" />
and everything works as expected.
The following commented piece of mapping sums up the behavior I’ve noticed when using prepare_sql=true:
<!-- this generates an nvarchar(255), data is saved as string(4000)-->
<property name="StringDefault" column="StringDefault" type="string" />
<!-- this generates an nvarchar(50), data is saved as string(50) -->
<property name="StringFixedLength" column="StringFixedLength" type="string" length="50" />
<!-- this generates an nvarchar(max), data is truncated at the length specified: string(10000) -->
<property name="StringHugeLength" column="StringHugeLength" type="string" length="10000" />
<!-- this generates an nvarchar(max), data is truncated at the default string length: string(4000),
the sql-type does not participate in the runtime parameter generation for the query -->
<property name="StringSqlType" type="string">
<!-- if you specify the length explictly it will truncate at the specified limit instead of the default -->
<column name="StringSqlType" sql-type="nvarchar(max)" />
</property>
<!-- this generates an nvarchar(255), reading and writing work for legacy nvarchar(max) fields -->
<property name="Blob" column="Blob" type="StringClob" />
<!-- this generates an nvarchar(max), data is truncated at the length specified: string(10000) -->
<property name="BlobLength" column="BlobLength" type="StringClob" length="10000" />
<!-- this mapping works! for generation, reading and writing -->
<property name="BlobSqlType" type="StringClob" >
<column name="BlobSqlType" sql-type="nvarchar(max)" />
</property>I have to admit I didn’t knew/used this feature in the past and I faced the problem only recently...here’s a good post from Andrei Volkov that sums up the whole story and point to the problem:
http://zvolkov.com/blog/post/2009/10/28/NHibernate-parameter-sizes-controversy.aspx
Even using those suggestions I’m still not totally satisfied of the solution; moreover the only mapping that works correctly (the last one in the previous example) does not satisfy me much, because it introduces a dependency to the specific database types.
From my point of view I would like to use the following mapping syntax to deal with ‘nvarchar(max)’ columns:
<property name="Blob" column="Blob" type="StringClob" />
This one should emit an nvarchar(max), which I consider my default type when dealing with long text, when using ddl and SchemaExport() and should be able to load and save data to that field without truncation. Tto map a varchar(max) you can use the <column> tag inside property and specify a sql-type explictly.
I have made my own modifications to the Sql Driver and Dialect to have NHibernate work the way I think it should (I really like its highly customizable environment)...more on this subject in some next posts (these changes should not have any impact on other dialects that do not have the same Sql Server problems, and you should be able to reuse your ‘new’ mapping without modifying them).
I have no clue if the NHibernate team will revert the way ‘prepare_sql’ works in the current version or if they modify things again. But in the meanwhile I’m happy with my current solution which has minimum impact over my mappings.
Happy NHibernating!
Edit: I was asked to post the modifications I made to the Sql Driver class, there’s no more need to use it since this behavior has been fixed in the latest versions of NHibernate (since 3.1.0 GA if I remind it correctly), however here is my code:
/// <summary>
/// A NHibernate Driver for using the SqlClient DataProvider
/// </summary>
public class SqlClientDriver : DriverBase, IEmbeddedBatcherFactoryProvider
{
/// <summary>
/// Creates an uninitialized <see cref="IDbConnection" /> object for
/// the SqlClientDriver.
/// </summary>
/// <value>An unitialized <see cref="System.Data.SqlClient.SqlConnection"/> object.</value>
public override IDbConnection CreateConnection()
{
return new SqlConnection();
}
/// <summary>
/// Creates an uninitialized <see cref="IDbCommand" /> object for
/// the SqlClientDriver.
/// </summary>
/// <value>An unitialized <see cref="System.Data.SqlClient.SqlCommand"/> object.</value>
public override IDbCommand CreateCommand()
{
return new System.Data.SqlClient.SqlCommand();
}
/// <summary>
/// MsSql requires the use of a Named Prefix in the SQL statement.
/// </summary>
/// <remarks>
/// <see langword="true" /> because MsSql uses "<c>@</c>".
/// </remarks>
public override bool UseNamedPrefixInSql
{
get { return true; }
}
/// <summary>
/// MsSql requires the use of a Named Prefix in the Parameter.
/// </summary>
/// <remarks>
/// <see langword="true" /> because MsSql uses "<c>@</c>".
/// </remarks>
public override bool UseNamedPrefixInParameter
{
get { return true; }
}
/// <summary>
/// The Named Prefix for parameters.
/// </summary>
/// <value>
/// Sql Server uses <c>"@"</c>.
/// </value>
public override string NamedPrefix
{
get { return "@"; }
}
/// <summary>
/// The SqlClient driver does NOT support more than 1 open IDataReader
/// with only 1 IDbConnection.
/// </summary>
/// <value><see langword="false" /> - it is not supported.</value>
/// <remarks>
/// MS SQL Server 2000 (and 7) throws an exception when multiple IDataReaders are
/// attempted to be opened. When SQL Server 2005 comes out a new driver will be
/// created for it because SQL Server 2005 is supposed to support it.
/// </remarks>
public override bool SupportsMultipleOpenReaders
{
get { return false; }
}
// Used from SqlServerCeDriver as well
public static void SetParameterSizes(IDataParameterCollection parameters, SqlType[] parameterTypes)
{
for (int i = 0; i < parameters.Count; i++)
{
SetVariableLengthParameterSize((IDbDataParameter)parameters[i], parameterTypes[i]);
}
}
private const int MaxAnsiStringSize = 8000;
private const int MaxBinarySize = MaxAnsiStringSize;
private const int MaxStringSize = MaxAnsiStringSize / 2;
private const int MaxBinaryBlobSize = int.MaxValue;
private const int MaxStringClobSize = MaxBinaryBlobSize / 2;
private const byte MaxPrecision = 28;
private const byte MaxScale = 5;
private const byte MaxDateTime2 = 8;
private const byte MaxDateTimeOffset = 10;
private static void SetDefaultParameterSize(IDbDataParameter dbParam, SqlType sqlType)
{
switch (dbParam.DbType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
dbParam.Size = MaxAnsiStringSize;
break;
case DbType.Binary:
if (sqlType is BinaryBlobSqlType)
{
dbParam.Size = MaxBinaryBlobSize;
}
else
{
dbParam.Size = MaxBinarySize;
}
break;
case DbType.Decimal:
dbParam.Precision = MaxPrecision;
dbParam.Scale = MaxScale;
break;
case DbType.String:
case DbType.StringFixedLength:
if (sqlType is StringClobSqlType)
{
dbParam.Size = MaxStringClobSize;
}
else
{
dbParam.Size = MaxStringSize;
}
break;
case DbType.DateTime2:
dbParam.Size = MaxDateTime2;
break;
case DbType.DateTimeOffset:
dbParam.Size = MaxDateTimeOffset;
break;
}
}
private static void SetVariableLengthParameterSize(IDbDataParameter dbParam, SqlType sqlType)
{
SetDefaultParameterSize(dbParam, sqlType);
// Override the defaults using data from SqlType.
if ((sqlType.LengthDefined) && !(sqlType is StringClobSqlType) && !(sqlType is BinaryBlobSqlType))
{
// we have to take into account a possible varchar or nvarchar (max), so assign the length
// to the string type and to the binary type only if it doesn't exceed the maximum allowed value
// otherwise use the MaxBinaryBlobSize or MaxStringClobSize
if (((sqlType.DbType == DbType.AnsiString) || (sqlType.DbType == DbType.AnsiStringFixedLength))
&& (sqlType.Length > MaxAnsiStringSize))
dbParam.Size = MaxBinaryBlobSize; // I'm not totally sure of this
else if (((sqlType.DbType == DbType.String) || (sqlType.DbType == DbType.StringFixedLength))
&& (sqlType.Length > MaxStringSize))
dbParam.Size = MaxStringClobSize;
else
dbParam.Size = sqlType.Length;
}
if (sqlType.PrecisionDefined)
{
dbParam.Precision = sqlType.Precision;
dbParam.Scale = sqlType.Scale;
}
}
public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
{
IDbCommand command = base.GenerateCommand(type, sqlString, parameterTypes);
//if (IsPrepareSqlEnabled)
{
SetParameterSizes(command.Parameters, parameterTypes);
}
return command;
}
public override bool SupportsMultipleQueries
{
get { return true; }
}
#region IEmbeddedBatcherFactoryProvider Members
System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
{
get { return typeof(SqlClientBatchingBatcherFactory); }
}
#endregion
}
Related Content
- Sql Server - Scramble / Mask database production data (26/08/2015)
- Install SQL Server (Express) with your Application (26/08/2015)
- Breaking news: the First European NHibernate Day had been officially announced (26/08/2015)
- NHibernate - First impressions on ConfORM (26/08/2015)
- NHibernate - Customize the Linq provider to call your user defined SQL functions (10/01/2010)
- More related document (23)
