NHibernate Pitfalls: Large Strings

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

If you want to map large string columns (SQL Server’s VARCHAR(max)/NVARCHAR(max), Oracle’s CLOB/NCLOB) to NHibernate properties, you must specify the StringClobType. This is because the default mapping type for string columns, StringType, does not support large strings (>4000 characters).

If you also want to generate the database from the model, you should also specify the native column type. For SQL Server:

   1: <property column="`LARGE_STRING`" name="LargeString" type="StringClob" sql-type="NVARCHAR(max)" />

And for Oracle:

   1: <property column="`LARGE_STRING`" name="LargeString" type="StringClob" sql-type="NCLOB" />

Of course, the .NET class remains the same, whatever the underlying NHibernate mapping type is:

   1: public virtual String LargeString { get; set; }

Bookmark and Share

                             

5 Comments

  • Recently we ran to a issue in production related to varchar(max) & NH. We resolved it by changing the mapping to Map(x => x.CSV, "CSV").CustomSqlType("varchar(MAX)").Length(2147483647);
    Which one is better? StringClob or CustomSqlType

  • @Vijay:
    It appears like you are using NHibernate's default type for Strings, which is StringType. If you are, it shouldn't support strings that large (2147483647 is very large!). Have you tried inserting/retrieving strings larger than 4000 characters? If it gets truncated, switch to StringClobType. Specifying CustomSqlType is only useful for creating the database, NHibernate doesn't use it in any other way.

  • I am working with NHibernate on both a program that is supposed to be using the hibernation layer as an interface into both SQL and Oracle databases. Recently it was decided that we wanted one of our fields to be a XML(SQL) or XMLType(Oracle) and it requires us passing either VARCHAR(SQL) or CLOB(Oracle).

    Is there anyway to set up the mapping so that i can use both of the databases? Right now i get a "ORA-01461: can bind a LONG value only for insert into a LONG column" error whenever i pass it data.

  • @Erik:
    not with static mappings, like hbm.xml. You can easily do it with the new mapping by code.

  • @Ricardo
    That is what i was unfortunately afraid of. I think ill give it a go and see if i can make it work somehow, but i get the feeling i'm going to have to make a report saying that we are going to have to store the data in strings and lose the xml functionality. Thankfully we do not have a need to use it day to day, only when we upgrade our XML format.

    Thanks for the help.

Comments have been disabled for this content.