Search code examples
sqlhibernatehqlsap-ase

Hibernate setMaxResults() not working for Sybase database query

Either of the following two approaches to executing a simple Hibernate query with limited results do not work in Sybase. They both result in a SybSQLException: Incorrect syntax near '@p0'.

Query q = session.createQuery( "from Record" );
q.setMaxResults( 50 );
q.list();

or

Criteria criteria = session.createCriteria( Record.class );
criteria.setMaxResults( 50 );
criteria.list();

It appears the actual SQL generated in both of these cases looks like...

select top ? record_id, etc...

and Sybase is balking at the ?, which Hibernate is not filling in with the value 50 (this is my guess). I've searched everywhere and while others have encountered a similar error, it was not due to attempting to limit the results.

I can execute a direct SQL statement such as 'select top 50 from Record' and it works perfectly, so I know my version of Sybase supports the syntax.

I'm using Hibernate 3.2 and Sybase ASE 15.0.2

Solution

  • Perhaps you configured Hibernate to use a wrong SQL dialect.

    It looks like HSQLDialect is the only dialect that can produce limit ? ?, and it's definitely a wrong choice for Sybase.

    See also: