Hibernate 3 not able to recognize the "ON" word in sql

[Kartik Tal]

Hello All,

Am using hibernate with MSSQL where am having a query which is joining 4 tables with "ON" condition

    
SELECT
 V.Description AS Vendor, P.ProductNumber, R.VendorItemNumber, P.Description, ISNULL(I.InventoryAmount, 0) AS InventoryAmount,
ISNULL(I.ThresholdAmount, 0) AS ThresholdAmount
FROM  tbProducts P  JOIN tbRacks R  ON P.ProductNumber = R.ProductNumber  JOIN tbRacksVendors V   ON R.VendorID = V.VendorID
 LEFT JOIN tbInventory I   ON P.ProductNumber = I.ProductNumber  Join tbBrands T   ON P.brandid=T.brandid  order by
  V.Description asc

Its giving an error at "ON" condition even I checked "WITH" condition but failed even with that.
But the same code is working in MSSQL properly, so the problem is with Hibernate

[Paul Sturrock]

How are you running this bit of SQL? Are you running it as a SQL query?

[Kartik Tal]

in MSSQL console am running as query but where as in hibernate its giving error telling "Invalid token ON".

[Paul Sturrock]

Yes, what I mean is which Hibernate method are you using to run this bit of SQL.

[Kartik Tal]

This is the way i had used

   
String select ="select v.description ,p.productNumber, r.vendorItemNumber, p.description," +
                        " ISNULL(i.InventoryAmount, 0) from Products p JOIN Racks r " +
                        " on p.productNumber = r.productNumber JOIN RacksVendors v on r.vendorID = v.vendorID " +
                        " LEFT JOIN Inventory i on p.productNumber =  i.productNumber JOIN Brands b" +
                        " on p.brandID = b.brandID

List query_select = null;
query_select = session_hibernate.createQuery(select).list();

[Paul Sturrock]

Ok. Well Hibernate's query language is HQL not SQL (have a read of the documentation). If you want to run proprietory SQL you will need to run it ass a sql query (check the JavaDocs for the Session class).

One thing to think about though: one of the reasons you use a tool like Hibernate is to avoid tighly coupling your data access code to a specific database, which is what you do when you introduce SQL Server specific syntax and keywords to your query. There is nothing in your query that you can't do in HQL, or using Hibernate's Criteria API.