Skip to Content

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″><!–
     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>

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Young
    Thanks for the information regarding path queries. I’ve learned to appreciate them quite a bit when using QueryBuilder. However, I’ve not been successful in my attempts to use them in the queries specified in the .properties files when working with biarengine.jar. So far I’ve had to resort to the ‘regular’ queries. Has this not been implemented in biarengine, or am I possibly doing something wrong?

    Mark

    (0) 
    1. Ted Ueda Post author
      Hello Mark,

      The path query handling hasn’t been implemented in the biarengine.jar command-line tool.

      Sincerely,

      Ted Ueda

      (0) 
  2. Jan Fröhlich
    Thank you for sharing such interesting informations.
    I just tried path queries with a german installation of BOEXI3.1 and had some problems with it.
    After querying for one of your examples i received an error message saying that the folder “Root Folder” can not be found in the repository.
    I changed the query to path://InfoObjects/ and searched for something similar and found out, that those folders are language dependend.
    For use with a “german infostore” I had to use path://InfoObjects/Stammordner. Is there a way around this?
    Regards
    Jan
    (0) 
    1. Ted Ueda Post author

      Programmatically, you would use the cuid value for the Root Folder, which is fixed (look up the SI_CUID value, then use the value):Ted Ueda

      (0) 
  3. Christian Reed
    Hi Ted,

    Thanks for this great article.  I have learned a lot.

    While looking through the SDK documentation hoping to learn more I found some surprising problems with the examples given:

    For example:

    path://SystemObjects/User Groups/Administrators/groups[SI_GROUP_MEMBERS]
    returns no data

    path://SystemObjects/User Groups/Administrators/members[SI_GROUP_MEMBERS]
    returns all of the groups to which Administrator belongs, funny it seems that “members”, “ancestors”, and “parents” all have the same result here.

    Also, I noticed that at least on my system it seems that the function keyword is case-sensitive, is this true.

    path://SystemObjects/User Groups/Administrators/Members[SI_GROUP_MEMBERS]
    returns the error “There was an error retrieving data from the server: Not a valid query. (FWB 00025)”, but if you replace the “Members” with “members” the query runs just fine.

    Can you comment on these anomalies and findings?

    (0) 
  4. Alastair Gulland
    Hi

    I couldn’t see anywhere in the developer guides where path queries (or indeed enterprise queries) are documented. Can you tell me where exactly?

    Thanks

    AL

    (0) 
  5. Lisa Stieg
    I am trying to determine if my need can be met, I am not a programmer so bear with me, I need to be able to pull out of infoview the following,

    SI_NAME, Full path (parent & subfolder), SI_UPDATE_TS, SI_SENDER_NAME, SI_MAIL_SUBJECT, SI_MAIL_ADDRESSES, SI_MAIL_CC

    It will be all Crystal Reports and I need this ONLY for FUTURE recurring scheduled reports. Need is URGENT as it is a HIPAA compliance issue.

    (0) 

Leave a Reply