Nhib.pdf Database supports classification Configuration

NHibernateisprimarilytestedonMicrosoftSQLServer2000.Itisalsoknowntoworkonthesedatabases: microsoftsqlserver20052000oraclemicrosoftaccessfirebird#sqldb2udbmysqlitemicrosoftsqlserver20052000sqlse

NHibernate is primarily tested on Microsoft SQL Server 2000. It is also known to work on these databases: Microsoft SQL Server 2005/2000 Oracle Microsoft Access Firebird PostgreSQL DB2 UDB MySQL SQLite Microsoft SQL Server 2005/2000 SQL Se

NHibernate is primarily tested on Microsoft SQL Server 2000. It is also known to work on these databases:

• Microsoft SQL Server 2005/2000
• Oracle
• Microsoft Access
• Firebird
• PostgreSQL
• DB2 UDB
• MySQL
• SQLite

Microsoft SQL Server 2005/2000

SQL Server 2005 and 2000 are the primary databases used by the developers of nhib.pdf.

Configuration example:

  

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Dialect.MsSql2005Dialect
    
    
        
    
     NHibernate.Driver.SqlClientDriver
    
    
        
    
     Server=(local);Initial Catalog=dbname;User Id=user;Password=********
    
    
    
   
   

  

For SQL Server 2000, change the dialectNhib.pdf. Dialect. MsSql2000Dialect.

Issues

SQL Server sometimes ignores columns specified inORDERClause of a query if they are not supported inSELECTClause. 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 shocould 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:Nhib.pdf. JetDriver. dll(In NHibernateContrib package ).

Here is what yourHibernate. cfg. xmlFile shoshould contain when using a Microsoft Access database:

  

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.JetDriver.JetDialect, NHibernate.JetDriver
    
    
        
    
     NHibernate.JetDriver.JetDriver, NHibernate.JetDriver
    
    
        
    
     Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourDatabaseFilePathHere.mdb
    
    
    
   
   

  

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 nhib.pdf 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 stronugly 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 p to your application configuration file (App. configOrWeb. config):

  
   
    
   
        
               fullName="FirebirdSql.Data.FirebirdClient, Version=2.0.1.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />
   
    
   

  

The value ofFullNameAttribute will depend on the version of the assembly you have installed in the GAC.

Here is what yourHibernate. cfg. xmlFile shoshould contain when using Firebird:

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Driver.FirebirdClientDriver
    
    
        
    
     ReadCommitted
    
    
        
    
     
            Server=localhost;
     
            Database=C:\nhibernate.fdb;
     
            User=SYSDBA;Password=masterkey
     
        
    
    
        
    
     false
    
    
        
    
     NHibernate.Dialect.FirebirdDialect
    
    
        
    
     true
    
    
        
    
     444
    
    
        
    
     true 1, false 0, yes 1, no 0
    
    
    
   
   

  

All Firebird 2.0.1 embedded functions are registered in the dialect, as well as user-defined functions fromIb_udf2. SQL. These functions can be used in HQL queries.

PostgreSQL

PostgreSQL version 7.4 with latest Npgsql works almost perfectly. Later versions shocould work too.

Configuration example:

  

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Driver.NpgsqlDriver
    
    
        
    
     
            Server=localhost;initial catalog=nhibernate;User ID=nhibernate;Password=********;
     
        
    
    
        
    
     NHibernate.Dialect.PostgreSQLDialect
    
    
    
   
   

  

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:

  

  
   

   
    
   
    

    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Driver.OdbcDriver
    
    
        
    
     driver={IBM DB2 ODBC DRIVER};Database=db;hostname=host;port=port;protocol=TCPIP; uid=uid; pwd=pwd
    
    
        
    
     true
    
    
        
    
     NHibernate.Dialect.DB2Dialect
    
    
        
    
     true
    
    

    
        
    
    

    
    
    
    
   
   

   

  

MySQL

All MySQL versions shocould 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:

  

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Driver.MySqlDataDriver
    
    
        
    
     
            Database=test;Data Source=someip;User Id=blah;Password=blah
     
        
    
    
        
    
     NHibernate.Dialect.MySQLDialect
    
    
    
   
   

  

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:

  

  
   
    
   
    
        
    
     NHibernate.Connection.DriverConnectionProvider
    
    
        
    
     NHibernate.Driver.SQLiteDriver
    
    
        
    
     
            Data Source=nhibernate.db;Version=3
     
        
    
    
        
    
     NHibernate.Dialect.SQLiteDialect
    
    
        
    
     true=1;false=0
    
    
    
   
   

  

Issues

SQLite currently fails 17 tests out of approximately 500 in nhib1_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.