Chapter 3. Introduction to Content Engine API programming
3.6 Querying
When developing for the CE, one of the primary ways of finding documents is to do a search, based on data from a user. Workplace and WorkplaceXT both offer search templates and stored searches that a user can execute.
When you program, doing the search directly in code by using the SearchScope and SearchSQL classes is necessary. Searching the CE requires the following steps:
1. Create the SQL statement. 2. Set the SearchScope. 3. Execute the search. 4. Work with the results.
3.6.1 SearchSQL
The CE offers a language for executing queries within single or multiple object stores. The SearchSQL class offers helper methods to assist you in constructing a SQL statement, or the SQL statement can be constructed independently and passed to a SearchSQL instance as a string.
SQL statements must follow the CE SQL syntax, which generally conforms to SQL-92, with extensions for FileNet-specific constructs. The complete SQL syntax guide is available in the IBM FileNet P8 Platform documentation by selecting ECM Help→ Developer Help→ Content Engine Development→ Reference→ SQL Syntax Descriptions.
Building a query with SearchSQL
The CE API has a SearchSQL object with a number of convenience methods for creating the search SQL. In addition, the SearchSQL object can be instantiated with an actual SQL statement. Example 3-28 and Example 3-29 show an example of building a query with SearchSQL.
Example 3-28 Building a query with SearchSQL in Java
SearchSQL sqlObject = new SearchSQL("SELECT DocumentTitle, Id FROM Document WHERE Creator = 'jsmith'");
Example 3-29 Building a query with SearchSQL in C#
SearchSQL sqlObject = new SearchSQL("SELECT DocumentTitle, Id FROM Document WHERE Creator = 'jsmith'");
Using the IBM FileNet Enterprise Manager to build SQL
When you initially build the SQL, use the Enterprise Manager's Query Builder
Note: The helper methods on SearchSQL and a SearchSQL object that are
instantiated with a SQL statement are mutually-exclusive. The SQL passed in (on creation) cannot be updated with the convenience methods. Experienced developers typically find that supplying the SQL as a string is the simpler approach.
3. Select new search.
4. Build the search in the Content Engine Query Builder. 5. Select View→ SQL View.
Figure 3-3 shows how to use the Enterprise Manager’s Content Engine Query Builder to create the search SQL for use in a SearchSQL object.
Figure 3-3 Compiled SQL statement from Content Engine Query Builder
Executing the query and working with the results
After a search has been executed, the SearchScope returns an
IndependentObjectSet or RepositoryRowSet of the returned items, depending on whether you fetch objects or rows, respectively. See Example 3-30 and
Example 3-31 on page 74.
Example 3-30 Search example in Java
// Construct the sql statement SearchSQL sql = new SearchSQL(
"select ISTOStartDate, ITSOEndDate, ITSOVehicle " + "from ITSOIdleActivity " +
"where “ +
"ITSOVehicle = OBJECT('{D5DC8C04-2625-496f-A280-D791AFE87A73}') " + "AND ITSOStartDate < 20090801T000000Z OR " +
"ITSOEndDate > 20090701T000000Z" );
// Search the object store with the sql and get the returned items SearchScope scope = new SearchScope(os);
IndependentObjectSet set = scope.fetchObjects(sql, null, null, false); // Loop through the returned results
for( Iterator i = set.iterator(); i.hasNext(); ) {
// Get the document
CustomObject obj = (CustomObject)i.next(); // Code to work with object goes here }
Example 3-31 Search example in C#
// Construct the sql statement SearchSQL sql = new SearchSQL(
"select ITSOStartDate, ITSOEndDate, ITSOVehicle " + "from ITSOIdleActivity " +
"where " +
"ITSOVehicle = OBJECT('{D5DC8C04-2625-496f-A280-D791AFE87A73}') " + "AND ITSOStartDate < 20090801T000000Z OR " +
"ITSOEndDate > 20090701T000000Z" );
// Search the object store with the sql and get the returned items SearchScope scope = new SearchScope(os);
IIndependentObjectSet set = scope.FetchObjects(sql, null, null, false); IEnumerator e = set.GetEnumerator();
while(e.MoveNext()) {
// Get the current object
ICustomObject obj = (ICustomObject)e.Current; // Code to work with object goes here
}
The SearchScope methods execute the SQL statement on one or more object stores to find objects (IndependentObject instances), database rows
(RepositoryRow instances), or metadata (ClassDescription instances). If the query includes a JOIN operator, you must fetch rows instead of objects.
Cross object store searching
When doing a cross-object store search (also called a
merged scope
search), the search results are merged based on the merge mode. The merge mode can be either: INTERSECTION: The search results will contain the classes occurring in all repositories searched.
UNION: The search results will contain the classes occurring in any repository searched.
When the merge mode is UNION and a class or property is not found in any repository, the following occurs:
For classes, an INNER JOIN returns no rows, and an OUTER JOIN returns nulls. JOIN types are specified in the JoinOperator class
For properties, the property value is null in a selection list or WHERE clause, and is omitted from an ORDER BY clause.
To search across multiple object stores: 1. Create an array of object stores to search.
2. Create the search scope with the array of object stores to search and the merge mode.
3. Create the SQL. 4. Execute the search. 5. Work with the results.
See Example 3-32 and Example 3-33 on page 76.
Example 3-32 Setting the scope to be multiple object stores in Java
// Create the object store array
ObjectStore[] osArray = new ObjectStore[]{os1,os2}; // Create the search scope
SearchScope objStores = new SearchScope(osArray, MergeMode.INTERSECTION);
Example 3-33 Setting the scope to be multiple object stores in C#
// Create the object store array
IObjectStore[] osArray = new IObjectStore[] { os1, os2 }; // Create the search scope
SearchScope objStores = new SearchScope(osArray, MergeMode.INTERSECTION);
3.6.3 Content searches
Full-text searches (also known as
content-based retrieval
(CBR)) can be used to search for words or phrases that are part of object content or that occur in string properties of these objects. For the content in an object or its string properties to be searched, you must enable CBR for the object class and any of its string properties to be included in a content search.A content search is indicated by either a CONTAINS or FREETEXT operator in the SQL statement contained in SearchSQL. The CONTAINS and FREETEXT operators have somewhat different operand formats and provide somewhat different search characteristics. Although the CONTAINS operator can search content in all properties, or in a single property, the FREETEXT operator can search content only in all properties. Note that attempting to specify a property name for FREETEXT will generate an exception. The CONTAINS operator is generally preferable to the FREETEXT operator.
For more information about the CONTAINS and FREETEXT operators, go to
ECM Help→ Developer Help→ Content Engine Development→ Reference→ SQL Syntax Descriptions→ Full-Text Queries.
3.6.4 Paging support
When working with a large result set, returning too many items at once can
Note: Full-text queries can have unexpected performance impact. You should
experiment with CBR queries on realistic data sets to ensure that you have taken performance into account.
Example 3-34 Paging in Java
// A query that will return a large result set
SearchSQL sql = new SearchSQL("select [DocumentTitle] from document"); SearchScope scope = new SearchScope(os);
// Set the paging to be 50 items per page and enable continuation IndependentObjectSet s = scope.fetchObjects(sql, 50, null, true); // Get the page iterator
PageIterator p = s.pageIterator(); // Loop through each page
while(p.nextPage()) {
// Loop through each item in the page for(Object obj : p.getCurrentPage()) {
// Get the document object and write Document Title Document doc = (Document)obj;
System.out.println(
doc.getProperties().getStringValue("DocumentTitle")); }
}
Example 3-35 Paging in C#
SearchSQL sql = new SearchSQL("select [DocumentTitle] from document"); SearchScope scope = new SearchScope(os);
IIndependentObjectSet s = scope.FetchObjects(sql, 50, null, true); IPageEnumerator p = s.GetPageEnumerator();
while(p.NextPage()) {
Console.WriteLine(p.ElementCount); foreach(object obj in p.CurrentPage ) {
IDocument doc = (IDocument)obj; Console.WriteLine(
doc.Properties.GetStringValue("DocumentTitle")); }
3.7 Viewing documents
The CE can store a wide variety of document file types, including images. For image file types that users need to view, applications can choose to retrieve the image content and display it in their preferred viewer application, or they can use WorkplaceXT’s Java viewer. The supported image file types for WorkplaceXT’s Java Viewer can be found in ECM Help→ User Help→ Actions, preferences, and tools→ Work with Image Viewer.
For applications that choose to use the WorkplaceXT Java viewer applet, the supported way to instantiate the viewer is to obtain a user token and call the getContent servlet from WorkplaceXT. Using the Java viewer applet directly with a separate application is not supported.
Example 3-36 and Example 3-37 on page 79 show the code samples for calling the WorkplaceXT Java viewer applet.
Example 3-36 Document viewing in Java and JSP
<%@ page import="java.util.*, java.io.*, java.net.*"%> <html>
<body> <%
String baseP8URL = "http://aeServer:9080/WorkplaceXT/"; String user = "user";
String password = "password";
String objectStore = "ObjectStoreName";
String docID = "{C9712786-4B17-4512-9E13-9F4154B35FC2}";
// Call WorkplaceXT’s setCredentials servlet to obtain user token URL url = new URL(baseP8URL +
"setCredentials?op=getUserToken&userId="
+ user + "&password=" + password + "&verify=true"); HttpURLConnection p8con =
String userToken = URLEncoder.encode(tempUserToken, "UTF-8"); // Build URL to getContent servlet
String contentUrl = baseP8URL + "getContent?objectStoreName=" + objectStore + "&id=" + docID +
"&objectType=document&ut=" + userToken + "&impersonate=true"; %> <script language="javascript"> window.top.location="<%=contentUrl%>" </script> </body> </html>
Example 3-37 Document viewing in C# and ASP.NET
using System.Net; using System.IO;
public partial class _Default : System.Web.UI.Page {
public string WorkplaceRootUrl = ""; public string ContentUrl = "";
public string Username = "";
private void Page_Load(object sender, System.EventArgs e) {
// set constant values
// replace these values with values applicable to your site WorkplaceRootUrl = "http://aeServer:9080/WorkplaceXT/"; Username = "user";
string pwd = "password";
string objectStoreName = "ObjectStoreName";
string Id = "{C9712786-4B17-4512-9E13-9F4154B35FC2}";
// Call WorkplaceXT’s setCredentials servlet to obtain user token string UserToken = getCEUserToken(WorkplaceRootUrl, Username, pwd); // create URLs for the JavaViewer
ContentUrl = WorkplaceRootUrl + "getContent?objectStoreName=" + objectStoreName + "&id=" + Id + "&objectType=document&ut=" + UserToken + "&impersonate=true";
private string getCEUserToken(string baseURL, string uid, string pwd) {
string UserToken = "";
// make the request and get the response
WebRequest request = WebRequest.Create(baseURL +
"setCredentials?op=getUserToken&userId=" + uid + "&password=" + pwd + "&verify=true");
request.Method = "POST";
WebResponse response = request.GetResponse(); // read the response from the stream into a byte[] Stream stream = response.GetResponseStream(); byte[] token = new byte[response.ContentLength]; stream.Read(token, 0, (int)response.ContentLength); response.Close();
// and convert the bytes in the array into a string. foreach (byte chr in token)
UserToken += System.Convert.ToChar(chr); // return the encoded string
return Server.UrlEncode(UserToken); }
}
// Below is the aspx page source <html> <body> <script language="javascript"> window.top.location="<%=ContentUrl%>" </script> </body> </html>
3.7.1 User tokens
User tokens can also be obtained in other ways and are described in detail in
ECM Help→ Developer Help→ Workplace Development→ Workplace Customization Guide→ User Tokens.