Error when using custom function on NHibernate mapping-by-code's SqlInsert

Question

I have an NHibernate mapping-by-code that works if using standard INSERT on standard SqlInsert

class ProductTranslationMapping : ClassMapping<ProductTranslation>
{
    public ProductTranslationMapping ()
    {
        .
        .
        SqlInsert ("insert into product_translation(product_id, language_code, product_name, product_description) values(?,?,?,?)");

        .
        .       
    }
}

However, I want to use Postgresql stored procedure, so both insert and update can use the same routine

class ProductTranslationMapping : ClassMapping<ProductTranslation>
{
    public ProductTranslationMapping ()
    {

        .       
        .
        SqlInsert ("select merge_product_translation(?,?,?,?)");
        SqlUpdate ("select merge_product_translation(?,?,?,?)");
        .
        .       
    }
}

However when I use Postgresql function, it has an error:

Unhandled Exception:
NHibernate.StaleStateException: Unexpected row count: -1; expected: 1
  at NHibernate.AdoNet.Expectations+BasicExpectation.VerifyOutcomeNonBatched (Int32 rowCount, IDbCommand statement) [0x00000] in <filename unknown>:0 
  at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch (IExpectation expectation) [0x00000] in <filename unknown>:0 
  at NHibernate.Persister.Entity.AbstractEntityPersister.Insert (System.Object id, System.Object[] fields, System.Boolean[] notNull, Int32 j, NHibernate.SqlCommand.SqlCommandInfo sql, System.Object obj, ISessionImplementor session) [0x00000] in <filename unknown>:0 
[ERROR] FATAL UNHANDLED EXCEPTION: NHibernate.StaleStateException: Unexpected row count: -1; expected: 1
  at NHibernate.AdoNet.Expectations+BasicExpectation.VerifyOutcomeNonBatched (Int32 rowCount, IDbCommand statement) [0x00000] in <filename unknown>:0 
  at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch (IExpectation expectation) [0x00000] in <filename unknown>:0 
  at NHibernate.Persister.Entity.AbstractEntityPersister.Insert (System.Object id, System.Object[] fields, System.Boolean[] notNull, Int32 j, NHibernate.SqlCommand.SqlCommandInfo sql, System.Object obj, ISessionImplementor session) [0x00000] in <filename unknown>:0

If it would help, here's my Postgresql stored procedure:

create or replace function merge_product_translation(p_product_id int, p_language_code text, p_product_name text, p_product_description text) returns int
as
$$
begin
    if not exists(select * from product_translation where product_id = p_product_id and language_code = p_language_code) then       
        insert into product_translation(product_id, language_code, product_name, product_description)
        values(p_product_id, p_language_code, p_product_name, p_product_description);
    else        
        update product_translation set
            product_name = p_product_name,
            product_description = p_product_description
        where product_id = p_product_id and language_code = p_language_code;    
    end if;

    return 1;

end;
$$
language 'plpgsql';

I also tried the following, but still out of luck, all of them has error:

SqlInsert ("perform merge_product_translation(?,?,?,?)");
SqlInsert ("call merge_product_translation(?,?,?,?)");  
SqlInsert ("{call merge_product_translation(?,?,?,?)}");

What should I use so I can avoid the error unexpected row count of -1? It's also hard to google it, as google eliminates the -1

score 1 · Answer 1 · answered May 19 '13 at 18:56

NHibernate needs to check the results of its internally generated SQL commands. The same is required/applied on your custom implementation (stored procedures), because it is treated the same.

But there is nice solution. Read more here 17.3. Custom SQL for create, update and delete.

The Extract:

The stored procedures are by default required to affect the same number of rows as NHibernate-generated SQL would. NHibernate uses IDbCommand.ExecuteNonQuery to retrieve the number of rows affected. This check can be disabled by using check="none" attribute in sql-insert element.

The Solution: ... adjust your mapping like this:

<sql-insert check="none" >..</sql-insert>
<sql-update check="none" >..</sql-update>

Error when using custom function on NHibernate mapping-by-code's SqlInsert

1 Answers1