query result offset is not supported

Explorer ,
Jun 09, 2021 Jun 09, 2021

Copy link to clipboard

Copied

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.

Views

97

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jun 13, 2021 Jun 13, 2021

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 14, 2021 Jun 14, 2021

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 14, 2021 Jun 14, 2021

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Jun 14, 2021 Jun 14, 2021

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines