Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

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

Comments

Vijay said:

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

# July 11, 2011 8:34 PM

Ricardo Peres said:

@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.

# July 12, 2011 4:10 AM

Erik said:

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.

# October 26, 2011 3:14 PM

Ricardo Peres said:

@Erik:

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

# October 26, 2011 4:39 PM

Erik said:

@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.

# October 27, 2011 9:33 AM