Databases supported by NHibernate NHibernate is primarily tested on Microsoft SQL Server 2000. It is also known to work on these databases:
Microsoft SQL Server 2005/2000 SQL Server 2005 and 2000 are the primary databases used by the developers of NHibernate.
Configuration example:
<?xml version="1.0" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string">Server=(local);Initial Catalog=dbname;User Id=user;Password=********</property>
</session-factory>
</hibernate-configuration>
For SQL Server 2000, change the dialect to NHibernate.Dialect.MsSql2000Dialect.
Issues
SQL Server sometimes ignores columns specified in ORDER BY clause of a query if they are not included in the SELECT clause. This behavior is actually valid according to the SQL standard, but may be surprising. For example, a query like this is not guaranteed to be ordered correctly:
from Person p order by p.Company.Name
Oracle Oracle 9i and 10g are supported, both using Microsoft driver (System.Data.OracleClient) and using Oracle driver (Oracle.Data.OracleClient).
Issues
Microsoft's driver does not handle long character strings correctly. An error happens in some circumstances when using a string of length 2000-4000 as a parameter value.
Oracle cannot handle empty strings (""), you should use null instead. An IUserType implementation to perform the conversion is contained in Nullables.NHibernate library (part of NHibernateContrib package).
Microsoft Access Microsoft Access has its own dialect and driver (contributed by Lukas Krejci).
They are currently in a separated library: NHibernate.JetDriver.dll (in NHibernateContrib package).
Here is what your hibernate.cfg.xml file should contain when using a Microsoft Access database:
<?xml version="1.0" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.JetDriver.JetDialect, NHibernate.JetDriver</property>
<property name="connection.driver_class">NHibernate.JetDriver.JetDriver, NHibernate.JetDriver</property>
<property name="connection.connection_string">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourDatabaseFilePathHere.mdb</property>
</session-factory>
</hibernate-configuration>
Issues
There are still few problems with join syntax in queries that use more than one join. This driver passes 93% of NHibernate tests (there are 23 failing tests).
The most complete topic about these issues is here: Using NHibernate with Microsoft Access 2003 / Jet 4.0. JIRA issues NH-124 and NH-437 have some information on problems with implementing Microsoft Access dialect. You can also try searching NHibernate forum for "access jet" (require all words).
Firebird Firebird is supported since version 1.5.3, though version 2.0.1 is strongly recommended. To work with Firebird, install the latest Firebird .NET Data Provider. If the data provider is installed in the GAC (the standard behavior of its installer), add this section to your application configuration file (App.config or Web.config):
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<qualifyAssembly partialName="FirebirdSql.Data.FirebirdClient"
fullName="FirebirdSql.Data.FirebirdClient, Version=2.0.1.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />
</assemblyBinding>
</runtime>
The value of the fullName attribute will depend on the version of the assembly you have installed in the GAC.
Here is what your hibernate.cfg.xml file should contain when using Firebird:
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.FirebirdClientDriver</property>
<property name="connection.isolation">ReadCommitted</property>
<property name="connection.connection_string">
Server=localhost;
Database=C:\nhibernate.fdb;
User=SYSDBA;Password=masterkey
</property>
<property name="show_sql">false</property>
<property name="dialect">NHibernate.Dialect.FirebirdDialect</property>
<property name="use_outer_join">true</property>
<property name="command_timeout">444</property>
<property name="query.substitutions">true 1, false 0, yes 1, no 0</property>
</session-factory>
</hibernate-configuration>
All Firebird 2.0.1 embedded functions are registered in the dialect, as well as user-defined functions from ib_udf2.sql. These functions can be used in HQL queries.
PostgreSQL PostgreSQL version 7.4 with latest Npgsql works almost perfectly. Later versions should work too.
Configuration example:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.NpgsqlDriver</property>
<property name="connection.connection_string">
Server=localhost;initial catalog=nhibernate;User ID=nhibernate;Password=********;
</property>
<property name="dialect">NHibernate.Dialect.PostgreSQLDialect</property>
</session-factory>
</hibernate-configuration>
DB2 UDB DB2 UDB is supported and reportedly even passes all the tests (see http://nhibernate.sourceforge.net/forum/viewtopic.php?t=73). Example configuration using the ODBC drivers that come with the 'stinger' release of db2:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0" >
<session-factory name="session">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.OdbcDriver</property>
<property name="connection.connection_string">driver={IBM DB2 ODBC DRIVER};Database=db;hostname=host;port=port;protocol=TCPIP; uid=uid; pwd=pwd</property>
<property name="show_sql">true</property>
<property name="dialect">NHibernate.Dialect.DB2Dialect</property>
<property name="use_outer_join">true</property>
<mapping resource="..." />
</session-factory>
</hibernate-configuration>
MySQL All MySQL versions should work, though there are issues with zero dates (see below for details). To connect to your database use Connector/NET (formerly known as ByteFX.Data.MySqlClient).
Configuration example:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
<property name="connection.connection_string">
Database=test;Data Source=someip;User Id=blah;Password=blah
</property>
<property name="dialect">NHibernate.Dialect.MySQLDialect</property>
</session-factory>
</hibernate-configuration>
Issues
MySQL has a unique "feature" of allowing invalid dates in a DATE field, and especially using 0000-00-00 as a default value for DATE NOT NULL columns. When MySQL Connector encounters such a date, it either throws an exception or returns a non-standard MySqlDateTime object (depending on a connection string parameter) which throws an exception when converting itself to a DateTime.
Possible workarounds:
- avoid zero dates
- modify Connector/NET source code to round zero dates to DateTime.Min and back (see JIRA issue NH-32 for a patch to an older version of the Connector)
- create a user type for MySqlDateTime (no known implementations currently)
SQLite SQLite version 3 works rather well using ADO.NET provider available from http://sourceforge.net/projects/adodotnetsqlite. NHibernate Query Analyzer uses SQLite (see [Related Projects]).
Configuration example:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.Test">
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>
<property name="connection.connection_string">
Data Source=nhibernate.db;Version=3
</property>
<property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
<property name="query.substitutions">true=1;false=0</property>
</session-factory>
</hibernate-configuration>
Issues
SQLite currently fails 17 tests out of approximately 500 in NHibernate test suite. Some of the failures are caused by SQLite being unable to store strings containing NUL characters, other tests fail because of a bug in SQLite ADO.NET provider when using a table with spaces in its name.
| Database | 20 Dec 2006, 09:33 | dementor |
Can i use XML as a database instead of MSSQL or Oracle etc?
if yes, please guide me to do it.
Thanks in advance. |
| |
| Configuration Error hibernate.connection.driver_class | 02 Jan 2007, 11:27 | vata2999 |
After following the Quick Start Guide I am getting an ERROR:
The hibernate.connection.driver_class must be specified in the
NHibernate configuration section
I have clearly declare the hibernate.connection.driver_class Key in
app.config file:
<add key="hibernate.connection.driver_class"
value="NHibernate.Driver.SqlClientDriver" />
I am stucked up with this. Kindly help to solve this prob.
Thanks, |
| |
| Re: Configuration Error hibernate.connection.driver_class | 13 Mar 2007, 08:58 | Wervis |
POST QUESTIONS ON THE FORUM! COMMENTS HERE SHOULD ADD VALUE TO THE PAGE!
POST QUESTIONS ON THE FORUM! COMMENTS HERE SHOULD ADD VALUE TO THE PAGE!
On 02 Jan 2007 11:27, vata2999 wrote:
>After following the Quick Start Guide I am getting an ERROR:
>The hibernate.connection.driver_class must be specified in the
>NHibernate configuration section
>I have clearly declare the hibernate.connection.driver_class Key in
>app.config file:
> <add key="hibernate.connection.driver_class"
>value="NHibernate.Driver.SqlClientDriver" />
>I am stucked up with this. Kindly help to solve this prob.
>Thanks,
Add following to your app.config and complete connectionstring:
<nhibernate>
<add key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="hibernate.dialect"
value="NHibernate.Dialect.MsSql2000Dialect"/>
<add key="hibernate.connection.driver_class"
value="NHibernate.Driver.SqlClientDriver"/>
<add key="hibernate.connection.connection_string" value="Data
Source=yourserver;Initial Catalog=yourdatabase;Persist Security
Info=True;User ID=sa;Password=pass"/>
<add key="hibernate.show_sql" value="true"/>
</nhibernate> |
| |
| Update Sqlite ADO.net driver | 22 Mar 2007, 11:40 | redinger |
There's a note about it on the ADO.net provider page that is referenced,
but it is no longer being actively developed.
A new provider that is ADO.net 2.0 compliant can be found here:
http://sqlite.phxsoftware.com/ |
| |
| postgresql connection string example does not work with latest | 02 May 2008, 16:59 | evarlast |
The connection string example for postgresql does not work.
Use databsae= instead of initial catalog=. |
| |
| FoxPro Connection works | 15 Jun 2008, 09:56 | antoschka |
I've managed to connect to FoxPro databases. Even in a complex data
model there I could not encounter any problems. As Dialect I took a
GeneralDialect or MsSql2000Dialect - the Driver I defined as OleDbDriver.
The biggest hurdle was to find out the appropriate generator class for
the various Id-columns of the entities.
Never set autoincrement on the database level. This will not work with
foxpro. Define it as Id or leave the id column as normal column. The
important think is to define the Id Column in entity Mapping file with
<generator class="increment" />
example:
<id name="IdentityEntity" column="IdentityColumn" type="int">
<generator class="increment" />
</id>
If you want to assign the ids yourself use <generator class="assigned" />. |
| |
| Bug encountered and solved in the NHibernate.JetDriver assembly | 15 Jun 2008, 09:59 | antoschka |
Hi,
there is a bug in the NHibernate.JetDriver assembly. As soon as you deal
with columns which contain the literals "from" the Driver throws
exceptions at you. I have modified the Driver and now it works. However
I do not know how to contribute the changes.
Bye antoschka |
| |
| Re: FoxPro Connection works | 15 Jun 2008, 10:53 | antoschka |
Just a further note on the FoxPro issue. Through all the hustle I also
tried Access as a Database. However it might be interesting for you how
databases compare speed-wise.
Currently I connect to three DB (all contain the same scheme and
identical data). To startup my application I received the following
speed figures.
- MS-SQL Server: normal start 1.4 seconds ( in in Debug-Mode 21 Seconds)
- Access: normal start 7.5 seconds( in in Debug-Mode 14.5 Seconds)
- FoxPro: normal start 2,6 seconds( in in Debug-Mode 21 Seconds)
Debug Mode means - SQL Command logging:
<add
key="hibernate.show_sql"
value="true"
/>
In case of performance problems with Access-DBs consider using FoxPro.
Similar to Access you also need only access to a (network-)drive instead
of installing a whole database server and dealing with all the overhead
in terms of configuration, maintenance and administration.
Greeting antoschka |
| |
| Configuration example for Oracle | 14 Sep 2008, 01:22 | mijaved |
| Where the hell is the Configuration example for Oracle!!!??? |
| |
| Re: Configuration example for Oracle | 25 Sep 2008, 12:07 | wizzsm |
POST QUESTIONS ON THE FORUM! COMMENTS HERE SHOULD ADD VALUE TO THE
PAGE!On 14 Sep 2008 01:22, mijaved wrote:
>Where the hell is the Configuration example for Oracle!!!???
in your .cs the oracle driver info is:
if ("ORACLE" == database)
{
cfg.SetProperty(NHibernate.Cfg.Environment.Dialect,
"NHibernate.Dialect.Oracle9Dialect");
cfg.SetProperty(NHibernate.Cfg.Environment.ConnectionDriver,
"NHibernate.Driver.OracleDataClientDriver");
cfg.SetProperty(NHibernate.Cfg.Environment.ConnectionString,
connectionString);
}
in assemblyinfo.cs add:
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
in the exe.config use:
<!-- Select the kind of database to use -->
<!-- Possible values: MSSQL, MySQL, or ORACLE -->
<appSettings>
<add key="Database" value="ORACLE" />
<add key="MSSQL.ConnectionString" value="Data Source=(local);Initial
Catalog=nhibernate;Integrated Security=SSPI" />
<add key="MySQL.ConnectionString"
value="Server=localhost;Database=nhibernate;User ID=root;Password=" />
<add key="ORACLE.ConnectionString" value="Data
Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=********.domain.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=*****)));User
ID=****;Password=****" />
</appSettings>
and copy the following file from oracle into your product:
C:\oracle\product\10.2.0\db_1\BIN\Oracle.DataAccess.dll
Steve. |