query result offset is not supported

[SKKB]

Hi folks,

we are testing application in ColdFusion 2021
I am getting below error message as i am using offset in ormExecuteQuery()
we used offset for pagination.
error is : "query result offset is not supported"

I tried below steps to resolve but none work for me.

1. Adding hibernate configuration file
this.ormSettings.ormconfig = "hibernate.cfg.xml";

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name = "hibernate.legacy_limit_handler">true</property>

</session-factory>
</hibernate-configuration>

2. Adding below setting along with other orm settings.
<cfset structInsert( this.ormSettings,'hibernate',structNew(),true )>
<cfset structInsert( this.ormSettings.hibernate,'legacy_limit_handler',true,true )>

i am not sure this works or not but tried based on other community posts.

3. Adding as java arguments.
Add the following flag in ColdFusion's jvm.config file.
-DHibernate.legacy_limit_handler=true

Any words of wisdom would be appreciated.

[BKBK]

I suspect that the cause is not the hibernate.legacy_limit_handler setting. I would therefore suggest that you do nothing about that setting. In effect, you would then be using its default value in fact. Such defaults are usually optimized.

Could you share with us the code using offset in ormExecuteQuery()? That is probably the cause of the error.

[SKKB]

Thak you @BKBK 

<cfset NoteRecords = ormExecuteQuery("select c.notes from note c",[],false,{offset=10, maxresults=20})/>

The above query is the simplified version of query i am using to replicate the issue.

and it's working fine in CF2016.

as we are testing our application in CF2021 i am getting below error.

Message:query result offset is not supported
StackTrace:java.lang.UnsupportedOperationException: query result offset is not supported at org.hibernate.dialect.pagination.TopLimitHandler.processSql(TopLimitHandler.java:56) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1906) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1889) at org.hibernate.loader.Loader.doQuery(Loader.java:934) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:351) at org.hibernate.loader.Loader.doList(Loader.java:2688) at org.hibernate.loader.Loader.doList(Loader.java:2671) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2503) at org.hibernate.loader.Loader.list(Loader.java:2498) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:379) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1488) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) at coldfusion.orm.hibernate.HibernatePersistenceManager._executeHQL(HibernatePersistenceManager.java:790) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeHQL(HibernatePersistenceManager.java:714) at coldfusion.orm.hibernate.HibernatePersistenceManager.executeQuery(HibernatePersistenceManager.java:583) at coldfusion.orm.ORMUtils._executeQuery(ORMUtils.java:376) at coldfusion.orm.ORMUtils.executeQuery(ORMUtils.java:366) at coldfusion.orm.ORMServiceImpl.executeQuery(ORMServiceImpl.java:215) at coldfusion.runtime.CFPage.ORMExecuteQuery(CFPage.java:14767)

adding new property as per below update
https://helpx.adobe.com/coldfusion/using/whats-new-coldfusion-2018.html
Hibernate 5 no longer supports offset queryoptions as it was inefficient and most of the underlying databases have the support for offset. However, this functionality can be enabled by setting the flag hibernate.legacy_limit_handler to true in the hibernate configuration file.

[SKKB]

it's still in progress

https://tracker.adobe.com/#/view/CF-4208163

[BKBK]

Thanks for the Tracker link. I have added my vote to the ticket.