Are you a developer integrating BusinessObjects Enterprise XI 3.x into your application using the BusinessObjects Enterprise SDK or the BIPlatform Web Services Consumer?
Are you looking to simplifying querying the InfoStore for repository InfoObjects?
If yes to the above, then this blog may be of interest to you. I'll describe Path Queries, when to use them, and how to modify the Query Builder Admin Tool to accept them.
h5. Prologue
Here's an usual suspect from the rogue gallery of sure-to-fail code - an Enterprise Query of the form:
bq. SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME='My Report' And SI_INSTANCE=0
I run into this query at least once every other month, together with a Support Incident description along the lines of "Updating 'My Report' does not work when I view it in my custom application".
Running that query in the Query Builder Admin Tool returns two documents - the first typically in a folder named "Test Reports", and the second the production copy that's been updated.
Turns out that the custom application was referring to the test copy of the document all this time - someone forgot to delete the test copy - and the issue not identified until the production document was updated.
SI_NAME of an object in the repository need not be unique, so there's no guarantee that the rogue query above will return the document you expect.
h5. Uniquely Query for a Document
How do you ensure uniqueness - that the report you're asking for is the report you'll get? One way is to look up the SI_CUID value for the report and always use that when referencing your document in an Enterprise query.
Alternatively, you can check the folder path for the document. In older versions of BusinessObjects Enterprise, this took multiple queries - one to retrieve your candidate documents, then another to check the folder path of each document.
Enhancements to the Enterprise SDK with XI 3.x greatly simplifies this task. You can now accomplish the task via a single query. There's two ways to do this. First is using the 'Folder Hierarchy' Relationship in a query as I described in a previous blog entry, "BusinessObjects Enterprise SDK - Relationship Queries". A relationship query, however, isn't particularly easy to write or read.
The second method is to use +Path Queries. +They were introduced with BusinessObjects Enterprise XI Release 2 Web Services, and was migrated over to BusinessObjects Enterprise XI 3.x Java and .NET SDKs.
Here's a sample path query, that references the sample "World Sales Report" Crystal Report:
bq. path://InfoObjects/Root Folder/Report Samples/Demonstration/World Sales Report
It's a very compact and easy-to-read URI for referencing a document by its folder path. As comparison, here's the equivalent Enterprise SQL query using relationships:
bq. Select
SI_ID, SI_CUID, SI_NAME, SI_OWNER, SI_CREATION_TIME, SI_LAST_SUCCESSFUL_INSTANCE_ID, SI_FILES, SI_DESCRIPTION, SI_CHILDREN, SI_PARENT_CUID, SI_KIND, SI_INSTANCE
From
CI_INFOOBJECTS
Where
CHILDREN("SI_NAME='Folder Hierarchy'",
"CHILDREN('SI_NAME=''Folder Hierarchy''',
'CHILDREN(''SI_NAME=''''Folder Hierarchy'''''',
''SI_NAME=''''Root Folder'''' And SI_PARENTID=4'')
And SI_NAME=''Report Samples''')
And SI_NAME='Demonstration'")
And SI_NAME='World Sales Report'
I think the advantage of path queries is clear in comparing the two queries.
h5. Path Queries
A path query is an alternative way of querying for objects managed by the BusinessObjects Enterprise CMS repository. The syntax is based loosely on the XML Path Language specification and is quite simple to read and understand.
I won't describe path query syntax in great detail here - the BusinessObjects Enterprise SDK Developer Guides do give very thorough explanations - but let me cover a few of the basics.
h6. The "/" path operator
Use "/" to specify the folder path - the "/" operator indicates a SI_PARENTID relationship from the right-side to the left-side objects. For example, to find all objects named "My Report" in the public folder path "My Folder", use the query:
bq. path://InfoObjects/Root Folder/My Folder/My Report
Since scheduled instances are related to the scheduled document via SI_PARENTID as well, the query:
bq. path://InfoObjects/Root Folder/My Folder/My Report/
returns all scheduled instances of the "My Report" report.
h6. The "@" attribute operator
Use "@" to restrict the returned object properties. For example, to return SI_ID, SI_CUID, SI_PARENT_CUID, and SI_NAME properties of the "Everyone" UserGroup, use:
bq. path://SystemObjects/User Groups/Everyone@SI_ID,SI_CUID,SI_PARENT_CUID,SI_NAME
you can use "@*" to return all properties, or "@STATIC" to return all non-relationship-based properties.
h6. The "*" wildcard
Use the wildcard "*" to glob for matching names. To search for all root Public Folders starting with "R", use:
bq. path://InfoObjects/Root Folder/R*
or to look for all objects starting with "D" found in root Public Folder starting with "R", use:
bq.
path://InfoObjects/Root Folder/R*/D*
Use "[]" to specify additional conditions to your query, where the "[" and "]" delimits a condition expression. For example, to query for Crystal Reports created only this year, use:
bq. path://InfoObjects/Root Folder/Crystal Reports Folder/[SI_KIND='CrystalReport' And SI_CREATION_TIME >= '2009.01.01']
where '2009.01.01' represents 12:00 am January 1, 2009 in GMT-0.
With the compact easy-to-read format of path queries, I make much less errors writing path queries than Enterprise queries. And in my book, the 10 minutes spent debugging a query string are 10 minutes I could have spent getting coffee (I live in Vancouver - when a buddy IM's you saying "Meet me at the Starbucks just around the corner", the proper response is "Which corner?"). if(Pattern.matches("^
w+://.*", sqlStmt)) { <br /> sqlStmt = iStore.getStatelessPageInfo(sqlStmt, new PagingQueryOptions()).getPageSQL();<br/> }<br/> objects = iStore.query(sqlStmt);<br/>}
<p>that uses a Regex to determine if the query string passed into Query Builder is a URI or Enterprise query, then if URI generates the Enterprise query, before running the query in the InfoStore.</p><p>Alter the import list at the top of the file to look like:</p>bq. <%@ page import="com.crystaldecisions.sdk.framework.ISessionMgr,<br /> com.crystaldecisions.sdk.framework.CrystalEnterprise,<br/> com.crystaldecisions.sdk.framework.IEnterpriseSession,<br/> java.util.ResourceBundle,<br/> java.text.MessageFormat,<br/> java.io.IOException,<br/> com.crystaldecisions.sdk.properties.IProperties,<br/> java.util.Iterator,<br/> com.crystaldecisions.sdk.properties.IProperty,<br/> com.crystaldecisions.sdk.exception.SDKException,<br/> com.crystaldecisions.sdk.occa.infostore.*,<br/> com.crystaldecisions.examples.*,<br/> com.crystaldecisions.sdk.occa.security.ILogonTokenMgr,<br/> com.crystaldecisions.sdk.uri.*,<br/> java.util.Date,<br/> java.util.regex.Pattern,<br/> java.textDateFormat" %>
<p>to include the classes required for parsing the path queries.</p><p>The complete code should look as follows:</p>bq. *Query Builder query.jsp modified to accept path queries*<br/><textarea cols="75" rows="10"><!--
File Version Start - Do not remove this if you are modifying the file
Build: 10.0.0
File Version End
(c) Business Objects 2003. All rights reserved.
-->
<%@ page import="com.crystaldecisions.sdk.framework.ISessionMgr,
com.crystaldecisions.sdk.framework.CrystalEnterprise,
com.crystaldecisions.sdk.framework.IEnterpriseSession,
java.util.ResourceBundle,
java.text.MessageFormat,
java.io.IOException,
com.crystaldecisions.sdk.properties.IProperties,
java.util.Iterator,
com.crystaldecisions.sdk.properties.IProperty,
com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.examples.*,
com.crystaldecisions.sdk.occa.security.ILogonTokenMgr,
java.util.Date,
java.text.DateFormat"%>
<%@ page language="java" errorPage="exception.jsp" %>
<%!
// helper methods
private void printOutProps(IProperties props, DateFormat dateFormatter, PageContext context ) throws IOException
{
String propStr = "";
Object obj = null;
JspWriter out = context.getOut();
Iterator itr = props.values().iterator();
while ( itr.hasNext()){
IProperty prop = (IProperty)itr.next();
out.write("\n<tr><td valign='top' width='15%'>" + Encoder.encodeHTML(CePropertyID.idToName(prop.getID())) + "</td>");
if (prop.isContainer()) {
out.write("<td valign='top'><table class='basic' width='100%' border='1' cellspacing='0'>");
IProperties bag = (IProperties)prop.getValue();
if ( null == bag ) {
out.write("\n<tr><td></td></tr>");
}
else {
printOutProps( bag, dateFormatter, context );
}
out.write("</table></td>");
}
else {
out.write("<td valign='top'>");
obj = prop.getValue();
if ( null == obj ) {
propStr = "";
}
else if (obj instanceof Date) {
propStr = dateFormatter.format((Date)obj);
}
else {
propStr = obj.toString();
}
out.write(Encoder.encodeHTML(propStr) + "</td>");
}
out.write("</tr>");
}
}
private void printOutBag(String headertext, IProperties props, DateFormat dateFormatter, PageContext context) throws IOException
{
if (props == null || props.size() == 0) {
return;
}
context.getOut().write("\n<tr class='header'><td valign='top' colspan=2 width='15%' class='sectionHeader'>" + headertext + "</td></tr>");
printOutProps(props, dateFormatter, context);
}
%>
<%
ExamplesUtil.setCharSet(request, response);
ExamplesUtil.setRequestEncoding(request, response);
// get resource bundle
String sqlStmt = request.getParameter("sqlStmt");
if (sqlStmt != null)
{
// before logonservlet is called, for setting sqlStmt to the session for later use
// ie.jsp (or nn.jsp) -> query.jsp
session.setAttribute("sqlStmt", sqlStmt);
// ExamplesUtil.forward(request, response, "logon");
// out.clear();
// return;
}
DateFormat dateFormatter = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.MEDIUM, request.getLocale());
// after logonservlet is called, then here
// ie.jsp ( or nn.jsp) -> query.jsp -> logonservlet ( -> newpwdform.jsp -> logonservlet ) -> query.jsp
ResourceBundle resource = ResourceBundle.getBundle("com.crystaldecisions.examples.query", request.getLocale());
ResourceBundle common = ResourceBundle.getBundle("com.crystaldecisions.examples.common", request.getLocale());
String errorpagerfile = "errorpage.jsp";
IEnterpriseSession en = (IEnterpriseSession) session.getAttribute("ISEnterpriseSession");
IInfoStore iStore = (IInfoStore) session.getAttribute("IStore");
IInfoObjects objects = null;
if ((en == null) || (iStore == null))
{
// should never come here, we will forward it to logon page in case it does happen
if ( -1 != request.getHeader("User-Agent").indexOf("MSIE"))
response.sendRedirect(QueryUtil.IE_LOGON_PAGE);
else
response.sendRedirect(QueryUtil.NN_LOGON_PAGE);
return;
}
try {
sqlStmt = ((String) session.getAttribute("sqlStmt")).trim();
// Check to see if URI
if(java.util.regex.Pattern.matches("^
w+://.*", sqlStmt)) {
sqlStmt = iStore.getStatelessPageInfo(sqlStmt,
new com.crystaldecisions.sdk.uri.PagingQueryOptions()).getPageSQL();
}
objects = iStore.query(sqlStmt);
}
catch(SDKException e) {
ExamplesUtil.WriteError(request, response, e, "RETRIEVE_ERROR", errorpagerfile);
out.clear();
return;
}
// form the header
String headerFormat = resource.getString("RESULT_HEADER");
String header = MessageFormat.format(headerFormat, new String[]{Integer.toString(objects.size())});
%>
<html>
<head>
<link rel='stylesheet' type='text/css' name='stylelink' href="<%= ExamplesUtil.getLinkPath(request) %>../default.css">
<title><%= resource.getString("TITLE") %></title>
<base target="_top">
</head>
<body bgcolor="#ffffff" text="#000000" link="#3300cc" vlink="#660066" alink="#FF0000">
<a name="top-anchor"> </A>
<H2><%= resource.getString("TITLE") %></H2>
<table class='basic' width='80%' border='1'>
<tr>
<td align='left' colspan=2><%= Encoder.encodeHTML(sqlStmt) %></td>
</tr>
<tr>
<td align='left'><%= header %></td>
<td align='right'></td>
</tr>
</table>
<HR SIZE='1'>
<%
//LOOP THROUGH THE COLLECTION AND DISPLAY THE OBJECT DETAILS
for ( int i =0; i < objects.size(); i++) {
IInfoObject obj = (IInfoObject)objects.get(i);
out.write("<br><br>");
out.write("<table class='basic' width='100%' border='0'>");
out.write("<tr><td align='left'><b>" + (i+1) + "/" + objects.size() +"</b></td>");
out.write("<td align='right'>");
out.write("<a href='#top-anchor'>" + resource.getString("TOP")+"</a></td></tr></table>");
out.write("<table class='basic' width='100%' border='1' cellspacing='0'>");
//LOOP THROUGH THE OBJECTS AVAILABLE PROPERTIES AND DISPLAY THEIR NAME / VALUES
printOutBag(resource.getString("PROPERTIES"), obj.properties(), dateFormatter, pageContext );
ISchedulingInfo sinfo = obj.getSchedulingInfo();
if (sinfo != null )
{
printOutBag(resource.getString("SCHEDULING_INFO"), sinfo.properties(), dateFormatter, pageContext);
}
IProcessingInfo pinfo = obj.getProcessingInfo();
if (pinfo != null )
{
printOutBag(resource.getString("PROCESSING_INFO"), pinfo.properties(), dateFormatter, pageContext);
}
out.write("</table>");
}
// Keep the session objects, don't discard them.
/*
// Clean up our session to keep the count down
if ( en != null ) {
// release logon token
String lt = Encoder.decodeCookie(ExamplesUtil.getCookieValue(request.getCookies(), DefaultLogonInfo.LOGON_TOKEN));
if(lt.length()>0)
{
ILogonTokenMgr logonTokenMgr = null;
try{
logonTokenMgr = en.getLogonTokenMgr();
logonTokenMgr.releaseToken(lt);
}catch (SDKException e){
// do nothing, we will empty the logon token cookie anyway.
}
}
Cookie logonToken = new Cookie(DefaultLogonInfo.LOGON_TOKEN, Encoder.encodeCookie(""));
logonToken.setMaxAge(0);
logonToken.setPath("/");
response.addCookie(logonToken);
en.logoff();
}
// clean up the session
session.removeAttribute("userID");
session.removeAttribute("IStore");
session.removeAttribute("ISEnterpriseSession");
session.removeAttribute("sqlStmt");
*/
%>
<br><br><div align='right'><a href='#top-anchor'><%= resource.getString("TOP") %></a></div>
</body>
</html>
</textarea>
<p>After making the above modification, you can enter path queries into Query Builder, and see what object they return.</p><p>Have Fun!</p><p> </p><p> </p>