Nhibernate Named SQL Query

Question

Can any body tell me what is wrong with below query Named SQL Query using NHibernate. As I am getting error - "Error in Named Query : GetSecondReviewIncomeStatements ":

 <sql-query name="GetSecondReviewIncomeStatements" read-only="true">
 <![CDATA[
  SELECT I.TotalNetSales,I.CostOfGoodsSold,I.GrossProfit
  FROM IncomeStatement as incomeStatement
  INNER JOIN FETCH CompSearchResultItem as resultItem
  ON incomeStatement.Comparable.ID = resultItem.Comparable.ID
  AND resultItem.CompSearch.ID = :compSearchID     
  ]]>-->
 </sql-query>-->

I have searched a lot on the web, some say instead on On put Where. I also tried NamedQuery only but everytime I get the same error.

Please share the sample code, only sample , dont put entire Code — Akshay Joy, May 16 '13 at 10:26
Ok..are you intending to use hql or sql for your query? (either way, you have syntax errors). If it's hql, you should be using a not a mapping. — DanP, May 16 '13 at 18:28
your query is has errors. For example where is `I` defined? And how can an SQL query contain expressions like `incomeStatement.Comparable.ID`? I think you are looking to use `HQL`, for which the tag is `` — mridula, May 17 '13 at 10:34

score 0 · Answer 1 · answered May 16 '13 at 10:26

Please check the Format for Named Query in .hbm.xml file.

in hbm.xml

     <sql-query name="ShowProducts">
        <return alias="Product" class="Product" />
        exec ShowProducts
      </sql-query>

<sql-query name="DeleteProducts">
    <query-param name="Id" type="int"></query-param>

    exec DeleteProducts @ID=:Id
  </sql-query>

in Code Usage

 IQuery query = (IQuery)session.GetNamedQuery("ShowProducts");


        var listProducts = query.List<Product>();

        GridView1.DataSource = listProducts;
        GridView1.DataBind();

score 0 · Answer 2 · answered May 17 '13 at 02:02

Does your SQL runs if you put it 'as-is' in your rdbms sql executor?

Besides the strange --> after the enclosing CDATA element, I've noticed that you haven't declared the I alias in your query.

I mean, these columns preceded with I...

SELECT I.TotalNetSales,I.CostOfGoodsSold,I.GrossProfit

...are coming from where?

Nhibernate Named SQL Query

2 Answers2