vrijdag 27 juli 2007

NHibernate & MS Access: problems with autonumber fields

I was playing around with NHibernate and MS Access a bit the other day, and I ran into an annoying problem.

When I tried to save an object which maps to a table wich has an 'autonumber' field, I received this error:

a different object with the same identifier value was already associated with the session: 0

It looked like NHibernate was unable to retrieve the ID that has been given to the new record in the database.

So, I decided to set up log4net to see if I could find any clue in the logs about the reason for this problem.

As it turned out, it seems that NHibernate was closing the database-connection between the INSERT and the SELECT @@identity statements.

DEBUG NHibernate.SQL - INSERT INTO table ...
DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining
IDbConnection from Driver
DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands: 0
DEBUG NHibernate.Impl.ConnectionManager - aggressively releasing
database connection
DEBUG NHibernate.Connection.ConnectionProvider - Closing connection
DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands: 1
DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for
the SqlString: select @@identity
DEBUG NHibernate.SQL - select @@identity

After some research in the Hibernate documentation and on the Internet, I discovered that this is caused by the 'connection release mode'.

The default connection release mode seems to release the connection after each SQL statement, so obviously, Access is not able to determine the correct ID of the last inserted record, since the select @@identity command must be executed on the same connection as its related INSERT statement.

Now that we know what caused this problem, it is easy to fix it: just make sure that you do not use the default connection release mode when using NHibernate with MS Access.

To do so, you should specify the connection release mode in your NHibernate configuration file like this:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<property name="hibernate.connection.release_mode">on_close</property>

More information regarding the different options / settings for the connection-release mode can be found here.

woensdag 18 juli 2007

Counting in SQL Server: SELECT COUNT(*) != SELECT COUNT(columnname)

Today, I've learned something new; or rather, I was being pointed out something that I didn't know about:
It seems that, doing a SELECT COUNT(columnname) does not always give you the same results as doing a SELECT COUNT(*). As it was pointed out to me, doing a SELECT COUNT(columnname) returns the number of rows in the resultset in where the value of the field in 'columnname' is NOT NULL.

This means that, given the following set of data:


performing this statement:

gives us 3 as a result, while doing this:
returns 2 as result.

I really was ignorant to this behaviour, as I do (or did), most of the time a SELECT COUNT(1) FROM table instead, since I thought that this was the most performant option, but this also seems to be not true in some circumstances;
doing a SELECT COUNT(*) enables SQL Server to use indexes in calculating the number of results in a resultset.

For a more thourough article about this, I'd like to refer to this article on SQL Server Central.

Thanks to Peter De Boer for pointing this out