I'm trying to execute a search query with hibernate and the query is failing because hibernate does not recognize MS SQL Server's keyword "FREETEXT".

Select S.serviceId from ServiceCbo S where FREETEXT(S.serviceName, '" + userInput + "')

I've been struggling use this for DAYS!! and there are not any information online regarding this keyword.

you should define such function in dialect.

just extends SQLServerDialect and register this function in constructor.

something like this:

use your dialect

If I create a class that extends the SQLServerDialect, when will it be invokes?

I'm using a Spring + Hibernate. So, Spring is initializing my DAO interfaces and Impl classes.

I'm not sure where to implement the provided suggestion. I've haven't use the SQLServerDialect functionality before.

You should define such dialect in one of your config xmls. Isn't it? try to search dialect word in your xmls

Hi I used the advice in this thread and created a custom SQL Server Dialect like the post mentioned. Everything is working when I test all my existing hql queries but when I get to the one query that wants to use FREETEXT I get an error.

I implemented my custom dialect like this:
public class customSQLServerDialect extends SQLServerDialect{

public customSQLServerDialect() {
super();
registerFunction( "FREETEXT", new StandardSQLFunction("FREETEXT", Hibernate.BOOLEAN) );
}
}

I wrote my hql query like this

select store
from com.myapp.data.Store store
where store.state = :state AND (store.city = :city OR store.city = :cityAlt) AND FREETEXT(store.address, :streetAddress)
order by store.name asc

I have narrowed down the issue to the use of free text because this query works fine but returns 0 stores

select store
from com.myapp.data.Store store
where store.state = :state AND (store.city = :city OR store.city = :cityAlt) AND store.address = :streetAddress
order by store.name asc


the exception I get is this:

[03/01/2009 16:57:41:921] ERROR org.hibernate.hql.PARSER - <AST>: unexpected AST node: (
[03/01/2009 16:57:41:921] ERROR org.hibernate.impl.SessionFactoryImpl - Error in named query: AddressSearch
org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: ( [
select store
from com.pmusa.signup.data.Store store
where store.state = :state AND (store.city = :city OR store.city = :cityAlt) AND FREETEXT(store.address, :streetAddress)
order by store.name asc
]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:235)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:402)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:352)

Can anyone provide any help on this?