tools
Hibernate 2.1.1
SQL SERVER database

I am trying to build a search engine for my database, and am using a full text index with a contains clause. I am putting the results in a scrollableResult so that i can display the results 20 per page.

With this code:
ScrollableResults sr = null;
try {
Session session = HibernateSession.getSession();
if (className != null) {
Query q = session.createSQLQuery(qry, "scrollable", className);
sr = q.scroll();
}

I get the following:
12:13:30,929 ERROR HibernateDataManager:153 - EXCEPTION
java.lang.UnsupportedOperationException: SQL queries do not currently support iteration at net.sf.hibernate.impl.SQLQueryImpl.scroll(SQLQueryImpl.java:60)
at org.guidestar.database.dm.HibernateDataManager.retrieveScrollableObjQry(HibernateDataManager.java:146)
at org.guidestar.uk.search.dm.UkSearchDataManager.getUkSearchResults(UkSearchDataManager.java:85)

I have found that if I dont use createSQLQuery, that HIbernate doesn't seem to support the syntax for the CONTAINS Cluase. Is this true?
To me it seems like a normal thing to do, get the count and if i get the right number of results, display them. maybe it's just in my world. but i did not find much if any documentation of FAQ's about my situation. I did find that for some reason I had to go back to straight JDBC just to get the count, do I have to do the same for the task i am trying to achieve?

Dave

You can in the moment not use scroll with createSQLQuery. I suggest you put a limit clause in your query and do paging that way.

if createSQLQuery can not support iterate or scroll, does HQL support the CONTAINS clause?

in the where-clause most likely yes ... what is the syntax in SQL?

example --
SELECT COUNT(*) as count FROM Uk_Search uks
WHERE ( CONTAINS ( uks.orgName, '"HOMEBOUND" AND "CRAFTSMEN" AND "TRUST"') )

when i originally tested this i was running the following query (three different fields, just more paren's...Hibernate was complaining about the number of paren's --

SELECT COUNT(*) as count FROM Uk_Search uks
WHERE ( (CONTAINS ( uks.orgName, '"HOMEBOUND" AND "CRAFTSMEN" AND "TRUST"') ) or (CONTAINS ( uks.orgAbbrev, '"HOMEBOUND" AND "CRAFTSMEN" AND "TRUST"') ) or (CONTAINS ( uks.altName, '"HOMEBOUND" AND "CRAFTSMEN" AND "TRUST"') ) )

Hm yes, this might probably confuse the parser - no other solution so as to use limit clauses together with createSqlQuery.