Category Archives: SDK

BusinessObjects SDK

How to Upload an Excel File to CMS Programmatically Using BO Java SDK

static public void uploadFile(IInfoStore infoStore, String filename, String title, int parentId)
    throws SDKException
{
    IInfoObjects infoObjects = infoStore.newInfoObjectCollection();
    IInfoObject newInfoObject = infoObjects.add(CeKind.EXCEL);
    newInfoObject.setTitle(title);
    newInfoObject.getFiles().addFile(filename);
    newInfoObject.setParentID(parentId);
    infoStore.commit(infoObjects);
}

Example of Token Generation

The following JSP page generates a token and logs on to the InfoView with the token.

The following file logonAuto.jsp can be placed into the folder:

[BOInstallationFolder]\Tomcat55\webapps\InfoViewApp

logonAuto.jsp

<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ page import="com.crystaldecisions.sdk.exception.SDKException"%>
<%@ page import="com.crystaldecisions.sdk.framework.CrystalEnterprise"%>
<%@ page import="com.crystaldecisions.sdk.framework.IEnterpriseSession"%>
<%@ page import="com.crystaldecisions.sdk.framework.ISessionMgr"%>
<%@ page import="com.crystaldecisions.sdk.occa.security.ILogonTokenMgr"%>
<%
    String url = "http://servername:8080";
    IEnterpriseSession enterpriseSession = null;
    String token = "";
    try {
        ISessionMgr sessionMgr = CrystalEnterprise.getSessionMgr();
        enterpriseSession = sessionMgr.logon("Administrator",
        "", "localhost", "secEnterprise");
        ILogonTokenMgr logonTokenMgr = enterpriseSession.getLogonTokenMgr();
        token = logonTokenMgr.createLogonToken("", 60, 1);
    } catch (SDKException ex) {
        ex.printStackTrace();
    } finally {
        if (enterpriseSession != null)
            enterpriseSession.logoff();
    }
%>
<html>
    <head>
        <meta http-equiv="REFRESH"
        content="0;url=<%=url%>/InfoViewApp/logon/start.do?ivsLogonToken=<%=token%>"/>
    </head>
    <body>
    </body>
</html>

BO XI 3.1 SP3 issue related to daylight savings time change

There is a quite serious issue in BO XI 3.1 SP3 related to the daylight savings time change. See the SAP note 1448881 for details.

After time change, BO generates thousands of failed instances for scheduled reports.  The CMS database is growing very fast. BO system becomes very slow and stops working because of lack of space in tablespace or on the disk.

The quickest way to check if you have the issue is to connect to CMS database and query the number of rows in cms_infoobjects6.

SELECT Count(*) FROM CMS_INFOOBJECTS6

The normal amount of rows is usually less then 10 thousands. If there are more then 100 thousands rows, the system is probably affected by the issue.

To resolve the issue the reports should be rescheduled and the failed instances should be removed (see the SAP note for details). The error is fixed in FP3.6.

Continue reading

Fun with Query Builder

I cannot explain this.

SELECT SI_ID, SI_NAME, SI_CUID FROM CI_INFOOBJECTS WHERE SI_ID=23
1/1
Properties
SI_NAME Root Folder
SI_CUID ASHnC0S_Pw5LhKFbZ.iA_j4
SI_ID 23
SELECT SI_ID, SI_NAME, SI_CUID, SI_PARENTID, SI_PARENT_CUID
FROM CI_INFOOBJECTS WHERE SI_PARENTID=23
1/10
Properties
SI_NAME Report Conversion Tool
SI_CUID AY9zJ8BgaF9OucZ2h2slcJM
SI_PARENT_CUID ASHnC0S_Pw5LhKFbZ.iA_j4
SI_PARENTID 0
SI_ID 123

Why the hell SI_PARENTID=0 in the result if SI_PARENTID=23 is in the query restriction?

How to Change SQL in Webi Document Using Java RE SDK

This post briefly describes how to change SQL in Webi document. The following code will work for a Webi document with one simple query (say one object, without subqueries, combined queries, etc):

DocumentInstance widoc = wiRepEngine.openDocument(31223); // 31223 is ID of the webi document
SQLDataProvider sdp = (SQLDataProvider)(widoc.getDataProviders().getItem(0)); 
SQLSelectStatement sss = (SQLSelectStatement)(sdp.getSQLContainer().getChildAt(0));
sss.setSQL("SELECT * FROM (" + sss.getSQL() +") A");
sdp.validateSQL();
sdp.changeSQL();
widoc.save();
widoc.closeDocument();

If the document contained the only object “Resort” from universe “Island Resorts Marketing”, the query was:

SELECT
  Resort.resort
FROM
  Resort

After applying the script the query will become:

Note that this is now a custom query.

Not clear?

Try to start from this: https://bukhantsov.org/2011/08/getting-started-with-businessobjects-java-sdk/ 🙂

Importing Linked Universes

Problem

Let’s assume there are two universes: Base and Derived. The Derived universe is linked to Base (Base does not have any link to other universes).

If you are importing Derived and then Base using .NET Designer SDK – everything is ok.

Universe derivedUnv = application.Universes.OpenFromEnterprise("", "Derived", false);
Universe baseUnv = application.Universes.OpenFromEnterprise("", "Base", false);

If you try to import Base and then Derived, import of the Derived universe will fail with the error: ‘Base’ is already open. Please close it and try again.

Let’s assume that we need to import a bunch of universes. The derived universes should be imported first, and the base universes should be imported last. How to determine if the universe is a base universe?

“Solution”

For me the most simple and working solution has been to avoid problem. I usually process universes one-by-one, i.e. I do not import many universes.

Solution

A solution is to query CMS to get the list of base universes:

List<String> list = new List<string>();

CrystalDecisions.Enterprise.InfoObjects unv_objects =
    infoStore.Query("SELECT * FROM CI_APPOBJECTS WHERE SI_KIND = 'Universe' "
                   +"AND SI_DERIVEDUNIVERSE.SI_TOTAL>0");

foreach (CrystalDecisions.Enterprise.InfoObject unv_object in unv_objects)
{
    list.Add(unv_object.CUID);
}

Now you can import the derived universes:

foreach (StoredUniverse unv in src_folder.StoredUniverses)
{
    if (!list.Contains(unv.CUID))
    {
        application.Universes.OpenFromEnterprise(src_path, unv.Name, false);
    }
}

and then the base universes:

foreach (StoredUniverse unv in src_folder.StoredUniverses)
{
    if (list.Contains(unv.CUID))
    {
        application.Universes.OpenFromEnterprise(src_path, unv.Name, false);
    }
}

Details

Let me know if you need details or complete code..

Compile and run BO SDK Java tool from Command Line

You can compile and run java tools without installation of IDE. You need JDK and BO SDK libraries. On BO server (with default settings), JDK can be found in

C:\Program Files (x86)\Business Objects\javasdk\bin

Batch to setup class path (env.bat)

@echo off
set libs=C:\Program Files (x86)\Business Objects\common\4.0\java\lib
set cp=%libs%/boconfig.jar
set cp=%cp%;%libs%/cecore.jar
set cp=%cp%;%libs%/celib.jar
set cp=%cp%;%libs%/cesdk.jar
set cp=%cp%;%libs%/cesession.jar
set cp=%cp%;%libs%/corbaidl.jar
set cp=%cp%;%libs%/ebus405.jar
set cp=%cp%;%libs%/javacsv.jar
set cp=%cp%;%libs%/jtools.jar
set cp=%cp%;%libs%/logging.jar
set cp=%cp%;%libs%/rebean.common.jar
set cp=%cp%;%libs%/rebean.jar
set cp=%cp%;%libs%/rebean.wi.jar
set cp=%cp%;%libs%/SL_plugins.jar
set cp=%cp%;%libs%/wilog.jar
set cp=%cp%;%libs%/external/xpp3.jar
set cp=%cp%;%libs%/external/xpp3_min.jar;
@echo on
set cp="%cp%"

Test program (Program.java)

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

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");
            // something useful
        } catch (SDKException ex) {
            ex.printStackTrace();
        } finally {
            if (enterpriseSession != null)
                enterpriseSession.logoff();    
        }
        System.out.println("Finished!");
    }
}

Compile and run

env.bat
javac -cp %cp% Program.java
java Program

How to retrieve SQL query of QaaWS using BO Java RE SDK

QaaWS is stored as Webi document on the server

To check that QaaWS is represented as Webi document, you can open Query Builder and run the following SQL:

SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='QaaWS'

The property SI_FILES describes the location of the files corresponding to QaaWS and it is actually the WID file:

The following code prints queries for all QaaWS found in CMS:

IInfoObjects objs = infoStore.query("SELECT * FROM CI_APPOBJECTS WHERE SI_KIND='QaaWS'");
System.out.println("Number of QaaWS found: " + objs.size());
for (Object obj : objs) {
   IInfoObject infoObj = (IInfoObject)obj;
   System.out.println("------ " + infoObj.getTitle() + " ------");
   DocumentInstance widoc = wiRepEngine.openDocument(infoObj.getID());
   printQuery(widoc);
   widoc.closeDocument();
}

The function printing SQL of Webi document

public static void printQuery(DocumentInstance widoc) {
   DataProviders dps = widoc.getDataProviders();
   for (int i = 0; i < dps.getCount(); ++i) {
      DataProvider dp = (DataProvider)dps.getItem(i);
      if (dp instanceof SQLDataProvider) {
         SQLDataProvider sdp = (SQLDataProvider)dp;
         ArrayList<TreeNode> nodes = getListOfTreeNodes(sdp.getSQLContainer(), true);
         for (TreeNode node : nodes) {
            if (node instanceof SQLSelectStatement) {
               SQLSelectStatement query = (SQLSelectStatement)node;
               System.out.println(query.getSQL());
            }
         }
      }
   }
}

Auxiliary functions

The following function finds the list of all nodes in the tree. It might be more convenient then writing recursion:

public static ArrayList<TreeNode> getListOfTreeNodes(TreeNode root, boolean onlyLeaves) {
   ArrayList<TreeNode> nodes = new ArrayList<TreeNode>();
   treeNodeTraversal(root, nodes, onlyLeaves);
   return nodes;
}

private static void treeNodeTraversal(TreeNode node,
   ArrayList<TreeNode> nodes,
   boolean onlyLeaves)
{
   if (!onlyLeaves || node.isLeaf()) {
      nodes.add(node);
   }
   for (int i = 0; i < node.getChildCount(); ++i) {
      treeNodeTraversal(node.getChildAt(i), nodes, onlyLeaves);
   }
}

Source Code

How to Create a Folder in CMS using BO Java SDK

public static void createFolder(
	IInfoStore infoStore,
	String name,
	String description,
	String parentCUID)
		throws SDKException
{
	IPluginMgr pluginMgr = infoStore.getPluginMgr();
	IPluginInfo plugin = pluginMgr.getPluginInfo(CeKind.FOLDER);
	IInfoObjects newInfoObjects = infoStore.newInfoObjectCollection();
	newInfoObjects.add(plugin);
	IInfoObject infoObject = (IInfoObject)newInfoObjects.get(0);
	infoObject.setTitle(name);
	infoObject.setDescription(description);
	infoObject.properties().setProperty(CePropertyID.SI_PARENT_CUID, parentCUID);
	infoStore.commit(newInfoObjects);
}

Handling Ctrl+C in a C# console tool (Designer SDK)

Handling exceptions is not enough to make clean up (quit Designer). The following code handles Ctrl+C.

using System;
using Designer;
namespace ConsoleApplication1
{
    class Program
    {
        delegate void CleanUpMethod();

        static void Main(string[] args)
        {
            Application application = new Application();

            CleanUpMethod cleanUp = delegate { application.Quit(); };
            Console.CancelKeyPress += delegate { cleanUp(); };

            try
            {
                application.LogonDialog();

                // ... some code here ...
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                cleanUp();
            }
        }
    }
}