Problem:
I am trying to use some native SQL to join two tables and get a list of persistent objects back. I feel as if the SQL should definitely work, as I copied and pasted the Generated SQL into the Access Query Wizard and it returned the correct result set.

However, NHibernate reports that The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. This just doesn't seem to make sense to me anymore. do you suppose this is a failure to parse this info on the Jet Driver side?

Please help - this is for a pet project i am working on and hit a pretty big wall.

Ps. If i'm to do this in HQL, any comments?


Hibernate version: NHibernate-1.2.1

Code between sessionFactory.openSession() and session.close():

public IList getItemsByDOid(int doId) {
IList items = null;
try {
session = factory.OpenSession();
transaction = session.BeginTransaction();

ISQLQuery sqlQuery = session.CreateSQLQuery("SELECT {item.*} FROM Item {item}, IncomingDOdetail {doDetail} "
+ "WHERE {doDetail}.incomingDOid = " + doId
+ " AND {doDetail}.itemId = {item}.itemId").
AddEntity("item", "Ezzeth.StockSale.DAL.Access.Item").
AddEntity("doDetail", "Ezzeth.StockSale.DAL.Access.IncomingDOdetail");

items = sqlQuery.List();
}
catch (Exception ex) {
if (transaction != null) {
transaction.Rollback();
}
if (session != null) {
session.Close();
}
throw new Exception("Rethrowing Exception at getItemsByDOid ...", ex);
}

return items;
}


Full stack trace of any exception that occurs:

at Ezzeth.StockSale.DAL.Access.DBManager.getItemsByDOid(Int32 doId) in C:\\Users\\Muhariz\\Documents\\Visual Studio 2005\\Projects\\Ezzeth\\Ezzeth_Stock_Sale\\Ezzeth.StockSale.DAL.Access\\DBManager.cs:line 274\r\n at Ezzeth.StockSale.DAL.Access.Test.Program.Main(String[] args) in C:\\Users\\Muhariz\\Documents\\Visual Studio 2005\\Projects\\Ezzeth\\Ezzeth_Stock_Sale\\Ezzeth.StockSale.DAL.Access.Test\\Program.cs:line 73\r\n at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)\r\n at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)\r\n at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()\r\n at System.Threading.ThreadHelper.ThreadStart_Context(Object state)\r\n at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n at System.Threading.ThreadHelper.ThreadStart()

Name and version of the database you are using:

Microsoft Access with Jet drivers version 4.x on Windows Vista 32 bit

The generated SQL (show_sql=true):

could not execute query\r\n[ SELECT item.itemId as itemId5_0_, item.masterCode as masterCode5_0_, item.subCode as subCode5_0_, item.ItemDescription as ItemDesc4_5_0_, item.currentStock as currentS5_5_0_, item.currentUnitPrice as currentU6_5_0_, item.currentUnitCost as currentU7_5_0_ FROM Item item, IncomingDOdetail doDetail WHERE doDetail.incomingDOid = 1 AND doDetail.itemId = item.itemId ]\r\n[SQL: SELECT item.itemId as itemId5_0_, item.masterCode as masterCode5_0_, item.subCode as subCode5_0_, item.ItemDescription as ItemDesc4_5_0_, item.currentStock as currentS5_5_0_, item.currentUnitPrice as currentU6_5_0_, item.currentUnitCost as currentU7_5_0_ FROM Item item, IncomingDOdetail doDetail WHERE doDetail.incomingDOid = 1 AND doDetail.itemId = item.itemId]


DB Schema (Access 2007):

Item:
itemId <pk> autonum
masterCode string
subCode string
ItemDescription string
currentStock number
currentUnitPrice number
currentUnitCost number

IncomingDO:
incomingDOid <pk> autonum
DOnumber string
DOdate date
DOdescription string

IncomingDOdetail:
incomingDOdetailId <pk> autonum
incomingDOid <fk to IncomingDO table> number
itemId <fk to Item table> number
masterCode string (yes need normalization I know)
subCode string (yes need normalization I know)
quantity number
baleNumber string
unitCost number


Mapping Documents:

IncomingDO mapping file:
<class name="Ezzeth.StockSale.DAL.Access.IncomingDO, Ezzeth.StockSale.DAL.Access" table="IncomingDO" lazy="false">

<id name="IncomingDOid" column="incomingDOid" type="int" unsaved-value="0">
<generator class="identity" />
</id>

<property name="DOnumber" column="DOnumber" type="String" length="255"/>
<property name="DOdescription" column="DOdescription" type="String" length="255"/>
<property name="DOdate" column="DOdate" type="DateTime"/>

<bag name="IncomingDODetails" cascade="all" inverse="true">
<key column="incomingDOid" />
<one-to-many class="Ezzeth.StockSale.DAL.Access.IncomingDOdetail, Ezzeth.StockSale.DAL.Access" />
</bag>

</class>


IncomingDODetail mapping class:
<class name="Ezzeth.StockSale.DAL.Access.IncomingDOdetail, Ezzeth.StockSale.DAL.Access" table="IncomingDOdetail" lazy="false">

<id name="IncomingDOdetailId" column="incomingDOdetailId" type="int" unsaved-value="0">
<generator class="identity" />
</id>

<property name="MasterCode" column="masterCode" type="String" length="255"/>
<property name="SubCode" column="subCode" type="String" length="255"/>
<property name="Quantity" column="quantity" type="Double"/>
<property name="BaleNumber" column="baleNumber" type="String" length="255"/>
<property name="UnitCost" column="unitCost" type="Double"/>

<many-to-one name="IncomingDO" column="incomingDOid" class="Ezzeth.StockSale.DAL.Access.IncomingDO, Ezzeth.StockSale.DAL.Access" cascade="none" />

<many-to-one name="Item" column="itemId" class="Ezzeth.StockSale.DAL.Access.Item, Ezzeth.StockSale.DAL.Access" cascade="none" />

</class>

Item mapping class:
<class name="Ezzeth.StockSale.DAL.Access.Item, Ezzeth.StockSale.DAL.Access" table="Item" lazy="false">

<id name="ItemId" column="itemId" type="int" unsaved-value="0">
<generator class="identity" />
</id>

<property name="MasterCode" column="masterCode" type="String" length="255"/>
<property name="SubCode" column="subCode" type="String" length="255"/>
<property name="ItemDescription" column="ItemDescription" type="String" length="255"/>
<property name="CurrentStock" column="currentStock" type="Double"/>
<property name="CurrentUnitPrice" column="currentUnitPrice" type="Double"/>
<property name="CurrentUnitCost" column="currentUnitCost" type="Double"/>

</class>

Help Pls? Anyone?

Hi guys,

after strugling with this for a few days I decided to just swap out the datastore from Access to SQLServer Express 2005. Took a few hours to hash out the schema, changed the connection string and BANG - it works. I also refactored the Native SQL to HQL as follows (but i tried this before i switched the database from Access to SQL Server Xpress)


IQuery query = session.CreateQuery( "SELECT item FROM " +
"Item item, IncomingDOdetail doDetail " +
"WHERE doDetail.IncomingDO.IncomingDOid = " + doId + " AND " +
"doDetail.Item.ItemId = item.ItemId");


So Bottom line is: I think the Jet Drivers are a bit off as its probably a Parse error. And if you're using JET for even test development, don't be lazy and switch to SQL Server Xpress :)