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