In a nutshell here is the problem, the text below has more detail. We have a defined database role that allows a mid level user to have permissions to 7 tables which a regular user can not access. The insert seems to work but returns the incorrect row count when inserting into any of the 7 tables in the role.
int resultCount = prepareStatement.executUpdate();
The JDBC prepared above returns -1 resultCount when when the statement appears to work. The Hibernate ORM engine checks the result and raises an exception since it was expecting a count of 1 to be returned. I realize this was fixed for the latest 2.0 release of the SQLServer JDBC driver but it still happens to us in special cases. I can get the error to happen through EJB 3.0 or just submitting SQL through a connection from the JBoss connection pool.
We are using JBoss 4.2.3 and have it setup so the connection pool uses the authenticated users login when accessing the database. For our super user account there are no problems. For any account that has been granted a specific mid level user role with, INSERT, SELECT, UPDATE and DELETE permissions to 7 additional tables, the error happens for the first 15 or so attempted insert sql statements then it seems to correct itself. When the application server is restarted the problem returns for the first 15 or so SQL statements then magically the insert starts returning a count of 1 which is correct. If we add our mid level user role to the super user account the problem happens for that login as well. Almost like adding a role has taken away a permission from the super user. The error is happening when inserting into any of the 7 additional table that are contained in the mid level users role.
Here is the full error:
"javax.ejb.EJBException: javax.persistence.OptimisticLockException: org.hibernate.StaleStateException: Batch update returned unexpected row count from update ; actual row count: -1; expected: 1"
We are moving a large application from Sybase to SQL Server. We did not have this issue with Sybase so I am assuming there is a bug in the JDBC driver.