SQL 2 query

Avatar

Avatar

suhass86991778

Avatar

suhass86991778

suhass86991778

14-11-2019

Hi Team,

On v6.4.4.0, I am running a simple SQL query in crx/de tools and I get the expected results.

select * from [cq:PageContent] where ISDESCENDANTNODE('/content/xxx/xxx/xxx') AND [customProperty] IS NOT NULL

When I do the same with my servlet, I am getting "No pages found matching the query" exception. Below is the snippet:

String queryStr =  "select * from [cq:PageContent] where ISDESCENDANTNODE('"+ path + "') AND [" + customProperty + "] IS NOT NULL";

try {

    Query query =  resourceResolver.adaptTo(Session.class).getWorkspace().getQueryManager().createQuery(queryStr, Query.JCR_SQL2);

    TagManager tagManager = resourceResolver.adaptTo(TagManager.class);

    QueryResult results = query.execute();

    NodeIterator resultsNodeIterator = results.getNodes();

    if (resultsNodeIterator.getSize() > 0) {

        while (resultsNodeIterator.hasNext()) {

            Node pageContentNode = resultsNodeIterator.nextNode();

            migrateTags(pageContentNode, tagManager);

        }

    } else {

        logger.info("No pages found matching the query {}", queryStr);

    }

} catch (RepositoryException e) {

    logger.error("Error while running query {}", queryStr);

}

I have tried getting resource from request (logged in as admin) and also used system user with both read and write access. But none of them worked.

One observation is the script only works when there are less than 100 pages in the results (QueryResult results = query.execute();) and when there are more results, ex: 400 pages, it directly falls into the else block.

I was seeing "consider creating an index or changing the query" in the error.log, so I updated the oak:indexes by adding my property to "/oak:index/cqPageLucene/indexRules/cq:Page/properties" and also tried my custom oak:index here "/oak:index/custom/indexRules/cq:PageContent/properties". I was seeing indexing was complete

"

*INFO* [async-index-update-async] org.apache.jackrabbit.oak.plugins.index.IndexUpdate Reindexing completed

*INFO* [async-index-update-async] org.apache.jackrabbit.oak.plugins.index.AsyncIndexUpdate [async] Reindexing completed for indexes: [/oak:index/custom*(0)] in 2.207 s"

And when I try to hit the servlet, I dont see "consider creating an index or changing the query" exception anymore, but I get the same exception from the script. I ran out of options, I am not sure whats missing and confused why the query will work on crx/de > tools and not in my script.

Thanks in advance for any suggestions.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

999

Correct Answer

829

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

999

Correct Answer

829
Arun_Patidar
MVP

21-11-2019

To run SQL2, you need to get the session from the repository not from the resource resolver.

example -

aem63app-repo/SimpleSQL2SearchServlet.java at master · arunpatidar02/aem63app-repo · GitHub

Answers (1)

Answers (1)

Avatar

Avatar

scottm91515441

Avatar

scottm91515441

scottm91515441

20-11-2019

check experience league