NHibernate - prepare_sql and some considerations on mapping very long string fields

Print Content | More

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
}


Nhibernate, Nvarchar(max), Prepare_sql

5 comments

Related Post

  1. #1 da Eddie - Thursday September 2010 alle 11:12

    A related question to this...
    Im using fluent NHibernate, and when retrieving data from a sql database that has "text" sql columns i get returned an empty string of length x where x is the length of characters a text field can contain.
    Is there a way to map text columns in nhibernate or fluent nh which will make sure if empty strings are returned they are 0bytes in length?
    Im currently getting around this issue by calling the .trim() method in code after i retrieve the rows however this isnt what i want to do as its not good practice.

    Manny thanks.

  2. #2 da alessandro giorgetti - Friday September 2010 alle 08:46

    I haven't noticed this behavior, when I get data from string fiends - varchar(x) or nvarchar(x) - I alwyas get back empty strings. I haven't tried what happens using NTEXT fields, because they are considered obsolete. But i think this is a bug and you should report it.
    Anyway you get get around this creating you custom IUserType and do the trimming there.

  3. #3 da Michael - Monday June 2011 alle 11:43

    Thanks for explaining this behavior. I've arrived at the same conclusion as you have; I would not like to change my mapping files. Would you be open to sharing the contents of your Sql Driver and Dialect and how you plugged it into NHibernate?

  4. #4 da alessandro giorgetti - Tuesday June 2011 alle 09:38

    If you upgrade to the latest version of NHibernate there's no more need to make such changes because this behavior has been fixed.

    If you still use NH 2.1.x I've updated the post to show my code for the SqlDriver (you can use it specifying the type in the config file section 'connection.driver_class' or configuring it by code) :

  5. #5 da Michael - Tuesday June 2011 alle 12:41

    Thanks for posting the class. I'm using 3.1 and am still getting trucated results. That might explain why the solution you give above didn't work for me. It wouldn't have been ideal anyways because I use SQLite to unit test my mappings. I'll investigate it further.

All fields are required and you must provide valid data in order to be able to comment on this post.


(will not be published)
(es: http://www.mysite.com)