-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
rcasha
 Post subject: invalid SQL generated from HQL (in where clause for join)
PostPosted: Fri Jun 01, 2007 3:18 am 
Newbie

Joined: Wed Oct 25, 2006 8:45 am
Posts: 9
Given an HQL query, NH generated invalid SQL for SQL Server 2000 in the where clause for an inner-join, adding multiple "and" keywords at the end. I believe this was working before so it's a regression.

Hibernate version:
1.2.0GA and SVN

Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" xmlns:hbm="urn:nhibernate-mapping-2.2"
                   xmlns:dm="http://www.megabyte.net/ns/dotnet/2.0/dm">

   <!--
     ArticleIndex:
     Non-persistent class to retrieve an index of articles
    -->

  <import class="Megabyte.Data.CMS.ArticleIndex, Megabyte" rename="ArticleIndex"/>

   <!--
     Application:
     Applications installed in this database.
    -->

  <class name="Megabyte.Data.Application, Megabyte" table="tb_application_mbcom">
      <id name="ID" column="appl_pk">
         <generator class="native"/>
      </id>
      <version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
      <property name="Name" column="appl_name" length="40"/>
      <bag name="Parameters" cascade="all" inverse="true" generic="true">
         <key column="parm_appl_fk"/>
         <one-to-many class="Megabyte.Data.Parameter, Megabyte"/>
      </bag>
      <bag name="Roles" cascade="all" inverse="true" generic="true">
         <key column="rol_appl_fk"/>
         <one-to-many class="Megabyte.Data.Security.Role, Megabyte"/>
      </bag>
   </class>

   <!--
     Article:
     Articles in the CMS. All text in an article is in the content table.
    -->

  <class name="Megabyte.Data.CMS.Article, Megabyte" table="tb_pagearticle_mbcom">
      <id name="ID" column="art_pk">
         <generator class="native"/>
      </id>
      <version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
      <property name="Path" column="art_code" length="50"/>
      <property name="StartDate" column="art_startdate"/>
      <property name="EndDate" column="art_enddate"/>
      <many-to-one name="Application" column="art_appl_fk"/>
      <property name="Common_RowStatus" type="string" length="1" column="aud_status"/>
      <property name="Common_Session" type="long" column="aud_session"/>
   </class>

   <!--
     Content:
     Article contents. Each record is a version of the text in one language.
    -->

  <class name="Megabyte.Data.CMS.Content, Megabyte" table="tb_pagecontent_mbcom">
      <id name="ID" column="pgc_pk">
         <generator class="native"/>
      </id>
      <version name="Common_LastModified" type="Timestamp" column="aud_timestamp"/>
      <property name="Language" column="pgc_langcode" length="2"/>
      <property name="Version" column="pgc_version"/>
      <property name="Status" column="pgc_status"/>
      <property name="Title" column="pgc_title" length="80"/>
      <property name="Summary" column="pgc_summary" length="4000"/>
      <property name="Keywords" column="pgc_keywords" length="50"/>
      <property name="BodyContent" column="pgc_bodycontent" length="102400"/>
      <property name="CreatedOn" column="pgc_createdon"/>
      <property name="ModifiedOn" column="pgc_modifiedon"/>
      <property name="ApprovedOn" column="pgc_approvedon"/>
      <many-to-one name="Article" column="pgc_art_fk"/>
      <many-to-one name="CreatedBy" column="pgc_createdby"/>
      <many-to-one name="ModifiedBy" column="pgc_modifiedby"/>
      <many-to-one name="ApprovedBy" column="pgc_approvedby"/>
      <property name="Common_RowStatus" type="string" length="1" column="aud_status"/>
      <property name="Common_Session" type="long" column="aud_session"/>
   </class>

</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
Code:
IQuery iq = NHSession.CreateQuery(
  "SELECT new ArticleIndex(cnt.Article.ID,cnt.Article.Path,cnt.Title,cnt.Summary,cnt.Status) " +
"from Content cnt " +
"where cnt.Article.Application=:app and cnt.Language=:lang");

IList<Megabyte.Data.CMS.ArticleIndex> ret =
iq.SetParameter("app", app)
.SetString("lang", lang)
.List<Megabyte.Data.CMS.ArticleIndex>();


In the above, the parameter 'app' is an object of type Megabyte.Data.Application, 'lang' is a string.

Full stack trace of any exception that occurs:

Code:
[SqlException (0x80131904): Line 1: Incorrect syntax near ')'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857322
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734934
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31
   System.Data.SqlClient.SqlDataReader.get_MetaData() +62
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +9
   NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\BatcherImpl.cs:181
   NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1395
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:415
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:181
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1783

[ADOException: could not execute query
[ select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=?  and content0_.pgc_art_fk=article1_.art_pk and )and(content0_.pgc_langcode=? ) ]
  Name: app - Value: Megabyte.Data.Application
  Name: lang - Value: mt
[SQL: select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=?  and content0_.pgc_art_fk=article1_.art_pk and )and(content0_.pgc_langcode=? )]]
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1793
   NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1735
   NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Loader\Loader.cs:1729
   NHibernate.Hql.Classic.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Hql\Classic\QueryTranslator.cs:1008
   NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\SessionImpl.cs:1773
   NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters) in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\SessionImpl.cs:1739
   NHibernate.Impl.QueryImpl.List() in X:\Systems\MBNETAPI2\NHibernate\src\NHibernate\Impl\QueryImpl.cs:87
   Index.All(Application app, String lang) in x:\Systems\MBNETAPI2\CMS\App_Code\DB.cs:246
   Index.All(Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\DB.cs:278
   CMSHierarchyArticleRoot..ctor(CMSHierarchyApplicationItem parent, Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:306
   CMSHierarchyApplicationItem..ctor(CMSHierarchyRootItem parent, Application app) in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:256
   CMSHierarchyRootItem..ctor() in x:\Systems\MBNETAPI2\CMS\App_Code\CMSHierarchyData.cs:163
   CMSTreeDataSourceView.Select() in x:\Systems\MBNETAPI2\CMS\App_Code\CMSTreeDataSourceView.cs:29
   System.Web.UI.WebControls.TreeView.DataBindNode(TreeNode node) +126
   System.Web.UI.WebControls.TreeView.PerformDataBinding() +305
   System.Web.UI.WebControls.HierarchicalDataBoundControl.PerformSelect() +82
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.TreeView.DataBind() +4
   Template.Template_Load(Object sender, EventArgs e) in x:\Systems\MBNETAPI2\CMS\Template.master.cs:44
   System.EventHandler.Invoke(Object sender, EventArgs e) +0
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Control.LoadRecursive() +131
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061


Name and version of the database you are using:

The generated SQL (show_sql=true):

select article1_.art_pk as x0_0_, article1_.art_code as x1_0_, content0_.pgc_title as x2_0_, content0_.pgc_summary as x3_0_, content0_.pgc_status as x4_0_ from tb_pagecontent_mbcom content0_, tb_pagearticle_mbcom article1_ where (article1_.art_appl_fk=? and content0_.pgc_art_fk=article1_.art_pk and and )and(content0_.pgc_langcode=? ) ]

Workaround / hack

In NHibernate\SqlCommand\QueryJoinFragment.cs, in AddCondition(string condition), add a line at the start:
Code:
if (condition.Trim().Equals("")) return false;


in AddCondition(SqlString condition), add:
Code:
if (condition.ToString().Trim().Equals("")) return false;
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.