Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

AEM 6.4.8.1 - SQL2 request with no result

Avatar

Avatar
Level 1
JimmyC148
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile

Avatar
Level 1
JimmyC148
Level 1

Likes

0 likes

Total Posts

1 post

Correct Reply

0 solutions
View profile
JimmyC148
Level 1

04-09-2020

Hello everybody,

 

We currently got an issue in our instance of AEM. We are currently on AEM 6.4.8.1.


Today to get some data we make a request in our code :

SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE('/etc/commerce/nicephore/catalog/MSegment') AND [cnp:type] = 'Parfums'

When we execute the query through the crx we got some results, it's okay. However today when we execute the query through the code the result is 0, we don't have any result.

We have currently an index for the property cnp:type and we already reindex it but it didn't change anything. We already checked the acl and right and this is not the issue. We don't understand why the same executed query (in crx and in our code) send us different result. We don't have any logs when the query is called.

Here is the code we use to make the query :

Session session = resourceResolver.adaptTo(Session.class);
queryManager = session.getWorkspace().getQueryManager();
StringBuilder queryStrLigne = new StringBuilder("SELECT * FROM [nt:base] WHERE ISDESCENDANTNODE('" + COMMERCE_SEGMENT + "') AND [cnp:type] = 'Parfums'");
Query queryAsset = queryManager.createQuery(queryStrLigne.toString(), Query.JCR_SQL2);
QueryResult result = queryAsset.execute();

 

What could cause this issue ? If you have any idea it would help a lot.
Thank you.

AEM 6.4.8 SQL2

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Affirm 50
MVP
Vaibhavi
MVP

Likes

195 likes

Total Posts

166 posts

Correct Reply

54 solutions
Top badges earned
Affirm 50
Validate 1
Ignite 1
Give Back 5
Give Back 3
View profile

Avatar
Affirm 50
MVP
Vaibhavi
MVP

Likes

195 likes

Total Posts

166 posts

Correct Reply

54 solutions
Top badges earned
Affirm 50
Validate 1
Ignite 1
Give Back 5
Give Back 3
View profile
Vaibhavi
MVP

04-09-2020

Hi @JimmyC148 , 

This seems like user permission issue. 

When you execute the query directly , you must be logged in as admin user or user with permission to query the specific path which you have mentioned. 

 

When you are trying the same query via program ,  access/permission for the user seems like limited which encountered 0 result. 

If you are using the service user please have a look if the user has required permission for the mentioned path. 

Answers (3)

Answers (3)

Avatar

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,282 likes

Total Posts

3,156 posts

Correct Reply

891 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,282 likes

Total Posts

3,156 posts

Correct Reply

891 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile
Arun_Patidar
MVP

05-09-2020

You need to get a session using SlingRespository, it will not work with just adapting from resource Resolver.

Example

https://github.com/arunpatidar02/aem63app-repo/blob/master/java/SimpleSQL2SearchServlet.java

Avatar

Avatar
Boost 1
Level 1
joshdboyle
Level 1

Like

1 like

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
joshdboyle
Level 1

Like

1 like

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
View profile
joshdboyle
Level 1

04-09-2020

A couple things:

  1. I highly recommend not using SQL2 directly like this and instead using QueryBuilder.  In your case, the path and property predicates can be used as follows:
    1. path=/etc/commerce/nicephore/catalog/MSegment
    2. property=cnp:type
    3. property.value=Parfums
  2. As a sanity check, install ACS Tools on your instance and force a reindex of the index you mentioned to rule out the index being the issue:  https://adobe-consulting-services.github.io/acs-aem-commons/features/ensure-oak-index/index.html.  Note that you'll need to install a version compatible 6.4.8 that has the Ensure Oak Index feature as they removed this feature from versions supported AEM as a Cloud Service.

Note that QueryBuilder uses the faster of XPath or SQL under the hood anyway but QueryBuilder use is the best practice for querying in AEM.

Avatar

Avatar
Give Back 50
MVP
Vijayalakshmi_S
MVP

Likes

452 likes

Total Posts

590 posts

Correct Reply

197 solutions
Top badges earned
Give Back 50
Give Back 5
Ignite 10
Ignite 5
Ignite 3
View profile

Avatar
Give Back 50
MVP
Vijayalakshmi_S
MVP

Likes

452 likes

Total Posts

590 posts

Correct Reply

197 solutions
Top badges earned
Give Back 50
Give Back 5
Ignite 10
Ignite 5
Ignite 3
View profile
Vijayalakshmi_S
MVP

04-09-2020

Hi @JimmyC148,

When you execute the query from CRX, it should have been in admin login or any user who has access to the path which we are querying. (In this case, /etc/commerce/...)

In the code snippet you have shared, can you let know details on how you are retrieving the resourceResolver object. (first line in the snippet)

If you are using Service User then check if the service user is available in the instance with desired permissions.