Hibernate version: 2.1.6
Name and version of the database you are using: MySql 4.0.17

I've just discovered the really useful features: setMaxResults() & setFirstResult() in hibernate, but i've ran into a problem! When I call list() on a Query with maxResults set I get the following error:

java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '5 advert0_.id as id, advert0_.expiryDate as expiryDate, advert0

I've diagnosed this problem as being due to the SQL that is generated:

Hibernate: select top ? advert0_.id as id, advert0_.expiryDate as expiryDate, advert0_.property_id as property3_, advert0_.isSuspended as isSuspen4_, advert0_.timeStamp as timeStamp, advert0_.user_id as user_id, advert0_.image_id as image_id from advert advert0_ where (advert0_.isSuspended='0' )

The very first part specifies select top ? but this isn't valid syntax for a SQL query when using mySql. Looking around on google this is MSSQL syntax. My first thought was the dialect being use was incorrect, but a little research concluded the following is correct (which I am using):

hibernate.dialect net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class = com.mysql.jdbc.Driver

So i'm a little stumped! Can you use these function when using hibernate with a mySql database?

Thanks in advance

Had anyone any ideas to this problem? I've still not found a solution.......

Thanks,

Be sure (through the logs) this is the actual dialect used