0

I'm a newbie and trying to fetch records in a Springboot application from DB2 database with paging concept.

The service call works great when I set offset=0 and size = 'n' But when I set the offset to anything else, be it 1 or 2 or any the service fails and returns the following: (I'm unable to give the entire log because of restrictions, so please bear with it) org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

There is also an exception thrown by the SQL Driver as follows:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=OF;ROWS * AT YEAR YEARS MONTH MONTHS DAY DAYS HOUR HOURS MINUTE, DRIVER=4.21.29

Hibernate is generating the following SQL query for offset=1, size=2 (in page fetch)

SELECT * FROM (SELECT inner2_.*, rownumber() over (order by order of inner2_) as rownumber_ from (select fields from table_name fetch first rows only) as inner2_) as inner1_ where rownumber_ > <offset*size> order by rownumber_

The bold marked text is the actual query syntax, whatever is left out of bold is the field names (that cannot be publicly exposed), please substitute offset and size values as stated.

FYI: My API service call returns Page <T> as follows

Repository.findAll(PageRequest.of(offset, size)); (Repository extends JpaRepository)

Rakshith Rahul B
  • 1
  • 1
  • SQLCODE=-199 means syntax error. It's hard to guess until you show the actual query. The problem probably is in the `field` list, since, [this example](https://dbfiddle.uk/cQHRgTtv) shows, that the query is correct with proper column list (it doesn't return rows with positive parameters, but it's probably a "feature"). – Mark Barinstein Oct 22 '22 at 15:16
  • @MarkBarinstein The queries are executed on the client VM. I do realize that the syntax is correct, but the issue only persists when I set offset > 0. I am getting records for offset=0 (completely working fine with no errors whatsoever). Is there any other thing that I can produce apart from the actual fields list? I have over 45 fields in the table schema aswell, so it will be pretty much impossible to interpret the field names here without clipboard functionality. – Rakshith Rahul B Oct 22 '22 at 15:29
  • What is the exact issue? 0 rows or an error with sqlcode=-199? If latter, then try to emulate the error providing a link similar to mine above. If former, then it's expected as I said: FETCH FIRST ROWS == FETCH FERST 1 ROW, so the subselect always return no more than 1 row with row number = 1. – Mark Barinstein Oct 22 '22 at 15:41
  • @MarkBarinstein I have found another topic having the same issue. PFB the link for reference: https://stackoverflow.com/questions/40521999/when-i-am-using-it-with-db2-for-pagination-my-next-page-is-giving-error?rq=1 It seems that the problem is with Hibernate's DB2Dialect source code itself in over(..) clause in processSql method. I tried to override the processSql method in DB2Dialect file as stated in that discussion and the issue seems to be fixed. Thank you so much for your time and please do take a look at the link posted above for reference. – Rakshith Rahul B Oct 22 '22 at 20:00

0 Answers0