Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Fri Apr 28, 2017 3:38 pm 
Newbie

Joined: Fri Apr 28, 2017 3:30 pm
Posts: 3
DB2/390 Dialect throws "offset not supported error" while trying to fetch the 2nd page result. Passing parameters like PageSize limit etc from the from the front end. This only happens after upgrading to Hibernate 5 recently. Any suggestions for this?

Code:
private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
      @Override
      public String processSql(String sql, RowSelection selection) {
         if (LimitHelper.hasFirstRow( selection )) {
            throw new UnsupportedOperationException( "query result offset is not supported" );
         }
         return sql + " fetch first " + getMaxOrLimit( selection ) + " rows only";
      }
};



I can view the above code in the DB2390 dialect. So what should be done if we want to implement the pagination for DB2 mainframe database in the latest hibernate framework?


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Mon May 01, 2017 3:46 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1226
You need to set the following configuration property:

Code:
<property name="hibernate.legacy_limit_handler" value="true"/>

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Thu May 04, 2017 11:17 am 
Newbie

Joined: Fri Apr 28, 2017 3:30 pm
Posts: 3
Thank you for the suggestion.


hibernate:
ddl-auto: validate
naming-strategy: org.hibernate.cfg.EJB3NamingStrategy
properties:
hibernate.cache.use_second_level_cache: false
hibernate.cache.use_query_cache: false
hibernate.generate_statistics: false
hibernate.dialect: org.hibernate.dialect.DB2390Dialect
hibernate.legacy_limit_handler: true

this is the application.yml which I use.
After the suggestion, I provided the property hibernate.legacy_limit_handler: true. but, still getting the same exception.
Am I missing some other property or is it the right place to provide the property.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Thu May 04, 2017 1:06 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1226
I'm not sure what other thing you could set. I don't have a DB2/390 to test the Dialect. Check some older Hibernate versions to see if it worked better so that we can find out if there was some change that caused this issue.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Thu May 04, 2017 1:40 pm 
Newbie

Joined: Fri Apr 28, 2017 3:30 pm
Posts: 3
It was working for the hibernate 4.x . After we upgraded, it is somehow setting the offset. Still finding the code which is causing the issue. Thanks vlad for the help.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Fri May 05, 2017 1:45 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1226
Try copy/pasting the DB2390Dialect so that you create your CustomDB2390Dialect and you remove the following if branch:

Code:
if (LimitHelper.hasFirstRow( selection )) {
    throw new UnsupportedOperationException( "query result offset is not supported" );
}


Then configure Hibernate to use this CustomDB2390Dialect instead, and let me know if it works.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Fri May 12, 2017 3:19 am 
Newbie

Joined: Fri May 12, 2017 1:12 am
Posts: 2
I found the solution with "hibernate.legacy_limit_handler = true" for DB2390Dialect some time ago, and got rid of the exception. But I now have a new problem.
For example, suppose the table MYTABLE contains the following rows (column "client" is primary key):
Code:
client description
----------------------------
A      a description for A
B      a description for B
C      a description for C

When I set "TypedQuery.setMaxResults(2)", both Hibernate 4.3.11 and Hibernate 5.2.10 generate the following SQL queries with "TypedQuery.getResultList()":
First call:
Code:
select
    myclient0_.client as client1_0_,
    myclient0_.description as descript2_0_
from
    MYTABLE scmsclient0_ fetch first 2 rows only

Second call:
Code:
select
    myclient0_.client as client1_0_,
    myclient0_.description as descript2_0_,
from
    MYTABLE scmsclient0_ fetch first 4 rows only

As expected, Hibernate 4 returns "(A, B)" on the first call and "(C)" on the second call.
Hibernate 5 returns "(A, B)" with every call, i.e. always the two first rows.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Sat May 13, 2017 3:06 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1226
You need to open a Jira issue for this.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Tue May 16, 2017 11:55 pm 
Newbie

Joined: Fri May 12, 2017 1:12 am
Posts: 2
Thanks!
I've done it and the problem is now fixed: HHH-11747


Top
 Profile  
 
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Wed May 17, 2017 12:15 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1226
Thanks.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.