I need to query the CE system databases, where to get the data

[eo]

Hi

CE10, SQL env.

I need to obtain a list of all our CE users, and their bespoke information which we have added on an ad hoc basis as time goes by.

So if the user name is "jpb", we have added a name of "joe Public" into the relevant field under user properties. Where in the system databases is the latter bespoke type of info held? All I can see are many columns populated with 'binary'. Is that perhaps it, and if so, are there anyway we can interrogate this for logical output?

Many thanks,

EO
Hertfordshire, England

 

[Turkbear]

Hi,
To view the data stored in the CE database you need to use the Query Builder tool ( on the User Launchpad) or write similar custom code to access the data through the InfoStore Object..( Look at the CE_SDK help file of review how the sample code does it - if you do not have the asp samples you can download them from Business Objects support).

It is not directly accessable through standard database query methods ( as you have noticed, most of the properties are stored in a binary blob and need special handling to be decoded).






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[yajmire]

EO
I had written a code to get results of tables that user can access from CE and added those to launch pages. I have copied details from this forum and Business Object.
I am not sure if following can help you. Try changing SQL query to suit your needs.

Thanks


Yogesh


------------------------------------------------------

using CrystalDecisions.Enterprise;
using CrystalDecisions.ReportAppServer.ClientDoc;
using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.ReportAppServer.ReportDefModel;
using CrystalDecisions.ReportAppServer.ObjectFactory;


protected void ValidateUser()
{
//This function authenticates if user has access to CE otherwise redirect to Invalid User pAge.
// Still need to add logic to check if user has access to EPC folders only.
SessionMgr SesMgr = new SessionMgr();
EnterpriseSession myEntSes;
string CMS, Authorization;

try
{
CMS = System.Configuration.ConfigurationSettings.AppSettings.Get("CMSServer");
Authorization = System.Configuration.ConfigurationSettings.AppSettings.Get("Authorization");
// Logon to CMS server using Windows AD. Passing null name and password.
myEntSes = SesMgr.Logon("", "", CMS, Authorization);
}
catch (Exception e)
{
Response.Write(e.Message + " , Source: " + e.Source);
Response.Redirect("InvalidUsr.aspx");
}
}


protected string getCERptList(int FldrNbr)
{
// This function retrurns list of reports from CE folder in Public string seperated by ;

InfoStore myInfoStore;
int iPos ;
string CMS, Authorization;
string qryStr;
string RptList;

CMS = System.Configuration.ConfigurationSettings.AppSettings.Get("CMSServer");
Authorization = System.Configuration.ConfigurationSettings.AppSettings.Get("Authorization");

// Logon to CMS server using Windows AD. Passing null name and password.
myEnterpriseSession = mySessionMgr.Logon("", "", CMS, Authorization);
myEnterpriseService = myEnterpriseSession.GetService("InfoStore");
myInfoStore = new InfoStore(myEnterpriseService);
myEnterpriseService = myEnterpriseSession.GetService("RASReportFactory");
Object returnedObjectFromEnterpriseService = myEnterpriseService.Interface;
ReportAppFactory myReportAppFactory = (ReportAppFactory )returnedObjectFromEnterpriseService;
//qryStr = "Select * From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_PARENTID = 3196 ORDER BY SI_NAME ASC";
qryStr = "Select * From CI_INFOOBJECTS WHERE SI_PROGID = 'CrystalEnterprise.Report' and SI_PARENTID = " + FldrNbr + " ORDER BY SI_NAME ASC";
// GEt the list of reports from InfoObjects
InfoObjects myInfoObjects = myInfoStore.Query(qryStr);
iCntRpt = myInfoObjects.Count;

// Response.Write(iCntRpt);
iPos = 0;
RptList = "";
foreach (InfoObject myInfoObject in myInfoObjects)
{
//Response.Write("<br>" + myInfoObject.ID + ":" + myInfoObject.ParentID + ":" + myInfoObject.Description + ":" + myInfoObject.Title);
// assign the values to Public array
//RptArray[iPos] = myInfoObject.Description;
RptList = RptList + ";" + myInfoObject.Title;
iPos += 1;
}
return RptList;
}