No, using SQL function in where clause.
On Mon, Sep 22, 2008 at 5:33 PM, Ken Egozi <[EMAIL PROTECTED]> wrote:
> Adding a SQLFunction that does not need to be compared to anything?
>
>
> On Mon, Sep 22, 2008 at 5:30 PM, Ayende Rahien <[EMAIL PROTECTED]> wrote:
>
>> Hm, that is a bug then, with NH.
>> Please file a JIRA issue for this.
>>
>>
>>
>> On Mon, Sep 22, 2008 at 5:25 PM, burkhard_m <
>> [EMAIL PROTECTED]> wrote:
>>
>>>
>>> The answer is still the same:
>>>
>>> System.Data.SqlClient.SqlException: Incorrect syntax near '='.
>>>
>>> NHibernate: select descriptio0_.Id as Id0_, descriptio0_.Text as
>>> Text0_ from [T_Descriptions] descriptio0_ where
>>> (contains(descriptio0_.Text , @p0)=1 ); @p0 = '"Text*"'
>>>
>>>
>>>
>>> On 22 Sep., 15:34, "Ayende Rahien" <[EMAIL PROTECTED]> wrote:
>>> > Try = 1
>>> >
>>> > On Mon, Sep 22, 2008 at 4:23 PM, burkhard_m
>>> > <[EMAIL PROTECTED]>wrote:
>>> >
>>> >
>>> >
>>> >
>>> >
>>> > > @Eric: thanks the Expression.Sql() restriction works.
>>> > > Anyhow there must be a way to register the function with NHibernate.
>>> > > So I tried Ayende Rahien's suggestion but still no success.
>>> >
>>> > > I registered the CONTAINS function like this:
>>> > > RegisterFunction("contains", new
>>> > > SQLFunctionTemplate(NHibernateUtil.Boolean, "contains(?1,?2)"))); [/
>>> > > code]
>>> >
>>> > > So now, the HQL parser expects CONTAINS to be a function with return
>>> > > type boolean. In other words, its a [i]predicate[/i], a
>>> boolean-valued
>>> > > characteristic function.
>>> >
>>> > > So I wrote the query like this:
>>> > > ISession session = GetSession();
>>> > > var result = session.CreateQuery("FROM Description d WHERE
>>> > > contains( d.Text, :searchValue)").SetString("searchValue", "\"Text*
>>> > > \"").List();
>>> >
>>> > > But NHibernate complains, because it expects a comparison, like
>>> > > ... WHERE contains( d.Text, :searchValue) = TRUE ...
>>> >
>>> > > So I changed the query to:
>>> > > ISession session = GetSession();
>>> > > var result = session.CreateQuery("FROM Description d WHERE
>>> > > contains( d.Text, :searchValue)" = TRUE).SetString("searchValue",
>>> > > "\"Text*\"").List();
>>> > > Now the query is accepted by NHibernate, but of course that's no
>>> valid
>>> > > SQL statement and MSSQL Server complains about the syntax.
>>> >
>>> > > I found the same problem described here:
>>> > >http://forum.hibernate.org/viewtopic.php?p=2386233
>>> >
>>> > > So the question is, how do I treat predicate functions?
>>> > > And how do I express the query, that both NHibernate and MSSQL-Server
>>> > > accept it?
>>> >
>>> > > On 19 Sep., 17:35, Eric Hauser <[EMAIL PROTECTED]> wrote:
>>> > > > If you don't want to mess with learning the Criteria API for
>>> complex
>>> > > > queries, then just use the native SQL option:
>>> >
>>> > > >http://www.hibernate.org/hib_docs/nhibernate/html/querysql.html
>>> >
>>> > > > On Sep 19, 11:10 am, burkhard_m <[EMAIL PROTECTED]>
>>> > > > wrote:
>>> >
>>> > > > > Hi Eric,
>>> >
>>> > > > > Thanks, I read about that kind of solution. But in my case it's
>>> rather
>>> > > > > complicated.
>>> >
>>> > > > > For each Article we have diffrent kind of lists containing
>>> > > > > descriptions in diffrent languages.
>>> > > > > In particular we have ShortTexts, LongTexts, Keywords and
>>> MediaTexts
>>> > > > > for each Article.
>>> >
>>> > > > > Here is a sample query using the LIKE-Expression:
>>> >
>>> > > > > new SimpleQuery<Article>(typeof(Article), "SELECT DISTINCT a FROM
>>> > > > > Article a, ShortTextDescriptionContainer stc,
>>> ShortTextDescription sd,
>>> > > > > LongTextDescriptionContainer ltc, LongTextDescription ld WHERE
>>> (stc
>>> > > > > IN elements(a.ShortTexts) AND sd IN (FROM stc.Content WHERE Text
>>> > > > > LIKE ?)) OR (ltc IN elements(a.LongTexts) AND ld IN (FROM
>>> ltc.Content
>>> > > > > WHERE Text LIKE ?))", "%Calculator%", "%Calculator%");
>>> >
>>> > > > > Of course we've got a lot of descriptions and using the LIKE-
>>> > > > > Expression takes minutes. So we'd like to use full text search
>>> > > > > instead.
>>> > > > > Maybe there is a better solution?
>>> >
>>> > > > > On 19 Sep., 16:42, Eric Hauser <[EMAIL PROTECTED]> wrote:
>>> >
>>> > > > > > For a simple case like that, I would just use the criteria API
>>> and an
>>> > > > > > Expression.Sql() restriction.
>>> >
>>> > >
>>> http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/qu...
>>> >
>>> > > > > > On Sep 19, 7:57 am, burkhard_m <[EMAIL PROTECTED]
>>> >
>>> > > wrote:
>>> >
>>> > > > > > > Hi everyone!
>>> >
>>> > > > > > > I'm currently working on a small merchandise management tool
>>> and
>>> > > have
>>> > > > > > > to implement a full-text search for the article
>>> descriptions.Our
>>> > > > > > > project is based on NHibernate & ActiveRecord as well as
>>> MSSQL
>>> > > Server
>>> > > > > > > 2005 and we would prefer to use the indexing capability of
>>> the SQL
>>> > > > > > > Server rather than implementing NHibernate.Search with
>>> Lucene.NET.
>>> >
>>> > > > > > > I have read about several suggestions at Google Groups
>>> including
>>> > > > > > > Ayende Rahien's:
>>> > > > > > > "You need to create an derived dialect and register the
>>> contains
>>> > > > > > > function, if
>>> > > > > > > you want to use HQL. Or, you can create an ICritertion
>>> > > implementation
>>> > > > > > > that will deal with this."
>>> > > > > > > (seehttp://
>>> > > groups.google.com/group/nhusers/browse_thread/thread/e9745dafd...)
>>> >
>>> > > > > > > I tried hard for several days but I'm stuck. I have no idea
>>> how to
>>> > > > > > > implement this correctly.
>>> > > > > > > All I get is a QueryException telling me that I have a
>>> "Incorrect
>>> > > > > > > query syntax".
>>> >
>>> > > > > > > Yes, the Full text indexing is activated for the property
>>> 'Text'.
>>> > > > > > > The SQL-Query works:
>>> > > > > > > SELECT * FROM [dbo].[T_Descriptions]
>>> > > > > > > WHERE CONTAINS(Text, ' "T*" ')
>>> >
>>> > > > > > > The HQL-Query "FROM Description d WHERE d.Text LIKE 'T%'"
>>> also
>>> > > works
>>> > > > > > > fine.
>>> >
>>> > > > > > > Would anyone help me, please?
>>> > > > > > > Maybe you could tell me what I have done wrong from the
>>> following
>>> > > > > > > code?
>>> >
>>> > > > > > > Thanks a lot!
>>> >
>>> > > > > > > Best regards,
>>> > > > > > > Martin
>>> >
>>> > > > > > > // The config section
>>> >
>>> > > > > > > <activerecord isWeb="false">
>>> > > > > > > <config>
>>> > > > > > > <add key="hibernate.connection.driver_class"
>>> > > > > > > value="NHibernate.Driver.SqlClientDriver" />
>>> > > > > > > <add key="hibernate.dialect"
>>> value="SQLServerDialectWithFTS,
>>> > > > > > > <AssemblyName>" />
>>> > > > > > > <add key="hibernate.connection.provider"
>>> > > > > > > value="NHibernate.Connection.DriverConnectionProvider" />
>>> > > > > > > <add key="hibernate.max_fetch_depth" value="3"></add>
>>> > > > > > > <add key="hibernate.connection.connection_string"
>>> > > > > > > value="ConnectionString = ${ourConnectionString}" />
>>> > > > > > > </config>
>>> > > > > > > </activerecord>
>>> >
>>> > > > > > > // The model
>>> >
>>> > > > > > > [ActiveRecord("[T_Descriptions]")]
>>> > > > > > > [Serializable]
>>> > > > > > > public class Description : ActiveRecordBase<Description>
>>> > > > > > > {
>>> > > > > > > protected Description() {}
>>> >
>>> > > > > > > protected Description(string text) : this()
>>> > > > > > > {
>>> > > > > > > Text = text;
>>> > > > > > > }
>>> >
>>> > > > > > > [PrimaryKey]
>>> > > > > > > public long Id { get; set; }
>>> >
>>> > > > > > > [Property]
>>> > > > > > > public string Text { get; set; }
>>> > > > > > > }
>>> >
>>> > > > > > > // The customized dialect
>>> >
>>> > > > > > > public class SQLServerDialectWithFTS : MsSql2005Dialect
>>> > > > > > > {
>>> > > > > > > public SQLServerDialectWithFTS()
>>> > > > > > > {
>>> > > > > > > RegisterFunction("CONTAINS", new
>>> > > > > > > StandardSQLFunction("CONTAINS", NHibernateUtil.String));
>>> > > > > > > }
>>> > > > > > > }
>>> >
>>> > > > > > > // The search
>>> >
>>> > > > > > > IConfigurationSource source =
>>> > > ActiveRecordSectionHandler.Instance;
>>> >
>>> > >
>>> Castle.ActiveRecord.ActiveRecordStarter.Initialize(typeof(Description).Asse
>>> > > mbly,
>>> > > > > > > source);
>>> >
>>> > > > > > > using(SessionScope scope = new SessionScope())
>>> > > > > > > {
>>> > > > > > > var sq = new SimpleQuery<Description>(typeof
>>> (Description),
>>> > > > > > > "FROM Description
>>> d
>>> > > WHERE
>>> > > > > > > CONTAINS(d.Text, ' \"T*\" ')");
>>> > > > > > > var res = sq.Execute();
>>> > > > > > > }- Zitierten Text ausblenden -
>>> >
>>> > > > > > - Zitierten Text anzeigen -- Zitierten Text ausblenden -
>>> >
>>> > > > - Zitierten Text anzeigen -- Zitierten Text ausblenden -
>>> >
>>> > - Zitierten Text anzeigen -
>>>
>>>
>>
>>
>>
>
>
> --
> Ken Egozi.
> http://www.kenegozi.com/blog
> http://www.musicglue.com
> http://www.castleproject.org
> http://www.gotfriends.co.il
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Castle Project Users" 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/castle-project-users?hl=en
-~----------~----~----~----~------~----~------~--~---
