Groovy script javax.jcr.query.InvalidQueryException

[ashwinka]

when i am running following query on crx/de it is working fine , but when i run the same using a groovy script im getting a exception.

Query which worked fine on crx/de :

SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE("/content/mm/mo") AND NAME() = "CritisismOnMentalismTheory"

Groovy script which is raising exception :

import javax.jcr.Node;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import javax.jcr.Session;
queryManager = session.workspace.queryManager;

activeList = ["CritisismOnMentalismTheory","bjp-sena-ncp-alliance-shines-in-maharashtra-rural-polls"]

for(nodename in activeList) {
 try{
 def statement = "SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE(\"/content/mm/mo\") AND NAME() = \""+nodename+"\"";
 query = queryManager.createQuery(statement, 'sql');
 pageList = query.execute();

 Node jcrContent
 pageList.nodes.each { currNode ->
 if(currNode != null) {
 jcrContent=resourceResolver.resolve(currNode.path+"/jcr:content").adaptTo(Node.class);
 println "pagepath"+currNode.path;
 }

 }
 }catch(Exception g ){ 

 println "Exception while getting getArticlePaths " + g + ""; 

 }
}

and exception is :

Exception while getting getArticlePaths javax.jcr.query.InvalidQueryException: java.text.ParseException: Query: SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE("/content/mm/mo") AND NAME() = "CritisismOnMentalismTheory(*)"; expected: static operand
Exception while getting getArticlePaths javax.jcr.query.InvalidQueryException: java.text.ParseException: Query: SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE("/content/mm/mo") AND NAME() = "bjp-sena-ncp-alliance-shines-in-maharashtra-rural-polls(*)"; expected: static operand

can see an extra (*) is appending on the string

[arunpatidar · Accepted solution]

HI @ashwinka 
The query is not compatible with SQL but works for SQL2.

You are trying two different type of queries in crx and groovy.

Please convert in SQL and then try or use SQL2 in Groovy
query = queryManager.createQuery(statement, 'JCR-SQL2'); 

Supported languages are : 

[JCR-SQL2, JCR-SQL2-noLiterals, sql, sql-noLiterals, xpath, xpath-noLiterals, JCR-JQOM, JCR-JQOM]

[Raja_Reddy]

Hi @ashwinka 

You can try modifying the query statement to remove the (*) from the end of the NAME() function. Here's an updated version of the query statement:

def statement = "SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE('/content/mm/mo') AND NAME() = '"+nodename+"'";

This should remove the extra (*) from the query and allow it to execute successfully.
Thanks.