I am using Spring and Hibernate to access data in a MS Access database. This works fine except when it comes to multiple joins. It looks like the JetDriver/JetDialect is producing the correct SQL but without parenthesis, so Access reports an error. I searched JIRA and also these forums and saw that this (parenthesis) problem has been already resolved but it still occurs in my scenario.
Hibernate version:
NHibernate 1.2.0.GA including the JetDriver
Spring 1.1.0 RC2
Mapping documents:
Code:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Aurelio.Model.Profile,AurelioModel" table="Profile" lazy="false">
<id name="ProfileId" column="ProfileID" type="int">
<generator class="increment" />
</id>
<property name="ProfileNo" column="ProfileNo" type="string" not-null="true" />
<property name="Name" column="Name" type="string" not-null="true" />
<property name="Code" column="Code" type="string" />
<bag name="OrderFormElements" inverse="true" lazy="true" cascade="save-update">
<key column="ProfileID" />
<one-to-many class="Aurelio.Model.OrderFormElement,AurelioModel" />
</bag>
<bag name="ProfileElements" inverse="true" lazy="true" cascade="save-update">
<key column="ProfileID" />
<one-to-many class="Aurelio.Model.ProfileElement,AurelioModel" />
</bag>
<bag name="ResultProfiles" inverse="true" lazy="true" cascade="save-update">
<key column="ProfileID" />
<one-to-many class="Aurelio.Model.ResultProfile,AurelioModel" />
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Aurelio.Model.Assay,AurelioModel" table="Assay" lazy="false">
<id name="AssayId" column="AssayID" type="int">
<generator class="increment" />
</id>
<property name="AssayNo" column="AssayNo" type="string" not-null="true" />
<property name="DefaultTestNo" column="DefaultTestNo" type="string" />
<property name="Name" column="Name" type="string" not-null="true" />
<many-to-one name="SampleType" column="SampleTypeID" cascade="save-update" not-null="true" />
<many-to-one name="AssayCategory" column="AssayCategoryID" cascade="save-update" />
<bag name="OrderFormElements" inverse="true" lazy="true" cascade="save-update">
<key column="AssayID" />
<one-to-many class="Aurelio.Model.OrderFormElement,AurelioModel" />
</bag>
<bag name="ProfileElements" inverse="true" lazy="true" cascade="save-update">
<key column="AssayID" />
<one-to-many class="Aurelio.Model.ProfileElement,AurelioModel" />
</bag>
<bag name="Results" inverse="true" lazy="true" cascade="save-update">
<key column="AssayID" />
<one-to-many class="Aurelio.Model.Result,AurelioModel" />
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Aurelio.Model.ProfileElement,AurelioModel" table="ProfileElement" lazy="false">
<id name="ProfileElementId" column="ProfileElementID" type="int">
<generator class="increment" />
</id>
<many-to-one name="Profile" column="ProfileID" cascade="save-update" not-null="true" />
<many-to-one name="Assay" column="AssayID" cascade="save-update" />
<property name="Position" column="Position" type="int" />
<many-to-one name="SubProfile" column="SubProfileID" cascade="save-update" />
</class>
</hibernate-mapping>
Code:
System.Data.OleDb.OleDbException: Fehler E_FAIL(0x80004005) in IErrorInfo.GetDescription.
bei System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
bei System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
bei System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
bei System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
bei System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
bei System.Data.OleDb.OleDbCommand.ExecuteReader()
bei NHibernate.JetDriver.JetDbCommand.System.Data.IDbCommand.ExecuteReader()
bei NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
Spring-Hibernate configuration:Code:
<d:dbProvider id="DbProvider"
provider="OleDb-2.0"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=${database.path};"/>
<object id="TransactionManager" type="Spring.Data.NHibernate.HibernateTransactionManager, Spring.Data.NHibernate12">
<property name="SessionFactory" ref="sessionFactory" />
<property name="DbProvider" ref="DbProvider" />
</object>
<object id="sessionFactory" type="Spring.Data.NHibernate.LocalSessionFactoryObject, Spring.Data.NHibernate12">
<property name="DbProvider" ref="DbProvider"/>
<property name="MappingAssemblies">
<list>
<value>AurelioModel</value>
</list>
</property>
<property name="HibernateProperties">
<dictionary>
<entry key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"/>
<entry key="hibernate.dialect"
value="NHibernate.JetDriver.JetDialect, NHibernate.JetDriver"/>
<entry key="hibernate.connection.driver_class"
value="NHibernate.JetDriver.JetDriver, NHibernate.JetDriver"/>
</dictionary>
</property>
</object>
MS Access (JetDriver)
The generated SQL (show_sql=true):I use the following hql
Code:
from Profile profile join fetch profile.ProfileElements pe join fetch pe.Assay
which results in the following SQL produced by JetDriver
Code:
select profile0_.ProfileID as ProfileID9_0_, profileele1_.ProfileElementID as ProfileE1_3_1_, assay2_.AssayID as AssayID17_2_, profile0_.ProfileNo as ProfileNo9_0_, profile0_.Name as Name9_0_, profile0_.Code as Code9_0_, profileele1_.ProfileID as ProfileID3_1_, profileele1_.AssayID as AssayID3_1_, profileele1_.Position as Position3_1_, profileele1_.SubProfileID as SubProfi5_3_1_, assay2_.AssayNo as AssayNo17_2_, assay2_.DefaultTestNo as DefaultT3_17_2_, assay2_.Name as Name17_2_, assay2_.SampleTypeID as SampleTy5_17_2_, assay2_.AssayCategoryID as AssayCat6_17_2_, profileele1_.ProfileID as ProfileID__0__, profileele1_.ProfileElementID as ProfileE1_0__ from Profile profile0_ inner join ProfileElement profileele1_ on profile0_.ProfileID=profileele1_.ProfileID inner join Assay assay2_ on profileele1_.AssayID=assay2_.AssayID
I am a little confused why I get the above SQL while all people in the forum say that the JetDriver works just fine and parenthesis around the joins are produced properly. I have the bad feeling that I am doing something wrong. Maybe someone has a hint for me where to look?
Login
FAQ
Search