By creating 2 sets of properties in hbm mapping file, I finally made SP works in NH, kind of, delete SP still doesn’t work. I suspect it’s NH’s bug, because from the log I can tell the delete SP only be passed in one parameter which is the primary key column, while insert SP and update SP both can get all the parameters.
I also don’t like the idea of retrieve-first-then-delete. It’s real ORM, but sometimes we need a little bit exception to directly operate database. I noticed some NH members already working on this, in their internal JIRA. Hope I don’t need to wait too long to see this ISession.Delete<T>(Object id);
Some tick I’ve been used to make SP works in NH so far.
- Turn on sql debug.
<add key=”hibernate.show_sql” value=”true”/>
- Create a set of field column to save original values used by update SP.
<property name=”ColumnNameOriginal” column=”ColumnName” insert=”false” access=”field“/>
Insert SP doesn’t need this kind of property, so turn insert to off. By setting it’s access type to ‘field’, we don’t need to write getter/setter in domain object any more.
- NHibernate.StaleStateException: Unexpected row count: -1; expected: 1
SP needs to Set NoCount OFF.
SET NOCOUNT – Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.
But The stored procedures are in most cases (read: better do it than not) required to return the number of rows inserted/updated/deleted, as Hibernate has some runtime checks for the success of the statement. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:
- My custom sql:
<sql-insert> EXECUTE Table_INS ?,?,?,?,?,?,?,?,?,?,?,? </sql-insert>
<sql-update> EXECUTE Table_UPD ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? </sql-update>
<sql-delete> EXECUTE Table_DEL ?,?,?,?,?,?,?,?,?,?,?,?,? </sql-delete>
I am mad at this unsorted parameter list. But what can I improve it? Using the pre-defined property name? How?
- id column has to be set to increment, otherwise the insert SP won’t be triggered. I am using SQLServer 2005.
- Output type parameter seems doesn’t work. Fortunately, primary key id can be catch successfully by real id column. For others, I think refresh/retrieve is the only option.