1

I have a MySQL Stored Procedure click here for stored procedure and calling the Procedure using Hibernate

Hibernate Code:

int ps=5;

    SQLQuery query=session2.createSQLQuery("CALL AbsentReportproc(:_fromdate,:_todate)");
            query.setParameter("_fromdate", fromdate);
            query.setParameter("_todate", todate);
            query.setFirstResult(ps*(pno-1));
            query.setMaxResults(ps);
             List<Object[]> empList=query.list();

when I execute above code I'm displayed with the following Error Message:

org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'limit 5' at line 1

Note:If i remove the below statement from the code,I'm displayed with all records in a single jsp Page

query.setMaxResults(ps);

Could any one give me the solution what was the Problem?

thanks...

Community
  • 1
  • 1
String
  • 3,660
  • 10
  • 43
  • 66

1 Answers1

0

It is clearly a jdbc driver error that you are using;

Also try this with setFetchSize , and if this doesn't work also then,

I would suggest not to get the details out of the query in chunks if you want to display them all and there is not much data and you should rather store all the data without calling this method query.setMaxResults(ps); into a collection.

And when you want to display that data in pages, then get subList in case you are using List, to break the data at the application level and then display it on your view that is jsp in this case.

Prateek
  • 3,923
  • 6
  • 41
  • 79