I've read the documentation, and even have some working code with Hibernate, MySQL, and calling stored procedures for sql-insert, sql-delete, and somewhat for sql-update. But it wasn't easy because much of the documentation seems inaccurate, and incomplete - at least when using MySQL.
I have written several stored procedures that I have tested and are working for inserting, updating and deleting tables using mysql, and I can even get Hibernate to call the stored procedure, but
I cannot seem to get Hibernate to recognize the returned result which indicates the success(or failure) of the procedure.
I've tried several approaches, and none seem to work:
1) Making the first parameter of the stored procedure an OUT parameter and setting it to ROW_COUNT(),
The error in this case is that
JDBC reports an exception that the number of parameters passed to the procedure is N-1. After investigating,
Hibernate is not providing a parameter for the OUT parameter, it is only supplying the parameters corresponding to columns defined in the mapping.
2) Changed the procedure to only have the provided IN parameters (removed the first OUT parameter). Now it is returning the ROW_COUNT() as the last statement of the procedure - and it works correctly when tested on Query Browser.
Hibernate reports an exception after (successful) completion of the stored procedure:
Exception: StaleStateException
Message: Batch update returned unexpected row count from update [0]; actual row count: -1; expected: 1
In Class: org.hibernate.jdbc.Expectations$BasicExpectation
In Method: checkBatched() : Expectations.java : 85
The problem here is that Hibernate is somehow not checking the result-set returned, because it is definitely returning a 1.
So does anyone know how to get this to work?
I am using mySQL 5.1.3, and Hibernate 3.3.2