MS SQL Server 2000 Row Not Found Exception

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

When I update a transient object, the changes get persisted to the DB even though the following HibernateException gets thrown. I'm using MS Sql Server and the Microsoft JDBC drivers. Anyone know why?

12:58:13,930 ERROR [SessionImpl] Could not synchronize database state with session
net.sf.hibernate.HibernateException: SQL update or deletion failed (row not found)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:25)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.jav
a:672)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.jav
a:625)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:52)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2308)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2262)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)

Code:

public void save(Object o) {
    Session session = null;
    try {
        // open session
        session = sessionFactory.openSession();

        // save Object
        session.saveOrUpdate(o);

        // flush session
        session.flush();

        // close session
        closeSession(session);
    } catch (HibernateException e) {
        log.fatal("Error occurred while saving object", e);
        closeSession(session);
    }
}

michael · **Posted:** Mon Feb 09, 2004 3:23 pm

Most likely some trigger messing with the returned updated row count.

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

No triggers are set on the table. In fact, it's a very simple testing table with only two columns (int (pk), varchar).

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

I looked through the hibernate code and this is where the exception is being generated. Any idea why this happens with SQL Server? Should I change the code and rebuild hibernate or should i catch the exception and look for this message and ignore it?

Code:

public void addToBatch(int expectedRowCount) throws SQLException, HibernateException {
    int rowCount = getStatement().executeUpdate();

    //negative expected row count means we don't know how many rows to expect
    if ( expectedRowCount>0 && expectedRowCount!=rowCount )
        throw new HibernateException("SQL update or deletion failed (row not found)");
}

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

On further examination, it looks like the expected row count is getting set to 1.

session.getBatcher().addToBatch(1); --> in EntityPersister.update(), line 672

So what appears to be happening is that the update is executing successfully, meaning the data makes it to the DB, however the returned row count is not equal to 1....which leads me to believe that it has something to do with SQL Server and the value that is getting returned from:

int rowCount = getStatement().executeUpdate(); --> NonBatchingBatcher.addToBatch(), line 22

I'm a SQL Server noob so any insights would be greatly appreciated :)

michael · **Posted:** Mon Feb 09, 2004 4:06 pm

Driver issue probably?

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

I checked the DB properties and NO COUNT is set to true. I suspect this may have something to do with the problem...investigating...

michael · **Posted:** Mon Feb 09, 2004 4:23 pm

From all I know about mssql that's most likely the problem.

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

The NoCount connection property actually suppresses the row_count messages that appear at the end of each statement.

Bummer...

Since we need to keep this propery set as is (for legacy code and stored procs), how would you recommend working around this. Changing the Hibernate code and rebuilding the hibernate JAR or catching the exception, looking for the 'row not found' exception and ignoring it?

thx.

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

solution

I commented out the following code in NonBatchingBatcher since it is essentially meaningless when the SQL Server connection parameter NoCount is set to on:

public void addToBatch(int expectedRowCount) throws SQLException, HibernateException {
int rowCount = getStatement().executeUpdate();
//negative expected row count means we don't know how many rows to expect

/*
if ( expectedRowCount>0 && expectedRowCount!=rowCount )
throw new HibernateException("SQL update or deletion failed (row not found)");
*/

}

Are there any plans for the Hibernate team to accommodate this scenario for SQL Server 2000?

emmanuel · **Posted:** Mon Feb 09, 2004 5:59 pm

The driver should return -1 or something like that
Which value do you have on batch_size ?

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

Whatever the default value is set to -- listed below is my hibernate config...

<hibernate-configuration>


<session-factory name="java:/jdbc/hibernate/session-factory/TEST">


<property name="connection.datasource">java:/jdbc/conn/TEST</property>
<property name="dialect">net.sf.hibernate.dialect.SQLServerDialect</property>
<property name="show_sql">true</property>
<property name="use_outer_join">true</property>


<mapping resource="TEST.hbm.xml"/>

</session-factory>

</hibernate-configuration>

gavin · **Posted:** Mon Feb 09, 2004 11:47 pm

I suggest that you continue working with your patched version of Hibernate.

bodhi · **Joined:** Mon Feb 09, 2004 3:15 pm **Posts:** 34

Thanks for the recommendation :-) Good news is that we're eventually going to get rid of the legacy stored procs that rely on NoCount being turned on.