Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

SQL 2 query

Avatar

Level 3

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.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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



Arun Patidar

View solution in original post

2 Replies

Avatar

Correct answer by
Community Advisor

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



Arun Patidar