AEM-6.4 SQL2 query is not working from backend

Avatar

Avatar

vijays80591732

Avatar

vijays80591732

vijays80591732

03-05-2019

Hi All,

I'm using an sql2 query to fetch all the home pages from different locales based on sling:resourceType and my query is below. I'm using system user to get the resolver. FYI.

SELECT * FROM [cq:Page] AS s WHERE s.[jcr:content/sling:resourceType] = 'sample/components/structure/pageHome' AND ISDESCENDANTNODE('/content/sample')

The above query is working fine in my local . But in Development environment I'm unable to fetch the home pages using this query from backend, whereas the same query I tried with crx/de query search where I'm able to fetch the homepages.  I tried the below things but no luck,

Checked the system user permissions in user admin (which I used to get the resolver) and I'm having read, modify, delete, readACL, editACL for all .

Checked the backend logs. After executing the above query from backend I'm getting zero results. So, suspecting it should be permission issue. Can anyone help me to fix this?

Arun Patidarsmacdonald2008Ratna Kumar

Regards,

Vijay

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

vijays80591732

Avatar

vijays80591732

vijays80591732

07-05-2019

Hi All,

Thank you very much for your inputs. The has been fixed and it was due to the permission issue which I given for service user.

Regards,

Vijay

Answers (11)

Answers (11)

Avatar

Avatar

Ankur_Khare

MVP

Avatar

Ankur_Khare

MVP

Ankur_Khare
MVP

09-10-2019

Check the system user permission..

Avatar

Avatar

satishc37349908

Avatar

satishc37349908

satishc37349908

09-10-2019

Can you please guide me. I am facing the same issue.

Avatar

Avatar

Gaurav-Behl

MVP

Avatar

Gaurav-Behl

MVP

Gaurav-Behl
MVP

06-05-2019

Could you share the service user mapping xml/screenshot?

Is the code-bundle where this query is written, given appropriate permissions via service user mapping?

Does that service user has read (or appropriate) access on the folder path where your query runs (/content/sample)?

Could you enable logging on your package/query to see the error in logs?

Avatar

Avatar

berliant

Employee

Avatar

berliant

Employee

berliant
Employee

06-05-2019

Try to use AEM Chrome plugin: AEM Chrome Plug-in - Chrome Web Store,

It will allow you to see teh exact query that your code executes, determine if it's traversal and hence you need to work with indexes and test the query output from AEM Chrome Plugin in AEM Explain Query tool.

Avatar

Avatar

vijays80591732

Avatar

vijays80591732

vijays80591732

06-05-2019

Hi Arun,

The state of the bundle is in active and the service is also in active. That's why I could able to see the logs which I put in that service.

If the issue with archtype or dependencies then it should not work in local as well right? but for me it is working in local. Issue is only wth the dev environment.

Regards,

Vijay

Avatar

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838
Arun_Patidar
MVP

06-05-2019

Can you check the the state of bundle of new project and service/servlet state also.

could be issue with archtype or dependencies.

Avatar

Avatar

vijays80591732

Avatar

vijays80591732

vijays80591732

06-05-2019

Hi Arun Patidar​,

I have changed the code as you suggested (using repository session). But still I'm facing the same issue. The updated code is working fine in my local instance but not in dev. Also I' having some existing projects in dev environment where I'm using the same logic (get all locale based home pages) and the same code is working for those projects. But the same code is not working in fresh/new project. In logs I could see the sql2 query which got executed and it return zero results whereas the same query I tried with crx/de query search and I'm getting the proper results..FYI. I checked with Apache Sling Service User Mapper Service Amendment and I'm having proper mappings as well. Any other clue on this?

The query is : SELECT * FROM [cq:Page] AS s WHERE s.[jcr:content/sling:resourceType] = 'sample/components/structure/pageHome' AND ISDESCENDANTNODE('/content/sample')

I believe there is no issue in the query

Regards,

Vijay

Avatar

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838
Arun_Patidar
MVP

04-05-2019

Avatar

Avatar

Ravi_Pampana

MVP

Avatar

Ravi_Pampana

MVP

Ravi_Pampana
MVP

03-05-2019

Can you try your query in crx/de, so that you can see whether results are coming with your query. It will help to understand if there any issues with the query or values passed with the query

Screen Shot 2019-05-03 at 10.42.03 AM.png

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

03-05-2019

Are you seeing any error message - if everything is setup the same way - it will work.

Avatar

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838

Avatar

Arun_Patidar

MVP

Total Posts

2.9K

Likes

1.0K

Correct Reply

838
Arun_Patidar
MVP

03-05-2019

Hi,

How did you get the session, the session should be repository session.

@Reference
private SlingRepository repository;

session = repository.loginService("readService",null);

final Query query=session.getWorkspace().getQueryManager().createQuery("yourquery",Query.JCR_SQL2);