I'm using NHibernate for the first time. I've set it up to use OleDB to connect to a SQLBase database. I've set up a few of my classes and now tried the first short test to see if it works.
This is my test:
[TestClass]
public class Hibernate
{
private ISessionFactory _sessionFactory;
private Configuration _config;
[TestInitialize]
public void InitTest()
{
_config = new Configuration();
_config.Configure();
_config.AddAssembly(typeof(Coil).Assembly);
_sessionFactory = _config.BuildSessionFactory();
}
[TestMethod]
public void TestMethod1()
{
using (var session = _sessionFactory.OpenSession())
{
Int32 coilid = 12189;
Coil coil = session.Get<Coil>(coilid);
Assert.AreEqual(coil.CoilNumber, "6FEB13");
}
}
}
And here's the relevant part of the mapping:
<class name="Coil" table="COIL">
<id name="ID" column="COILID" type="integer">
<generator class="hilo">
<param name="table">DEFTAB</param>
<param name="column">WERT1</param>
<param name="max_lo">10</param>
<param name="where">TBCODE='LFDNR' AND CODE='WGID'</param>
</generator>
</id>
<property name="CoilNumber" column="COILNR" type="string" />
</class>
When I try to run my simple test, I get an GenericADOException: could not load an entity. [SQL: SELECT ... WHERE coil0_.COILID=?] ---> System.Data.OleDb.OleDbException: Invalid program bind variable.
When I copy the SELECT ... WHERE coil0_.COILID=12189 to a SQLbase client the query succeeds without any error. The problem is that somehow NHibernate doesn't put my id variable into the query and instead simply puts a ? there.
Am I just using a wrong syntax in my test or do I have some configuration problem?
At last my hibernate config file if it helps:
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.GenericDialect</property>
<property name="connection.driver_class">NHibernate.Driver.OleDbDriver</property>
<property name="connection.connection_string">Provider=SQLBASEOLEDB.1;Password=XXX;User ID=XXX;Data Source=XXX</property>
<property name="show_sql">true</property>
Again, the SQL output is fine but I don't get why NHibernate doesn't put the ID I use for the function into the SQL query.
Edit: COILID is a normal INTEGER in the database table, and a int in the Coil class.
Edit2: Here's the stack trace
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader()
NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, String optionalEntityName, Object optionalIdentifier, IEntityPersister persister)
NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, String optionalEntityName, Object optionalIdentifier, IEntityPersister persister)
NHibernate.Loader.Entity.AbstractEntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId)
NHibernate.Loader.Entity.AbstractEntityLoader.Load(Object id, Object optionalObject, ISessionImplementor session)
NHibernate.Persister.Entity.AbstractEntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session)
NHibernate.Event.Default.DefaultLoadEventListener.LoadFromDatasource(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
NHibernate.Event.Default.DefaultLoadEventListener.DoLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
NHibernate.Event.Default.DefaultLoadEventListener.Load(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
NHibernate.Event.Default.DefaultLoadEventListener.ProxyOrLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options)
NHibernate.Event.Default.DefaultLoadEventListener.OnLoad(LoadEvent event, LoadType loadType)
NHibernate.Impl.SessionImpl.FireLoad(LoadEvent event, LoadType loadType)
NHibernate.Impl.SessionImpl.Get(String entityName, Object id)
NHibernate.Impl.SessionImpl.Get(Type entityClass, Object id)
NHibernate.Impl.SessionImpl.Get[T](Object id)
Tests.Hibernate.TestMethod1() in "D:\Hibernate.cs": Zeile 36
The create table syntax for the Coil table is as follows:
CREATE TABLE COIL (
COILID INTEGER NOT NULL,
COILNR VARCHAR(50),
);
Edit5: Querying all entries works, but as soon as I want to get a specific entry from the table I get the ? error again :(
var allCoils = session.CreateCriteria<Coil>().List<Coil>();
Coil datCoil = allCoils.First<Coil>(coil => coil.CoilNumber == "6FEB13");
Assert.AreEqual(datCoil.CoilNumber, "6FEB13");
This test is successful at least
Edit6: The problem seems to be that nhibernate prepares the sql like this:
DEBUG NHibernate.SQL - SELECT coil0_.COILID as COILID1_0_, coil0_.COILNR as COILNR1_0_ FROM COIL coil0_ WHERE coil0_.COILID=?;p0 = 12189
but SQLbase wants prepared strings like
WHERE coil0_.COILID = :0
\
12189
/
What would I have to do to change the way nhibernate transforms the bind variables into SQL?
I was able to fix this problem by writing my own driver and dialect for SQLBase (from OleDbDriver and GenericDialect respectively). I can post the code if anyone else would need it to use SQLBase with NHibernate.