SQL2 query PDF content

Question

What's an example of SQL-2 query for content of a PDF file that was uploaded to DAM?I've checked all nodes and properties but cannot find property with file's contentThanks in advance

smacdonald2008 · Accepted Answer

If you really want to use JCR SQL to search AEM DAM, then read this community article:

http://cq-ops.tumblr.com/post/23544023402/how-to-query-cqs-jcr-with-sql-or-xpath

There is a JCR SQL builder tool that you can use. I just tested it and it works:

[img]JCR SQL Builder.png[/img]

The string that tool provides was:

select * from nt:base where jcr:path like '/content/dam/car/%' and contains(*, '%.pdf') order by jcr:score desc

You can test your queries there. Then get them into your Java JCR API App:

String url = "localhost:4502" ;

String aemUrl = "http://"+url +"/crx/server" ;
//Create a connection to the CQ repository running on local host
Repository repository = JcrUtils.getRepository(aemUrl);

//Allocate memory to the List
//Create a Session
javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()));

//Obtain the query manager for the session ...
javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();

String sqlStatement = "<Insert query String>";

javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");

//Execute the query and get the results ...
javax.jcr.query.QueryResult result = query.execute();

//Iterate over the nodes in the results ...
javax.jcr.NodeIterator nodeIter = result.getNodes();

int u = 0;
while ( nodeIter.hasNext() ) {

//Do Something with the Result Set!

}

smacdonald2008 · Answer

When working with AEM and querying content from the JCR, it is recommended that you use the QueryBuilder API to retrieve data. We have an article that shows you how to retrieve data from the AEM DAM using the QueryBuilder API. See:

http://helpx.adobe.com/experience-manager/using/downloading-dam-assets.html

To get file content - you use this code:

//Convert the HIT to an asset - each asset will be placed into a ZIP for downloading
String path = hit.getPath();
Resource rs = resourceResolver.getResource(path);
Asset asset = rs.adaptTo(Asset.class);

//We have the File Name and the inputstream
InputStream data = asset.getOriginal().getStream();

In this article, we retrieve graphic files. For your use case, change the query parameters to search for PDF.

Sign up

Login with SSO

Login to the community

Login with SSO