JCR-SQL2 query return no results in OSGI but returns results in crxde query tool.

[Keerthana_H_N]

Hi,

String sqlQuery = "SELECT * FROM [dam:Asset] AS asset " +
        "WHERE ISDESCENDANTNODE(asset, '/content/dam') " +
        "AND asset.[jcr:path] NOT LIKE '/content/dam/archive/%' " +
        "AND asset.[jcr:content/metadata/productdata/samsExpirationDate] IS NOT NULL " +
        "AND asset.[jcr:content/metadata/productdata/samsExpirationDate] < '" + currentDateStr + "'";

QueryManager queryManager = session.getWorkspace().getQueryManager();
Query query = queryManager.createQuery(sqlQuery, Query.JCR_SQL2);
query.setLimit(limit);
query.setOffset(0);
QueryResult result = query.execute();

query is working in local but not in dev cloud. Working in dev crxde as well. I have given the read, write permission for the given path. updated the index as well. Still the query is not getting results. Please let me know what might be the issue.

Thank you.

[Uppari_Ramesh]

@Keerthana_H_N Could you explain the issue a little more? query is working in local but not in dev cloud means do you see any error or you are getting results for same query?

[Keerthana_H_N]

Query is failing to give results in dev cloud. There's no error just query results are 0. And same query is working when deployed to local. When I just run the query in dev crxde tools able to get the result. Via code trying to run failed get the results.

[arunpatidar]

Hi @Keerthana_H_N 
How did you get the session? Is it a subservice session or user session? If subservice session then how and How did you set the permission for service user?

[Keerthana_H_N]

get seesion from service user and in the service user I have provided the read and write permission for /content/dam folder

[arunpatidar]

Hi @Keerthana_H_N 

Could you please check logs. If it is working on local but not in dev with same code then issue could be with permission or subservice configuration.

[giuseppebag]

Check out this helpful tutorial to ensure everything is in the right place: https://experienceleague.adobe.com/en/docs/experience-manager-learn/cloud-service/developing/advance... 

[kautuk_sahni]

@Keerthana_H_N just checking in! Were you able to get this resolved? If one of the replies above helped—whether it completely solved the issue or simply pointed you in the right direction—marking it as accepted can make it much easier for others with the same question to find a solution. And if you found a different way to fix it, sharing your approach would be a great contribution to the community. Your follow-up not only helps close the loop but also ensures others benefit from your experience. Thanks so much for being part of the conversation!