I am using NHibernate for the first time. I installed it to use OleDB to connect to the SQLBase database. I created some 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 is the corresponding part of the display:
<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 a GenericADOException: the object failed to load. [SQL: SELECT ... WHERE coil0_.COILID =?] ---> System.Data.OleDb.OleDbException: Invalid program binding variable.
When I copy SELECT ... WHERE coil0_.COILID = 12189 for the SQLbase client, the query is executed without any error. The problem is that somehow NHibernate does not put my id variable in the request and instead just sets it? there is.
Am I just using the wrong syntax in my test or am I having a configuration problem?
Finally, 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 understand why NHibernate does not put the identifier that I use for this function in the SQL query.
Edit: COILID is a normal INTEGER in the database table and int in the Coil class.
Edit2: Here is 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 syntax for create table for the Coil table is as follows:
CREATE TABLE COIL ( COILID INTEGER NOT NULL, COILNR VARCHAR(50), );
Edit5: querying all records works, but as soon as I want to get a specific record from a table, do I get? 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 passed at least
Edit6: The problem is that nhibernate prepares sql as follows:
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 rows like
WHERE coil0_.COILID = :0 \ 12189 /
What do I need to do to change the way nhibernate converts bind variables to SQL?