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