Named SQL Query, Query.setMaxResults, SQL Server 2000

bpapa · **Joined:** Fri Jul 29, 2005 6:21 pm **Posts:** 2

I have a full-text search query that I'm writing for SQL Server 2000. Since I need to use the CONTAINS operator, I decided to write a native SQL query. At the same time, I want to page through the results, so I am using the setMaxResults method of the Query object. My code looks like this -

Code:

Query query = sess.getNamedQuery(queryStr);
query.setMaxResults(pgSize);

What happens though, is the SQL Query created fails - the error message is -

Code:

18:06:42,953 ERROR [JDBCExceptionReporter] Incorrect syntax near the keyword 'top'.

And the statement generated by the log looks something like this -

Code:

top 26
        SELECT
           <some vars>
        FROM <some tables>
        WHERE <some condition>

The way SQL Server expects top to be used is something like

Code:

SELECT top (some stuff)

So I'm assuming Hibernate (version 2.x) is generating the wrong Statement. Am I stuck here? Or is there some workaround tht I haven't thought of?[/code]

pksiv · **Posted:** Fri Jul 29, 2005 8:31 pm

bpapa wrote:

I have a full-text search query that I'm writing for SQL Server 2000. Since I need to use the CONTAINS operator, I decided to write a native SQL query. At the same time, I want to page through the results, so I am using the setMaxResults method of the Query object. My code looks like this -

Code:

Query query = sess.getNamedQuery(queryStr);
query.setMaxResults(pgSize);

What happens though, is the SQL Query created fails - the error message is -

Code:

18:06:42,953 ERROR [JDBCExceptionReporter] Incorrect syntax near the keyword 'top'.

And the statement generated by the log looks something like this -

Code:

top 26
        SELECT
           <some vars>
        FROM <some tables>
        WHERE <some condition>

The way SQL Server expects top to be used is something like

Code:

SELECT top (some stuff)

So I'm assuming Hibernate (version 2.x) is generating the wrong Statement. Am I stuck here? Or is there some workaround tht I haven't thought of?[/code]

What does your named-query mapping look like ?

gavin · **Posted:** Fri Jul 29, 2005 8:36 pm

The HB2 support for setMaxResults() was never designed to be used with native SQL queries.

However, you can easily create a custom dialect that will support this, by copying HB3.