Only for information the HQL is<query name="Test" cacheable="false" read-only="true"> from Type t where t.id in (:myList) <query>
2009/2/24 antoschka <[email protected]> > > Thanks. Yepp, I didn't see it before!!! > > The solution for everybody who hits the same problem are the > parenthesis around the parameter - so it should look like that > <sql-query name="Test" cacheable="false" read-only="true"> > <return alias="type" class="MyClass.Type, MyAssembly"/> > <![CDATA[ > SELECT > {type.*} > FROM Type type > WHERE > type.Id IN (:myList) > ]]> > </sql-query> > > On 24 Feb., 20:16, Fabio Maulo <[email protected]> wrote: > > wrong SQL syntax, review your IN clause > > > > 2009/2/24 antoschka <[email protected]> > > > > > > > > > > > > > well I thought I could summarize it - here is the complete query > > > (MyClass & MyAssembly are not teh real names ofcourse): > > > <sql-query name="Test" cacheable="false" read-only="true"> > > > <return alias="type" class="MyClass.Type, MyAssembly"/> > > > <![CDATA[ > > > SELECT > > > {type.*} > > > FROM Type type > > > WHERE > > > type.Id IN :myList > > > ]]> > > > </sql-query> > > > > > here the code which throws an exception in the last line > > > > > ISession session = SessionProviderStatic.Instance.GetSession(); > > > IQuery query = session.GetNamedQuery("Test"); > > > query.SetParameterList("myList", new ArrayList() > > > { "employee", "assistant" }); > > > IList returnList = query.List(); > > > > > The exception itself comes from: > > > - class: NHibernate.Loader.Loader > > > - method: DoList > > > - exception text: > > > NHibernate.ADOException wurde nicht behandelt. > > > Message="could not execute query\r\n[ SELECT\n type.Id as Id2_0_, > > > type.SuperType as SuperType2_0_, type.Name as Name2_0_, type.Icon as > > > Icon2_0_, type.IsStructuralType as IsStruct5_2_0_, type.IsEditable as > > > IsEditable2_0_, type.RootInPerspective as RootInPe7_2_0_\n FROM > > > Type type\n WHERE\n type.Id IN @p0, @p1 ]\r\n Name:myList_0_ - > > > Value:mitarbeiter Name:myList_1_ - Value:abteilung\r\n[SQL: SELECT > > > \n type.Id as Id2_0_, type.SuperType as SuperType2_0_, type.Name as > > > Name2_0_, type.Icon as Icon2_0_, type.IsStructuralType as > > > IsStruct5_2_0_, type.IsEditable as IsEditable2_0_, > > > type.RootInPerspective as RootInPe7_2_0_\n FROM Type type\n WHERE > > > \n type.Id IN @p0, @p1]" > > > Source="NHibernate" > > > StackTrace: > > > bei NHibernate.Loader.Loader.DoList(ISessionImplementor > > > session, QueryParameters queryParameters) in C:\Projekte\NHibernate\src > > > \NHibernate\Loader\Loader.cs:Zeile 1713. > > > bei NHibernate.Loader.Loader.ListIgnoreQueryCache > > > (ISessionImplementor session, QueryParameters queryParameters) in C: > > > \Projekte\NHibernate\src\NHibernate\Loader\Loader.cs:Zeile 1628. > > > bei NHibernate.Loader.Loader.List(ISessionImplementor session, > > > QueryParameters queryParameters, ISet`1 querySpaces, IType[] > > > resultTypes) in C:\Projekte\NHibernate\src\NHibernate\Loader > > > \Loader.cs:Zeile 1622. > > > bei NHibernate.Loader.Custom.CustomLoader.List > > > (ISessionImplementor session, QueryParameters queryParameters) in C: > > > \Projekte\NHibernate\src\NHibernate\Loader\Custom > > > \CustomLoader.cs:Zeile 272. > > > bei NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery > > > customQuery, QueryParameters queryParameters, IList results) in C: > > > \Projekte\NHibernate\src\NHibernate\Impl\SessionImpl.cs:Zeile 1716. > > > bei NHibernate.Impl.SessionImpl.List > > > (NativeSQLQuerySpecification spec, QueryParameters queryParameters, > > > IList results) in C:\Projekte\NHibernate\src\NHibernate\Impl > > > \SessionImpl.cs:Zeile 1702. > > > bei NHibernate.Impl.SessionImpl.List > > > (NativeSQLQuerySpecification spec, QueryParameters queryParameters) in > > > C:\Projekte\NHibernate\src\NHibernate\Impl\SessionImpl.cs:Zeile 1684. > > > bei NHibernate.Impl.SqlQueryImpl.List() in C:\Projekte > > > \NHibernate\src\NHibernate\Impl\SqlQueryImpl.cs:Zeile 127. > > > bei Program.Main() in C:\Program.cs:Zeile 42. > > > bei System.AppDomain._nExecuteAssembly(Assembly assembly, String > > > [] args) > > > bei System.AppDomain.ExecuteAssembly(String assemblyFile, > > > Evidence assemblySecurity, String[] args) > > > bei > > > Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() > > > bei System.Threading.ThreadHelper.ThreadStart_Context(Object > > > state) > > > bei System.Threading.ExecutionContext.Run(ExecutionContext > > > executionContext, ContextCallback callback, Object state) > > > bei System.Threading.ThreadHelper.ThreadStart() > > > InnerException: System.Data.SqlClient.SqlException > > > Message="Wrong Syntax near '@p0'." > > > Source=".Net SqlClient Data Provider" > > > ErrorCode=-2146232060 > > > Class=15 > > > LineNumber=5 > > > Number=102 > > > Procedure="" > > > Server=".\\SQLEXPRESS2008" > > > State=1 > > > StackTrace: > > > bei System.Data.SqlClient.SqlConnection.OnError > > > (SqlException exception, Boolean breakConnection) > > > bei System.Data.SqlClient.SqlInternalConnection.OnError > > > (SqlException exception, Boolean breakConnection) > > > bei > > > System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning > > > (TdsParserStateObject stateObj) > > > bei System.Data.SqlClient.TdsParser.Run(RunBehavior > > > runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, > > > BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject > > > stateObj) > > > bei System.Data.SqlClient.SqlDataReader.ConsumeMetaData() > > > bei System.Data.SqlClient.SqlDataReader.get_MetaData() > > > bei System.Data.SqlClient.SqlCommand.FinishExecuteReader > > > (SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) > > > bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds > > > (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean > > > returnStream, Boolean async) > > > bei System.Data.SqlClient.SqlCommand.RunExecuteReader > > > (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean > > > returnStream, String method, DbAsyncResult result) > > > bei System.Data.SqlClient.SqlCommand.RunExecuteReader > > > (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean > > > returnStream, String method) > > > bei System.Data.SqlClient.SqlCommand.ExecuteReader > > > (CommandBehavior behavior, String method) > > > bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader > > > (CommandBehavior behavior) > > > bei > > > System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() > > > bei NHibernate.AdoNet.AbstractBatcher.ExecuteReader > > > (IDbCommand cmd) in C:\Projekte\NHibernate\src\NHibernate\AdoNet > > > \AbstractBatcher.cs:Zeile 220. > > > bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, > > > Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, > > > ISessionImplementor session) in C:\Projekte\NHibernate\src\NHibernate > > > \Loader\Loader.cs:Zeile 1369. > > > bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor > > > session, QueryParameters queryParameters, Boolean returnProxies) in C: > > > \Projekte\NHibernate\src\NHibernate\Loader\Loader.cs:Zeile 398. > > > bei > > > NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections > > > (ISessionImplementor session, QueryParameters queryParameters, Boolean > > > returnProxies) in C:\Projekte\NHibernate\src\NHibernate\Loader > > > \Loader.cs:Zeile 233. > > > bei NHibernate.Loader.Loader.DoList(ISessionImplementor > > > session, QueryParameters queryParameters) in C:\Projekte\NHibernate\src > > > \NHibernate\Loader\Loader.cs:Zeile 1704. > > > InnerException: > > > > > Thanks for your help > > > > > antoschka > > > On 24 Feb., 19:31, Fabio Maulo <[email protected]> wrote: > > > > Which is the return alias ?The SQL is not enough, why you don't send > the > > > > whole <sql-query> node ? > > > > > > 2009/2/24 antoschka <[email protected]> > > > > > > > Yes I know this chapter quite well but the IN-construction is not > > > > > touched there. > > > > > > > I tried it alread like that: > > > > > <![CDATA[ > > > > > SELECT > > > > > {type.*} > > > > > FROM Type type > > > > > WHERE > > > > > type.Id IN :myTypeList > > > > > ]]> > > > > > > > query.SetParameterList("myTypeList ", new ArrayList(){ "employee", > > > > > "assistant" }); > > > > > IList returnList = query.List(); > > > > > > > but it always complains about the syntax of myTypeList. > > > > > > > The signature of SetParameterList asks for ICollection, so I don't > see > > > > > any reason why arraylist shoul not work > > > > > > > Any idea? > > > > > > > antoschka > > > > > > > On 24 Feb., 17:59, Fabio Maulo <[email protected]> wrote: > > > > > >http://nhforge.org/doc/nh/en/index.html#querysql-namedqueries > > > > > > > > 2009/2/24 antoschka <[email protected]> > > > > > > > > > Hi, > > > > > > > > > I was wondering if it is possible to create a named query like > > > this: > > > > > > > <![CDATA[ > > > > > > > SELECT > > > > > > > {type.*} > > > > > > > FROM Type type > > > > > > > WHERE > > > > > > > type.Id IN :myTypeList > > > > > > > ]]> > > > > > > > > > If this is Possible how do I need to set the myTypeList > parameter. > > > > > > > > > I tried some but it always complains about the syntax. > > > > > > > > > Any idea how to approach that > > > > > > > > > Thanks for your help > > > > > > > > > antoschka > > > > > > > > -- > > > > > > Fabio Maulo > > > > > > -- > > > > Fabio Maulo > > > > -- > > Fabio Maulo > > > -- Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
