-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
deepak_java
 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  
 
vlad
 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: 1628
Location: Romania
You need to set the following configuration property:

Code:
<property name="hibernate.legacy_limit_handler" value="true"/>
Top
 Profile  
 
deepak_java
 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  
 
vlad
 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: 1628
Location: Romania
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.
Top
 Profile  
 
deepak_java
 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  
 
vlad
 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: 1628
Location: Romania
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.
Top
 Profile  
 
mglasel
 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  
 
vlad
 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: 1628
Location: Romania
You need to open a Jira issue for this.
Top
 Profile  
 
mglasel
 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  
 
vlad
 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: 1628
Location: Romania
Thanks.
Top
 Profile  
 
vlad
 Post subject: Re: DB2/390 Dialect throws offset not supported error pagination
PostPosted: Thu Aug 24, 2017 4:58 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You'll just have to upgrade to 5.2.11 when it's released.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

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.