Search code examples
sqldatabasedqldocumentumdfc

DQL query to return all files in a Cabinet in Documentum?

I want to retrieve all the files from a cabinet (called 'Wombat Insurance Co'). Currently I am using this DQL query:

select r_object_id, object_name from dm_document(all) 
where folder('/Wombat Insurance Co', descend);

This is ok except it only returns a maximum of 100 results. If there are 5000 files in the cabinet I want to get all 5000 results. Is there a way to use pagination to get all the results?

I have tried this query:

select r_object_id, object_name from dm_document(all) 
where folder('/Wombat Insurance Co', descend) 
ENABLE (RETURN_RANGE 0 100 'r_object_id DESC');

with the intention of getting results in 100 file increments, but this query gives me an error when I try to execute it. The error says this:

com.emc.documentum.fs.services.core.CoreServiceException: "QUERY" action failed.

java.lang.Exception: [DM_QUERY2_E_UNRECOGNIZED_HINT]error:  
"RETURN_RANGE is an unknown hint or is being used incorrectly."

I think I am using the RETURN_RANGE hint correctly, but maybe I'm not. Any help would be appreciated!

I have also tried using the hint ENABLE(FETCH_ALL_RESULTS 0) but this still only returns a maximum of 100 results.

To clarify, my question is: how can I get all the files from a cabinet?

Solution

  • Aha, I've figured it out. Using DFS with Java (an abstraction layer on top of DFC) you can set the starting index for query results: 

    String queryStr = "select r_object_id, object_name from dm_document(all) 
                   where folder('/Wombat Insurance Co', descend);"

PassthroughQuery query = new PassthroughQuery();
query.setQueryString(queryStr);
query.addRepository(repositoryStr);

QueryExecution queryEx = new QueryExecution();
queryEx.setCacheStrategyType(CacheStrategyType.DEFAULT_CACHE_STRATEGY);
queryEx.setStartingIndex(currentIndex);      // set start index here

OperationOptions operationOptions = null;

// will return 100 results starting from currentIndex
QueryResult queryResult = queryService.execute(query, queryEx, operationOptions);

    You can just increment the currentIndex variable to get all results.