Debug "String or binary data would be truncated" error in nHibernate

Question

I'm debugging an intermittent truncation error in my application. The error in my logs looks like this:

System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated. Generated: Tue, 02 Nov 2010 03:55:18 GMT

NHibernate.Exceptions.GenericADOException: could not insert:
[DataModel.Product][SQL: INSERT INTO [Product] (Fields) VALUES (?, ?,...);
select SCOPE_IDENTITY()] ---> 
System.Data.SqlClient.SqlException: String or binary data would be truncated. 
The statement has been terminated.    
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

Notice that the error does not provide parameter values for the sql that threw the error, only placeholders: (?, ?,...). Is there a way to get these from nHibernate? Something like this:

try {
    ...
    Session.Flush(); // throws
}
catch (GenericADOException ex) {

    // want to get the bad parameter values, 
    // so I can re-throw a more helpful exception
}

AYK · Accepted Answer · 2012-09-21 06:06:11Z

6

Most of the time, this string or binary data truncated.... comes from Nhibernate Ado exception when data being fed has more characters than the field length. e.g. if a table db field is nvarchar(50), but an effort is being made to insert more than 50 characters, this exception occurs. solution is to either increase the table Db field length or reduce the data characters being inserted.

edited Sep 21, 2012 at 6:06

AYK

3,3201 gold badge19 silver badges30 bronze badges

answered Sep 20, 2012 at 19:21

Rajesh

791 silver badge1 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

cbp · Accepted Answer · 2010-11-02 23:51:21Z

3

You could setup an IPreInsertEventListener that loops through the string properties prior to insertion, checking their length.

If one of the properties is too long, you can log the name of the property and its value.

answered Nov 2, 2010 at 23:51

cbp

25.7k29 gold badges134 silver badges215 bronze badges

Comments

Diego Mijelshon · Accepted Answer · 2010-11-03 00:14:26Z

2

You can configure log4net to send NH SQL output somewhere. Example:

<log4net>
  <appender name="Debug" type="log4net.Appender.DebugAppender">
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date %message%newline" />
    </layout>
  </appender>
  <logger name="NHibernate.SQL">
    <level value="DEBUG"/>
    <appender-ref ref="Debug"/>
  </logger>
</log4net>

In this case, I'm sending it to the Debug window.

answered Nov 3, 2010 at 0:14

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

Comments

Callie J · Accepted Answer · 2010-11-02 23:42:59Z

1

You might be able to drill down and find out what it's doing with nHibernate Profiler.

answered Nov 2, 2010 at 23:42

Callie J

31.4k6 gold badges74 silver badges114 bronze badges

Comments

cezarypiatek · Accepted Answer · 2018-04-16 21:23:32Z

0

The best solution seems to be event listener that validate values used in SQL (it works independently of DB, even with SQLite which ignores length constrains). I wrote a blog post with description how to implement such listener https://cezarypiatek.github.io/post/validate-fields-in-nhibernate-model/

answered Apr 16, 2018 at 21:23

cezarypiatek

1,13411 silver badges22 bronze badges

Collectives™ on Stack Overflow

Debug "String or binary data would be truncated" error in nHibernate

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related