SQL2 query PDF content

[Stanleyor]

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 content

Thanks in advance

[smacdonald2008]

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.

[smacdonald2008]

You can also read this communty article when using the QueryBuilder.

http://cq-ops.tumblr.com/post/23543240500/how-to-use-cqs-query-debugger-tool

It talks about using the QueryBuilder tool that helps you build your queries. 

[smacdonald2008 · Accepted solution]

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! 

}