There is an alternative to InfoStore sql query – it is BO URI queries. In some situations URI queries are significantly shorter and clearer.

For instance, if we need to find all Webi document in folder Test, we will have to run multiple queries to get the result with SQL queries. First you need to find Test folder in the root folder. Then you can only form a SQL to find Webi reports.

String sql0 = "select si_id from ci_infoobjects where si_parent_cuid='"
    + CeSecurityCUID.RootFolder.FOLDERS + "' and si_name='Test'";
int id = ((IInfoObject)infoStore.query(sql0).get(0)).getID();
String sql = "select si_id from ci_infoobjects where si_parentid=" 
    + id + " and si_instance=0 and si_kind='Webi'";

Using URI queries, the logic is far clearer

String uri = "path://InfoObjects/Root Folder/Test/[si_kind='Webi' and si_instance=0]";
String sql = infoStore.getStatelessPageInfo(uri, new PagingQueryOptions()).getPageSQL();

(Both versions lack error handling)

URI syntax

I will try to describe syntax of URI. Non-terminal elements are marked in red.

protocol://expression[[condition]][@attributes][?parameters]

protocol = query | path | cuid | search

Condition should be a valid WHERE condition, attributes are fields separated by comma, parameters are speparated by ampersand and have format name=value. Conditions, attributes and parameters are optional

It is better to see it on an example.

path://InfoObjects/Root Folder/Test/**[si_kind=’Webi’ and si_instance=0]@si_name,si_description?OrderBy=si_parentid

here,

• path is a protocol,
• InfoObjects/Root Folder/Test/** is path expression,
• si_kind=’Webi’ and si_instance=0 is a condition,
• si_name,si_description are attributes, and
• OrderBy=si_parentid is a parameter.

This query will find all Webi documents in folder Test (located in Public Folders) and its subfolders. The corresponding SQL query is

SELECT TOP 200 si_name,si_description,SI_CUID,SI_PARENT_CUID 
FROM CI_INFOOBJECTS WHERE SI_ANCESTOR IN (1851) AND (si_kind='Webi' and si_instance=0) 
ORDER BY si_parentid,SI_ID

(Here 1851 is the ID of Test folder).

The conditions are simply added to the where clause. Attributes are used to restrict the list of fields in select clause (SI_CUID and SI_PARENT_CUID will always be added). If attributes are not specified all fields will be returned. Properties are primarily used for paging query. You can specify order with OrderBy.

Path expression syntax

path://root/path

root = InfoObjects | AppObjects | SystemObjects | *

path = path/node[/]

where node can be name of object, search pattern (e.g. *de* all objects that contain ‘de’ in their name) or recursive search wild char **. If the experssion ends with slash, children will be returned.

For instance, here are the queries to find object in the root folder with name ‘Test’

URI	path://InfoObjects/Root Folder/Test
SQL	SELECT TOP 200 SI_CUID, SI_NAME, SI_PARENT_CUID, SI_DESCRIPTION, SI_CREATION_TIME, SI_KIND, SI_CHILDREN, SI_OWNER, SI_PATH, SI_CORPORATE_CATEGORIES_ID, SI_PERSONAL_CATEGORIES_ID, SI_FILES, SI_INSTANCE, SI_SCHEDULE_STATUS, SI_LAST_SUCCESSFUL_INSTANCE_ID, SI_KEYWORD FROM CI_INFOOBJECTS WHERE SI_PARENTID IN (23) AND SI_NAME=’Test’ ORDER BY SI_ID

where 23 is ID of the root folder (aka Public Folders).

(In the following examples, I will only show WHERE clause.)

Trailing slash says to find direct children:

URI	path://InfoObjects/Root Folder/Test/
SQL	… WHERE SI_PARENTID IN (1851) …

Plus sign allows to include the parent in the result:

URI	path://InfoObjects/Root Folder/Test+/
SQL	… WHERE (SI_PARENTID IN (1851) OR SI_ID IN (1851)) …

You can use asterisk character to search using pattern:

URI	path://InfoObjects/Root Folder/Test/*de*
SQL	… WHERE SI_PARENTID IN (1851) AND SI_NAME LIKE ‘%de%’ …

You can use a recursive search using double asterisk. The following query will return all objects in Test and its subfolders. Note a nice field SI_ANCESTOR  that allows recursive search in folders:

URI	path://InfoObjects/Root Folder/Test/**
SQL	… WHERE SI_ANCESTOR IN (1851) …

You can combine

URI	path://InfoObjects/Root Folder/Test+/**
SQL	… WHERE (SI_ANCESTOR IN (1851) OR SI_ID IN (1851)) …

 

Cuid syntax

cuid://<list>/path

list = cuids | ids

cuids = cuids,cuid

ids = ids,id

You can either list IDs or CUIDs but you cannot mix them in the same list. For instance,

cuid://<AfRWaT5_131NlLLf5bRMLKY,AcgOFGfhCzJEg.VjnPaidmI>

cuid://<12,11>

You can use the path expression. For instance, the following URI query will find all objects in the folder with ID=1851 that contain ‘de’ in them.

URI	cuid://<1851>/*de*
SQL	… WHERE SI_PARENTID IN (1851) AND SI_NAME LIKE ‘%de%’ …

 

Query syntax

query://{sql}

For instance,

URI	query://{select * from ci_infoobjects where si_parentid=1851}?BPPSIZE=99999
SQL	SELECT TOP 99999 * FROM ci_infoobjects WHERE si_parentid=1851 ORDER BY SI_ID

 

Search

search://{pattern}?options

Patern is either a single word or quoted words

search://{Revenue}

search://{‘US’ ‘France’ ‘Germany’}

Options are separated by ampersand and have form name=value. Possible options are SearchName, SearchKeywords, SearchDescription, CaseSensitive, MatchAllWords, FindWithoutWords, MatchExact, IncludeInstances.

URI

search://{‘US Report’}?CaseSensitive=true&MatchAllWords=true

SQL

SELECT TOP 200 SI_CUID, SI_NAME, SI_PARENT_CUID, SI_DESCRIPTION, SI_CREATION_TIME, SI_KIND, SI_OWNER, SI_CORPORATE_CATEGORIES_ID, SI_PERSONAL_CATEGORIES_ID, SI_FILES, SI_INSTANCE, SI_SCHEDULE_STATUS, SI_LAST_SUCCESSFUL_INSTANCE_ID, SI_KEYWORD FROM CI_INFOOBJECTS WHERE (((SI_NAME LIKE ‘%[U][S][ ][R][e][p][o][r][t]%’) OR (SI_KEYWORD LIKE ‘%[U][S][ ][R][e][p][o][r][t]%’)) AND (SI_INSTANCE=0)) ORDER BY SI_ID

Code

import com.crystaldecisions.sdk.exception.SDKException;
import com.crystaldecisions.sdk.framework.*;
import com.crystaldecisions.sdk.occa.infostore.*;
import com.crystaldecisions.sdk.uri.*;
import com.crystaldecisions.sdk.exception.*;

public class Program {
    public static void main(String[] args) {
        IEnterpriseSession enterpriseSession = null;
        try {
            System.out.println("Connecting...");
            ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
            enterpriseSession = sessionMgr.logon("Administrator", "", 
                "localhost", "secEnterprise");
            IInfoStore infoStore = (IInfoStore) enterpriseSession.getService("InfoStore");
            PagingQueryOptions options = new PagingQueryOptions();
            String uri = "path://InfoObjects/Root Folder/Test/[si_kind='Webi' and si_instance=0]";
            IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(uri, options);
            String sql = pageInfo.getPageSQL();
            System.out.println(uri);
            System.out.println(sql);
        } catch (SDKException ex) {
            ex.printStackTrace();
        } finally {
            if (enterpriseSession != null) {
                enterpriseSession.logoff(); 
            }
        }
        System.out.println("Finished!");
    }
}