Retrieve a trigger error message from HibernateException ?

tomjo · **Joined:** Wed Aug 02, 2006 6:11 am **Posts:** 8

Is it possible to retrieve a detailed error message from a trigger (thrown by SQL Server's RAISERROR) from HibernateException ?

When a SQL server trigger fails, the error message from hibernate looks like this:

Code:

NHibernate.HibernateException: SQL insert, update or delete failed (expected affected row count: 1, actual affected row count: -1). Possible causes: the row was modified or deleted by another user, or a trigger is reporting misleading row count.
   at NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
   at NHibernate.Persister.EntityPersister.Insert(Object id, Object[] fields, Boolean[] notNull, SqlString sql, Object obj, ISessionImplementor session)
   at NHibernate.Persister.EntityPersister.Insert(Object id, Object[] fields, Object obj, ISessionImplementor session)
   at NHibernate.Impl.ScheduledInsertion.Execute()
   at NHibernate.Impl.SessionImpl.Execute(IExecutable executable)
   at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
   at NHibernate.Impl.SessionImpl.Execute()
   at NHibernate.Impl.SessionImpl.Flush()
   at NHibernate.Transaction.AdoTransaction.Commit()

However, it seems difficult to retrieve a more detailed error message even though the trigger raises an error message.

For example, if the end of the trigger looks something like this:

Code:

CREATE myTrigger ON myTable FOR INSERT AS
...
RAISERROR ('Some detailed error message' , 10 , 1 )
ROLLBACK TRANSACTION

Then I would like to be able to do something like this:

Code:

try {
// NHibernate code that will try to insert a row into "myTable"
catch (NHibernate.HibernateException e)
{
log.Debug(e.GetBaseException().ToString());
}

Now, when I look in my logfile I would like to see the following string occurring: "Some detailed error message" but I have not been able to figure out how to do it, and have tested to get more nested exceptions also, such as:
log.Debug(e.GetBaseException().GetBaseException().GetBaseException().GetBaseException().GetBaseException().GetBaseException().GetBaseException().ToString());

But it seems to me as if the error message from the trigger is never forwarded from NHibernate.
Or is it, and then how can I retrieve it ???

(I supect some people might be tempted to suggest not using triggers at all and to put that kind of logic in the C# code instead, but that kind of discussion is not the issue here, and the question here is how to get a more detailed error message from existing stored procedures if it is possible)

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

To do that in java, you'd use e.getSQLException().getMessage() (or e.getSQLException().toString()). The base exception of the chained HibernateException (JDBCException) is itself, and the SQLException is just a member property, accessed via getSQLException(). I would guess that it is implemented very similarly in NHibernate.

tomjo · **Joined:** Wed Aug 02, 2006 6:11 am **Posts:** 8

No, the class "NHibernate.HibernateException" does not have any property or method named "SQLException" or "getSQLException()".
(I am using the latest NHibernate stable version 1.0.2)
Though, there is a property named "InnerException", but it is null when I catch an error caused by the SQL Server function RAISERROR in a trigger.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

HibernateException doesn't, but JDBCException does. It's a subclass, you have to do the C# equivalent of instanceof and casting. I know nothing about that language (not even sure that it is C# :), maybe someone else can help with that part.

tomjo · **Joined:** Wed Aug 02, 2006 6:11 am **Posts:** 8

Well, in NHibernate the concrete exception class I get is the class "NHibernate.HibernateException", i.e. I can not try to first catch some subclass with more sql error details, nor can the exception class be downcasted.

(the concrete class can easily be determined by catching an exception and check the value of "exception.GetType().FullName" )

sergey · **Posted:** Fri Sep 29, 2006 4:41 am

First of all, whether you get the error message from NHibernate or not depends on whether the SqlClient provider throws an exception. In this case it looks like it doesn't, instead the trigger silently returns -1. It is of course possible that NHibernate swallows the exception internally for some reason but I think it's unlikely.

Try looking through the exception chain (InnerException property), maybe the exception you are looking for will be there. Try executing your code using plain ADO.NET and see if you get an exception then.

tomjo · **Joined:** Wed Aug 02, 2006 6:11 am **Posts:** 8

I now just tried with plain ADO.NET and you were right, there was no exception being thrown from the trigger into the .NET client code.

I then learned by doing some experimenting, that I need a severity higher than 10 to get the error message propagated as a .NET SqlException.
(by the way, I then tried to verify it as a documented fact, and found this page)

In other words, when I changed the trigger with this:
RAISERROR ('Some detailed error message' , 11 , 1 )
then it worked in ADO.NET and then also the error was propagated to NHibernate, and now indeed there is also a subexception "NHibernate.ADOException" that I can catch instead of catching the base class "NHibernate.HibernateException".

From "ADOException" it is possible to get more details from the wrapped SqlException by doing a type check and downcast the result of "GetBaseException()".

However, that is not really necessary for the logging purposes I was asking for, since I actually can get the RAISERROR message (with severity > 10 !) by just logging with "ToString()" on the exception I catch.

Thank you for your help.