The FileNet P8 Platform supports a query syntax that conforms generally to SQL-92 and specifically is aligned with SQL Server query syntax wherever there is equivalence of function, with classes and properties playing the role of tables and columns. This section:
The statements and descriptions in this topic follow these conventions:
Convention | Description | |
---|---|---|
UPPER CASE bold type | Keyword tokens. For example, SELECT. | |
Single quotes |
' |
Surround symbols forming part of the grammar. For example, '*'. |
Square brackets |
[ ] |
Surrounding a token or series of tokens indicates an optional clause. For example, [ORDER BY <orderby>]. (Also, see the Note below about using square brackets with SQL reserved words.) |
Brackets |
{ } |
Surrounding a token or series of tokens indicates an optional clause that may be repeated more than once. |
Single vertical lines |
| |
Separate alternatives. For example, AND | OR. |
Parentheses without accompanying quotes |
( ) |
Determine precedence. For example, (AND | OR). |
Italic type |
<italic> |
Indicates production rules whose definitions are well known. For example <integer>. |
Keyword matching is case-insensitive, even though keywords appear in upper case (see examples below).
Note If a class has a property of the
same name as a reserved SQL word, and you want to search for that property, you should surround the
property name in your SELECT
statement with brackets ([ ]) to prevent errors. (For a list of
reserved words, refer to your SQL documentation.) For example, if your Document class has a property
named "From", which is a reserved word in SQL, structure your SELECT
statement
in the following manner:
SELECT d.[DocumentTitle], d.[From], d.[Id] FROM Document d WHERE d.[Creator] = "jsmith"
This section presents the SQL statement grammar. In some cases, a link is provided to information in this document that is relevant to the use of a category or keyword.
<query> ::= <SQL query> [ OPTIONS '(' <option> { ',' <option> } ')' ]
<option> ::= ( BATCHSIZE | TIMELIMIT | FULLTEXTROWLIMIT) <integer>
<SQL query> ::= SELECT [DISTINCT | ALL] [TOP<integer>] <select_list> FROM <class_reference> [WHERE <search_condition>] [ORDER BY <orderby> { ',' <orderby> } ]
<select_list> ::= <select_prop> { ',' <select_prop>
}
<select_prop> ::= (<property_spec> [ [AS] <entity_name> ] ) | ( <class_alias> '.' '*' ) | '*'
<class_reference> ::= <from_class> | <qualified_join>
<qualified_join> ::= <joined_class> ( INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] ) JOIN <from_class> ON <comparison_predicate>
<joined_class> ::= <from_class> | '(' <qualified_join> ')'
<from_class> ::= <class_ident> [ [AS] <class_alias> ] [ WITH (INCLUDESUBCLASSES | EXCLUDESUBCLASSES )]
<search_condition> ::= <basic_search_condition> [ ( AND | OR ) <search_condition> ]
<basic_search_condition > ::= ( [NOT] '(' <search_condition> ')' ) | <predicate>
<predicate> ::= <comparison_predicate> | <null_test> | <in_test> | <existence_test> | <isclass_test> | <content_test>
<comparison_predicate> ::= <scalar_exp> <comparison_op> <scalar_exp>
<comparison_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | <infolder_op> | LIKE
<infolder_op> ::= INFOLDER | INSUBFOLDER
<null_test> ::= <property_exp> IS [ NOT ] NULL
<in_test> ::= <scalar_exp> IN ( ( '(' <SQL query> | <literal_list> ) ')' ) | <property_spec> )
<existence_test> ::= EXISTS '(' <SQL query> ')'
<isclass_test> ::= ISCLASS '(' <class_alias> ',' <class_ident> ')'
<content_test> ::= ( CONTAINS | FREETEXT ) '('
<property_spec>',' <string_literal> ')'
<scalar_exp> ::= <literal> | <property_exp> | ( '(' <scalar_exp> ')' ) | ( <scalar_exp> <arith_op> <scalar_exp> )
<arith_op> ::= '+' | '-' | '*' | '/'
<property_exp> ::= <property_spec> | ( ( UPPER | LOWER | ABS ) '(' <scalar_exp> ')' ) | <object_exp>
<object_exp> ::= OBJECT'(' ( <guid> | <string_literal> | <property_spec>) ')'
<orderby> ::= <property_spec> [ ASC | DESC ]
<class_ident> ::= <guid> | <entity_name>
<class_alias> ::= <integer> | <entity_name>
<property_spec> ::= ( <class_alias> '.' <guid> ) | ( [ <class_alias> '.' ] <entity_name> )
<entity_name> ::= <simple_name> | <complex_name>
<simple_name> ::= <alpha> { <alpha> | <digit> }
<complex_name> ::= ('"' { <any_character> } '"' ) | ( '[' { <any_character> } ']' )
<literal_list> ::= <literal> { ',' <literal> }
<literal> ::= <string_literal> | <integer> | <float> | <ISO datetime> | <W3C datetime> | TRUE | FALSE | UNKNOWN | <guid>
<string_literal> ::= <single_quote> {<any_character>} <single_quote>
Note Include a single quote as part of the literal value by using two consecutive single quotes. For example, 'document''1234'.
<ISO datetime> ::= YYYYMMDDThhmmss[,ffff]Z
<W3C datetime> ::= YYYY-MM-DD[Thh:mm:ss[.ffff]][<timezone>]
Note If you omit the time portion (Thh:mm:ss), 00:00:00 is assumed. If you omit the <timezone> option, Z is assumed, which is Coordinated Universal Time (UTC).
<timezone> ::= Z | ( ( '+' | '-') hh:mm )
<guid> ::= [<single_quote>] '{'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'}' [<single_quote>]
The <class_ident> and <property_spec> production rules (in the statements above) include a complexity in the way IBM FileNet P8 allows class and property identifiers for queries. The <class_ident> and <property_spec> production rules can be any one of the following:
A <class_ident> can specify only a searchable class. A searchable class is identified by SearchScope.fetchSearchableClassDescriptions
. In general, searchable classes are classes that reside in an object store repository (implement the RepositoryObject
interface), and are subclasses of IndependentObject
.
SELECT
list may be identified by GUID, symbolic
name, or display name. The class qualifier may be omitted if the property name or GUID can be resolved uniquely.SELECT
statement may be given a string
alias (<select_prop>) that may be used
elsewhere in the query in place of a GUID or symbolic/display name. Aside
from this, the same rules apply everywhere that a property identifier appears. SELECT
list (<select_prop>) can be any property defined in the class or classes in the FROM clause. No properties in top level select lists are restricted from being selected. Note that this is a change from prior releases where many properties were not selectable.SELECT
(a SELECT
statement inside an IN clause or EXISTS clause) within the SELECT
list must be a searchable property. Refer to the property definitions for the properties identified as "Searchable". All user-defined properties are searchable, except properties with a cardinality of enum or a datatype of binary. The INFOLDER operator matches an object contained within a specified folder. The left-hand operand specifies an object-valued property or an expression that results in a single object value. The right-hand operand identifies the folder in one of the following forms:
If the left-hand operand yields an object of class Folder, and the folder is a direct child of the folder identified by the right-hand operand, the expression evaluates to TRUE.
If the left-hand operand yields an object of another containable class (Document
,
CustomObject
), the expression evaluates to true if that object is a referential
containee of the folder identified by the right-hand operand.
The following example locates all documents contained in the /sub1/sub1a folder:
SELECT … FROM Document WHERE Document.This INFOLDER '/sub1/sub1a'
The INSUBFOLDER operator works in a similar fashion for matching objects contained within a folder subtree (a specified folder or any subfolder in that tree, to any depth). The operands for the INSUBFOLDER operator are the same as for INFOLDER.
The following example locates all documents contained in the /sub1/sub1a folder or any subfolder:
SELECT … FROM Document WHERE Document.This INSUBFOLDER '/sub1/sub1a'
INSUBFOLDER can be used only on subfolders that have the Folder.Parent
property set to the parent of the folder (direct containment). the INSUBFOLDER operator will not recognize a subfolder linked to a parent by referential containment (a ReferentialContainmentRelationship
object having the ReferentialContainmentRelationship.Tail
property equal to the subfolder and the ReferentialContainmentRelationship.Head
property equal to the parent folder).
Note Use of the INSUBFOLDER operator results in a complex query that can take a long time to execute. Before deploying a solution that uses an INSUBFOLDER search across a large number of folders and containees, be sure to check that this search behaves well on a large database, and is properly optimized. The database is not capable of properly optimizing all queries.
The IsClass function acts as a filter for finding specific classes. The IsClass function is useful when querying base classes WITH INCLUDESUBCLASSES and the results must include objects that belong to one or more particular subclasses of the base class.
The first parameter specifies the alias of a FROM class to which the filter is applied and the second specifies the class which it must match.
The following example locates documents of either DocSubclass1 or DocSubclass2,
but excludes those of class Document
or any other subclass:
SELECT … FROM Document D WHERE IsClass(D,DocSubclass1) OR IsClass(D,DocSubclass2)
The INCLUDESUBCLASSES and EXCLUDESUBCLASSES operators, along with IsClass, are used to limit results to specific classes or class hierarchies. INCLUDESUBCLASSES is the default. SQL query examples are shown below.
Both of these queries find documents and their subclasses with the DocumentTitle "MyDoc":
SELECT … FROM Document WHERE DocumentTitle = 'MyDoc' SELECT … FROM Document WITH INCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc'
Both of the next two queries find documents with the DocumentTitle "MyDoc". Matches in subclasses of the Document
class are not returned:
SELECT … FROM Document d WHERE DocumentTitle = 'MyDoc' AND ISCLASS (d, Document) SELECT … FROM Document WITH EXCLUDESUBCLASSES WHERE DocumentTitle = 'MyDoc'
With the following query, we want to find 'Form Data' and 'Form Policy' objects (which are both subclasses ofo Document
):
SELECT … FROM Document d WHERE … AND (ISCLASS (d, [Form Data]) OR ISCLASS (d, [Form Policy]))
We can use the next query to find objects from all subclasses of Document
except the Document
class itself. (Remember that INCLUDESUBCLASSES is used by default.)
SELECT … FROM Document d WHERE … AND NOT (ISCLASS (d, Document))
The OBJECT function provides a means to create the effect of an object constant, which can then be compared to an object-valued property or expression. There are two logical forms of the OBJECT function, one of which takes an argument of type GUID (Id), which can either be a constant or a GUID-valued property, and the other which takes a string argument, which can be one of the following:
Folder
objects, the full path name of the folder. For example, the following statement locates all immediate subfolders of the /root/sub1/sub1a folder:
SELECT … FROM Folder WHERE Parent = OBJECT('/root/sub1/sub1a')
When <comparison_predicate> is used to compare two object-valued properties, the IDs of the objects, rather than the contents, are actually being compared. For this reason, comparing objects is just as fast as comparing IDs.
Using the LIKE operator, you can perform pattern-matching queries (also called wildcard queries) on single- and multi-valued string properties. The following example shows a wildcard query on a string property:
WHERE DocumentTitle LIKE 'hello%'
You can also perform wildcard queries on multi-valued string properties, as shown in the following example:
WHERE mv_string_prop LIKE 'he%' WHERE mv_string_prop has cardinality of LIST
Using multi-valued properties as the left-hand operand for other query operations is illegal. The following example would result in the query failing:
WHERE mv string prop = 'abc'
The following formats are used for the IN operator:
Where "value" is either a property name or a constant (<property_spec> | <literal>), and listproperty is a property name (<property_spec>) of a property that has cardinality of LIST. This format is used to search a property that is of cardinality LIST. If the list has a value equal to "value", the expression is true. Note that you cannot use "value = listproperty" to compare a property of type LIST to a value; you must use the IN operator.
Where constant1, constant2, constant3 are constants (<literal>s). This format is used to determine if "value" is equal to one of the constants in the list. Note that when multiple items are between the parentheses, the items must be constants.
This format is used to check if "value" is one of the results returned by the subquery. Note that the subquery must select only one property.
When performing comparison queries on float-valued properties, you must use a decimal point in the value. The following examples use the Document.ContentSize
, a float-valued property, to illustrate incorrect and correct syntax for a comparison query.
This query will produce an error:
SELECT … FROM Document WHERE ContentSize > 3
The correct form for this query is:
SELECT … FROM Document WHERE ContentSize > 3.0
A JOIN is used to query the result from multiple tables, and behave the same as ANSI/ISO SQL99 compliant joins. The syntax is slightly more restrictive, however, and requires parenthesis if multiple joins are used. For example, the following is a statement using two joins:
SELECT … FROM (class1 INNER JOIN class2 ON class1.x = class2.y) INNER JOIN class3 ON class3.x = class1.y
Note the parentheses around "class1 INNER JOIN class2 ON class1.x = class2.y". Additionally, the ON clause must immediately follow the class of the JOIN, and precede the next JOIN clause. This is more restrictive than ANSI/ISO SQL99.
See also Full-Text Joins.
Full-text queries (also called Content-Based Retrieval, or CBR queries) use the CONTAINS and FREETEXT functions to filter text. These functions allow searching for a word or phrase in document or annotation content, or in string properties.
Only one CONTAINS or FREETEXT clause is allowed per search statement.
Full-text queries can take a considerable amount of time to execute. Some queries can finish in a few seconds, while others could potentially run for hours. Your applications should be written to allow the user to set a timeout; a single default value is probably not sufficient. The user setting(s) should ensure that either the query does not run longer than desired, or that the timeout value is high enough to enable the query to finish execution. Note that the timeout value is the time required to fetch a page for a continuable query, not the time to fetch all pages for the query.
The full-text index contains one row for each content element that is indexable, plus one more row for all CBR-enabled strings of an object. These rows can be returned for any content search. Since a single full-text query can find matches on multiple content elements or properties of a single Content Engine document, multiple rows for a single document can be returned from a search.
Since the Verity AND operator requires that the operands of the AND be in the same row, searching for ”word1 AND word2”, where “word1” is in a content element and “word2” is in the properties, will not find a match. Likewise, if “word1” is in content element 1 and “word2” is in content element 2 of the same document, the search will not find a match. For example, the following query will not find a match when “lion” is in a content element and “tiger” is in the DocumentTitle property:
SELECT … FROM Document D WHERE CONTAINS(*, 'lion AND tiger <IN> DocumentTitle');
Long full-text queries that require fetching several pages of data should not be executed while objects matching the full-text query are being either deleted or modified; data may be missing from the results. For instance, suppose a full-text query was started and the first page was fetched, then some rows matching the CONTAINS or FREETEXT clause were deleted. Subsequently, when the second page is fetched, the second page may not contain all existing rows that would be expected. Note that the missing rows are not the ones deleted (matching the CONTAINS or FREETEXT clause), but are existing database rows that are skipped because the starting point for the second page of data may not be valid.
See also FullTextRowLimit and Optimized Queries.
Use CONTAINS to search for precise or fuzzy (less precise) matches for the following types of text:
The first parameter in the CONTAINS function identifies the property or pseudo-property to which the content filter is to be applied, and the second parameter specifies the filter expression.
The first parameter (<property_spec>) can be any of the following:
PropertyDefinition.isCBREnabled
field and the ClassDefinition.isCBREnabled
field to true
. You can also use FileNet Enterprise Manager to enable a property for CBR. See
Enable CBR for class definitions and properties in the Content-based Retrieval "How To" section of the Content Engine Administration online help.Note Support for the first parameter as a property name (anything other than "Content" or "*") was implemented in the 4.0 release. If you have full-text indexing information that was created using a prior release, this information is not associated with a property: using a property name in the CONTAINS clause will not return any matches based on the full-text information from the prior release. If the functionality of searching a specific property is required, the documents from the prior release can be re-indexed using an index job (IndexJob
class) to create the necessary property-related full-text index.
The second parameter of the CONTAINS function is the filter expression, and indicates what to search for. Refer to the Autonomy K2 documentation for more information about the format and use of filter expressions for CONTAINS. Autonomy has this documentation in their Verity K2 product under the heading "simple parser".
Use FREETEXT to search the text of document content for values that match the meaning, but not the exact wording in the filter expression. When using FREETEXT, the full-text query engine internally breaks the filter expression into a number of search terms, assigns each term a weight, then finds the matches.
The first parameter of the FREETEXT function (<property_spec>) must be either "*" or "Content" to denote searching all CBR-enabled data, and the second specifies the filter expression. The literal "Content" is supported for backward compatibility and is equivalent to "*". "Content" is deprecated and should not be used for new applications.
The second parameter of the FREETEXT function is the filter expression, indicating what is being searched for. For more information about the format and use of FREETEXT filter expressions, see the Autonomy Verity K2 product documentation, under the heading "freetext parser" and "query by example (QBE) parser". The QBE parser is equivalent to the freetext parser.
The FREETEXT function does not support searching a single string property that is CBR-enabled because that support does not exist in Autonomy K2. All CBR-enabled properties (including content, if any), are searched by an instance of FREETEXT. Use the CONTAINS function instead of FREETEXT to search a single CBR-enabled string property without searching other properties or content (note that the filter expression is somewhat different).
Whenever a CONTAINS or FREETEXT clause is used, a join is performed on the (internal) ContentSearch
or VerityContentSearch
classes.
Note Instances of the ContentSearch
or VerityContentSearch
classes cannot be retrieved. However, values for the properties of these classes can be specified, and are defined in the metadata for these classes. See ContentSearch and VerityContentSearch.
The join is necessary because when a query with a full-text search is executed, the full-text search is done first, then the data from the full-text search is copied to a temporary database table that is referenced by either the ContentSearch
or VerityContentSearch
class name (both behave identically). The remainder of the search statement is then executed against the repository, joining to this temporary table to access the full-text search data.
ContentSearch
is a base class, and VerityContentSearch
is a subclass of ContentSearch
. VerityContentSearch
has properties that are specific to the Verity full-text search engine. When other full-text search engines are supported, other subclasses of ContentSearch
will be used to access data specific to the particular full-text engine being used.
Only one join to the VerityContentSearch
or ContentSearch
class is allowed per query statement, since only one CONTAINS or FREETEXT clause is allowed.
The following is an example of an inner join in a full-text search:
SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject WHERE CONTAINS(*,'text') AND D.Creator = 'Frank'
This query is executed by first evaluating the CONTAINS(*, 'text') clause and writing that data to a temporary table. Then the remainder of the query is executed:
SELECT … FROM Document D INNER JOIN ContentSearch CS ON D.This=CS.QueriedObject WHERE CS.QueriedObject IS NOT NULL AND D.Creator = 'Frank'
Note that the CONTAINS clause has been replaced by CS.QueriedObject IS NOT NULL
.
In the example above, the CONTAINS clause is used with an AND operator. An OR operator would yield confusing results due to the presence of the inner join (which is an effective AND). For this reason, an AND operator is required with an INNER JOIN, and an OR operator is disallowed.
An example of an outer join would be:
SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject WHERE CONTAINS(*,'text') OR D.Creator = 'Frank'
The outer join above first executes the CONTAINS clause and builds the temporary table in the database that holds the full-text search results, then executes the query:
SELECT … FROM Document D LEFT OUTER JOIN ContentSearch CS ON D.This=CS.QueriedObject WHERE CS.QueriedObject IS NOT NULL OR D.Creator = 'Frank'
When using outer joins, only the ContentSearch
class can be joined conditionally. The query above would not be allowed to use a right outer join, because that would potentially return only ContentSearch
data, and that is not allowed. Outer joins must also use an OR operator when combining the CONTAINS clause with other conditions. Use of an AND operator here is disallowed.
Certain restrictions exist when performing queries, as noted below.
You can define a custom string property to store in either a short or long database column by setting the UsesLongColumn property when the property is created. A long string column may only appear in the WHERE clause of a query as part of a LIKE, IS NULL or IS NOT NULL expression or, if enabled for CBR, as part of a CONTAINS or FREETEXT expression.
Properties of type Boolean, DateTime, Float64, ID, Integer32, and Object may appear in an ORDER BY clause, along with short String properties. Neither Binary nor long String properties may be used to order a query.
Any property appearing in the ORDER BY clause must also be present (explicitly or by use of *) in the SELECT
list.
A DISTINCT query can only be performed when all of the SELECT
ed properties are orderable (see above). For example, if property Foo is not orderable (Binary, or String type with UsesLongColumn), the following query produces an error message:
SELECT DISTINCT Foo From Bar
Obsolete (not used)
The time limit for the query, in seconds.
FullTextRowLimit indicates the number of rows to pull from the full text index prior to executing the remainder of the query. There is one row for each content element that is indexable, plus one more row for all CBR-enabled strings of an object. As noted in Full-Text Joins, data from the full-text index is copied to a temporary table, and that table is then joined with the remainder of the object store database to execute the query.
A query may have a CONTAINS clause that matches many thousands of rows in the full-text index. However, a user may not want to pull all these rows into the temporary database table prior to running the remainder of the query. In this case, the FullTextRowLimit can be set to a lesser value, enabling the user to see a subset of the matches more quickly.
You must be careful not to set the FullTextRowLimit too low. For example, suppose a query has the WHERE clause "WHERE color = 'red' and CONTAINS(*, 'blue')". This query may have a thousand rows that match "CONTAINS(*, 'blue')", but only ten rows that match "color = 'red'" and "CONTAINS(*, 'blue')". If FullTextRowLimit is set to 500, then only 500 rows are pulled from the full-text index and written to the temporary table. However, those 500 rows may not be the same rows that also have "color = 'red'" in the database, so not all ten rows may be found as a result of the query.
If a value for FullTextRowLimit is not supplied, the value of the ObjectStore.FullTextRowDefault
property (stored in the GCD) is used.
If a value for FullTextRowLimit is supplied and this value is greater than the value of ObjectStore.FullTextRowMax
, then the value of ObjectStore.FullTextRowMax
is used instead. The FullTextRowMax
property is present on the ObjectStore
class, enabling the system administrator to prevent queries from using too much processing time.
A full-text query is optimized if the following is true:
VerityContentSearch.Rank
, by default.ContentSearch
class.An optimized full-text query can return results faster in some instances, and will also allow the user to retrieve more rows without hitting the FullTextRowLimit value, as well as possible timeout errors or out of memory conditions.
If a full-text query is optimized, when a request for the first page is generated, the server retrieves the FullTextRowLimit number of rows from the full-text search engine and stores them in the temporary database table, then executes the relational part of the query. However, if an insufficient number of rows are found to fill the current page, the server retrieves the next set of rows from the full-text search engine, repeating the process until enough rows are found. When it pulls the second and subsequent set of rows from the full-text search engine, the server has the percentage of rows retrieved from the full-text engine that are actually used in the resultant data to be returned to the user, so it no longer uses the FullTextRowLimit, and instead retrieves the number of rows based on the prior percentage used.
When the second or subsequent page is requested for an optimized full-text query, the server also retrieves the next set of rows from the full-text search engine. Therefore, this type of query can be used to browse through an unlimited number of rows that match a content search.
Optimization may not be suitable for all types of queries: ordering by descending VerityContentSearch.Rank
does slow the relation part of the query, and some callers may not want the data to be returned in descending order.
A continuable query that is not optimized will not continue past the FullTextRowLimit number of rows. When the query is not ordered by descending VerityContentSearch.Rank
, the server cannot retrieve the next set of rows for each subsequent page (these rows can only be retrieved from the search engine by descending VerityContentSearch.Rank
). Instead, the server retrieves the same set of rows for each page processed, using the FullTextRowLimit value for the number of rows.
Queries that are not continuable pull just one set of a FullTextRowLimit number of rows from the full-text search engine. The value of FullTextRowLimit must be chosen carefully in this case.
Queries that are not optimized may return a subset of the number of rows matching the query if more than the FullTextRowLimit number of matches exists in the full-text index. When this condition occurs, the server throws a CBR_FULLTEXTROWLIMIT_EXCEEDED exception to notify the client of the condition, and that not all matches have been returned. This exception will be thrown when the caller iterates through the rows returned for the query, after the last row found.
If a value for FullTextRowLimit is not supplied, the value used will be the value of the ObjectStore.FullTextRowDefault property stored in the Global Configuration Data (GCD) database.
If a value for FullTextRowLimit is supplied and this value is greater than the value of the ObjectStore.FullTextRowMax property, the ObjectStore.FullTextRowMax
value is used instead. The FullTextRowMax property enables system administrators to prevent queries from using too much processing time.
This section provides some examples of simple SQL statements to retrieve various pieces of information.
Retrieve documents created by the Administrator user after April 1, 2005, and return the specified list of properties for each returned document:
SELECT Creator, DateCreated, DocumentTitle FROM Document WHERE (DateCreated >20050401T080000Z AND Creator='Administrator')
Retrieve documents by ID (GUID) that have a document title containing the character pattern "Acct":
SELECT DocumentTitle Id FROM Document WHERE DocumentTitle LIKE '%Acct%'
If the object store contains three documents whose titles contain the characters "Acct", then this query would return them in this format:
AcctDocument {4E017CB8-4980-4BB2-88E9-248C555445E2} MyAcctDocument {01C92932-E840-4FC2-90E8-45E245248CB5} AcctgStoredSearch {4ECDE7D9-F551-4C53-A109-8D81B1DE8577E}
To search for a specific ID, you can change the query to specify a particular GUID:
SELECT DocumentTitle Id FROM Document WHERE Id='{4ECDE7D9-F551-4C53-A109-8D81B1DE8577E}'
The following query retrieves the list of checked out documents (document versions in Reservation state) created by the "HRManager" user:
SELECT Id FROM Document WHERE VersionStatus=3 AND Creator='HRManager'
The query above represents the SQL query behind the ObjectStore.getCheckoutList
method.
By changing the value of the VersionStatus property, you can use this query to search for documents that are Released (1), In Process (2), or Superseded (4).
The following SQL queries retrieve the containees from a particular folder (in this example, the
folder "/Test". Note that when retrieving a list of folders, the Workplace application issues the query in lieu of calling the Folder.getContainees
method. Folder.getContainees
returns all items in the folder, with no limit on the number of items returned. Using such queries, you can set the criteria to limit the number of items returned to only those required by your application.
Get subfolders:
SELECT f.[ObjectType], f.[Id], f.[FolderName], f.[ContainerType], f.[ClassDescription], f.[OIID] FROM Folder f WHERE (( f.This infolder '/Test')) AND (( [IsHiddenContainer]=false) )) ORDER BY FolderName
Get documents:
SELECT d.[ObjectType], d.[Id], d.[LastModifier], d.[DocumentTitle], d.[DateLastModified], d.[ContentSize], d.[MajorVersionNumber], d.[ClassDescription], FROM Document d WHERE (( (d.This infolder '/Test') ) AND (( [MimeType] IS NULL) OR ([MimeType] <> 'application/x-filenet-search') ) )) ORDER BY DocumentTitle
The following sample query searches for and returns the ID of all HTML documents in the database with greater than 10,000 bytes of content, and that have been modified since December 12, 2005:
SELECT Id FROM Document d WHERE ([MimeType] = 'text/html' AND [ContentSize] > 10000.0) AND [DateLastModified] > 20051201T000000Z