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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
