Query support

OASIS CMIS specification

The OASIS CMIS specification provides a type-based query service to search for objects on the repository.

For more information, see section 2.1.10 Query of the OASIS CMIS specification.

IBM CMIS implementation

IBM CMIS for FileNet Content Manager supports the OASIS CMIS query specifications with the following exceptions and limitations.

Character escaping in LIKE strings is not supported

The OASIS CMIS specification requires repositories to support the backslash (\) as an escape character in query statements. One of the requirements is that in a LIKE string, \% represents % and \_ represents _.

However, IBM CMIS for FileNet Content Manager does not support querying on the percent sign (%) or the underscore (_) characters in a LIKE string because the Content Engine APIs do not support ESCAPE clauses. If a query includes an escaped percent sign (\%) or an escaped underscore (\_), IBM CMIS for FileNet Content Manager converts the characters to an underscore, which is a single-character wildcard.

Converting escaped percent signs and escaped underscores to a single-character wildcard character can result in more search results than expected.

For example, you have three documents in your repository named document_1, document_2, and document11. You use the query SELECT * FROM Document WHERE DocumentTitle LIKE 'document\_%' to search for all documents that have a title that starts with document_. The expected result is that document_1 and document_2 are returned by the query.

However, when you submit the query, IBM CMIS for FileNet Content Manager converts the query to (SELECT * FROM Document WHERE DocumentTitle LIKE 'document_%'), so that IBM FileNet P8 can parse the query. This query returns all three documents.

Tip: You can use percent signs and underscores as wildcard characters in queries that include equality conditions because equality conditions do not require escape characters. For example, you can use the query SELECT * FROM Document WHERE DocumentTitle = 'document_1' OR DocumentTitle = 'document_2' to return document_1 and document_2.

Parentheses are required for nested JOIN clauses

The OASIS CMIS specification defines the following syntax for a query that contains nested JOIN clauses:

<joined table> ::= "(" <joined table> ")"
			| <table reference> [ <join type> ] JOIN <table reference>
			<join specification>

In the OASIS CMIS specification, the parentheses around the nested <joined table> are optional. However, IBM CMIS for FileNet Content Manager requires parentheses around the nested <joined table>.

For example, if you submit the following query, IBM FileNet P8 returns a parsing error on the INNER JOIN clause:

SELECT d.* FROM  cmis:document d INNER JOIN cmis:folder f ON
d.cmis:createdBy = f.cmis:createdBy INNER JOIN EntryTemplate e ON

d.cmis:lastModifiedBy = e.cmis:lastModifiedBy WHERE
e.cmis:createdBy<>'admin'

To run this query in IBM CMIS for FileNet Content Manager, you must use the following syntax:

SELECT d.* FROM  (cmis:document d INNER JOIN cmis:folder f ON
d.cmis:createdBy = f.cmis:createdBy) INNER JOIN EntryTemplate e ON

d.cmis:lastModifiedBy = e.cmis:lastModifiedBy WHERE
e.cmis:createdBy<>'admin'

Nested JOIN clauses can appear on only one side of the JOIN keyword

The OASIS CMIS specification defines the following syntax for a query that contains nested JOIN clauses on both sides of the JOIN keyword:

<table reference> ::= <table name> [ [ AS ] <correlation name> ]
			| <joined table>
<joined table> ::= "(" <joined table> ")"
			| <table reference> [ <join type> ] JOIN <table reference>
			<join specification>

In the OASIS CMIS specification, you can have nested JOIN clauses on either side of the JOIN keyword. However, IBM CMIS for FileNet Content Manager supports nested JOIN clauses on either the left side or the right side of the JOIN keyword, but not on both sides of the JOIN keyword.

For example, if you submit the following query, IBM FileNet P8 returns a parsing error because there is a nested JOIN clause on each side of the JOIN keyword:

SELECT d.* FROM  (cmis:document d INNER JOIN cmis:folder f ON
d.cmis:createdBy = f.cmis:createdBy) INNER JOIN  (EntryTemplate
e INNER JOIN Email e2 ON e.cmis:createdBy = e2.cmis:createdBy)
ON d.cmis:lastModifiedBy = e.cmis:lastModifiedBy WHERE
e.cmis:createdBy<>'admin'

To run this query in IBM CMIS for FileNet Content Manager, you must remove the nested JOIN clause on one side of the JOIN key word. The following query has a nested JOIN clause on the right side of the JOIN keyword:

SELECT d.* FROM EntryTemplate e INNER JOIN (cmis:document d
INNER JOIN cmis:folder f ON d.cmis:createdBy = f.cmis:createdBy) ON

d.cmis:lastModifiedBy = e.cmis:lastModifiedBy WHERE
e.cmis:createdBy<>'admin'