BusinessObjects Enterprise SDK - XI 3.x Path Quer...

ted_ueda · ‎02-20-2009

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*

The "[]" condition operators

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">
<%@ 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>

BusinessObjects Enterprise SDK - XI 3.x Path Queries and Query Builder

The "[]" condition operators

Now live: 2014 SAP HANA and SAP HANA Cloud Applications Challenge voting

My Personal Ux, Fiori, Portal and Cloud Cheat Sheet

Web Dynpro ABAP Demonstration Videos