Skip to Content
0
Former Member
Nov 11, 2008 at 01:10 AM

Web Services SDK unable to process reports with parameterized SQL Commands

125 Views

Hi,

Iu2019ve encountered what I believe to be a bug with the Web Services SDK.

Summary: If a parameterized SQL Command or Stored Procedure is used as the report data source then the web services SDK will fail with the following exception:

"Failed to retrieve database logon info. Information is needed before this report can be processed. (WRE 02517)"

If a SQL Command is not used and the parameterized data filtering is done via the formulas then the web services SDK does not throw an exception and the report can be rendered. i.e., formulas created via the menu items u201CSelect Expertu201D or u201CSelection Formulau201D.

I can work-around the problem by not using a SQL Commands BUT this only really works well for simple reports. For more complex reports that use Stored Procedures or custom SQL u2018Commandsu2019 using formulas is not always an option.

(For me this is a show-stopper issue because our Stored Procedures and Custom SQL Commands already exist u2026 we donu2019t want to have to recreate them and parameterize them via formulas)

Further details if anyone would like to reproduce:

Environment:

-Crystal Reports 2008 (SP1)

-Crystal Reports Server 2008.

-Oracle 10g (although I think database vendor and version are irrelevant in order to reproduce the problem)

-Web Service SDK DLL Version (12.0.2000.683). This is the latest Web Services SDK shipped with Crystal Reports 2008 SP1.

Steps to reproduce the problem:

[1] Create blank report

[2] Create SQL Command using Database Expert menu item

[3] Use the u2018Add Commandu2019 option from the Database Expert dialog

[4] In the Add Command dialog window create a new parameter in the parameter panel: For example: TEST_PARAM

[5] In the Add Command dialog window create simple SQL that returns data and is filtered using the parameter TEST_PARAM. For example:

SELECT value FROM table WHERE value = {?TEST_PARAM}

[6] Save SQL Command

[7] Add u2018valueu2019 data to the report.

[8] Test report via preview.

[9] Deploy report to Crystal Reports Server

[10] utilize the ASP.NET code snippet below in ASP.NET to trigger the Web Service Exception.

NOTE: When using a parameterized SQL Command or Stored Procedure the code will always fail at this line:

doc = reportEngine.GetDocumentInformation(docRef, retrieveInfo, null, null, null);

Code Snippet:

protected void Page_Load(object sender, EventArgs e) {
 
    // Document path
    string crPath = FixedPaths.InfoObjects.ROOT_FOLDER + "/302782727/302782727_sp@SI_ID";
 
    // Enterprise Web Services URL and Login Credentials.
    string boSessionURL = "http://tueda-bexir2s2:8080/dswsbobje/services/Session";
 
    string boCMSName    = "TUEDA-BEXIR2S2";
    string boAuthType   = "secEnterprise";
    string boUsername   = "Administrator";
    string boPassword   = "";
 
    Session    boSession    = null;
    try {
 
        // Logon to Enterprise
 
        EnterpriseCredential boCredential = new EnterpriseCredential();
        boCredential.Domain   = boCMSName;
        boCredential.AuthType = boAuthType;
        boCredential.Login    = boUsername;
        boCredential.Password = boPassword;
 
        boSession =  new Session(new Connection(boSessionURL));
        boSession.Login(boCredential);
 
        // Retrieve Document CUID
 
        BIPlatform biPlatform
            = BIPlatform.GetInstance(boSession, boSession.GetAssociatedServicesURL("BIPlatform")[0]);
 
        ResponseHolder rh = biPlatform.Get(crPath, null);
 
        string doc_cuid = rh.InfoObjects.InfoObject[0].CUID;
 
        // Retrieve ReportEngine service.
 
        ReportEngine reportEngine
            = ReportEngine.GetInstance(boSession, boSession.GetAssociatedServicesURL("ReportEngine")[0]);
 
        // Refresh document and retrieve the prompts.
 
        RetrieveMustFillInfo retrieveInfo = new RetrieveMustFillInfo();
        retrieveInfo.RetrievePromptsInfo = new RetrievePromptsInfo();
 
        DocumentInformation doc
                = reportEngine.GetDocumentInformation(doc_cuid, null, new Action[] { new Refresh() }, null, null);
        string docRef = doc.DocumentReference;
 
 
        doc = reportEngine.GetDocumentInformation(docRef, retrieveInfo, null, null, null);
        docRef = doc.DocumentReference;
 
        int promptCount = doc.PromptInfo == null ? 0 : doc.PromptInfo.Length;
 
        FillPrompt[] fillPromptList = new FillPrompt[promptCount];
 
        int i = 0;
        foreach(PromptInfo promptInfo in doc.PromptInfo) {
            fillPromptList<i> = new FillPrompt();
            fillPromptList<i>.ID = promptInfo.ID;
 
            DiscretePromptValue dvalue1, dvalue2;
 
            dvalue1 = new DiscretePromptValue();
            dvalue1.Value = "5";
            fillPromptList<i>.Values = new PromptValue[] {dvalue1};
            i++;
            Response.Write(promptInfo.ID + "<BR>");
        }
 
        FillPrompts fillPrompts = new FillPrompts();
        fillPrompts.FillPromptList = fillPromptList;
 
        doc = reportEngine.GetDocumentInformation(docRef, null, new Action[] {fillPrompts}, null, null);
        docRef = doc.DocumentReference;
 
        retrieveInfo = new RetrieveMustFillInfo();
        retrieveInfo.RetrieveDBLogonInfo = new RetrieveDBLogonInfo();
 
        doc = reportEngine.GetDocumentInformation(docRef, retrieveInfo, null, null, null);
        docRef = doc.DocumentReference;
 
        DBLogonInfo[] dbLogonInfos = doc.DBLogonInfos;
 
        FillDBLogon[] dbLogon = new FillDBLogon[1];
		dbLogon[0] = new FillDBLogon();
        dbLogon[0].Name = dbLogonInfos[0].Name;
        dbLogon[0].UserName = "vantech";
        dbLogon[0].Password = "vantech";
       
       
		FillDBLogons  dbLogons  = new FillDBLogons();
		dbLogons.FillDBLogonList = dbLogon;
 
        doc = reportEngine.GetDocumentInformation(docRef, null, new Action[] { dbLogons} , null, null);
        docRef = doc.DocumentReference;
 
        RetrieveData retrieveData = new RetrieveData();
        RetrieveView retrieveView = new RetrieveView();
 
        ViewSupport viewSupport = new ViewSupport();
        viewSupport.OutputFormat = OutputFormatType.HTML;
        viewSupport.ViewType = ViewType.CHARACTER;
        viewSupport.ViewMode = ViewModeType.REPORT_PAGE;
 
        retrieveView.ViewSupport = viewSupport;
        retrieveData.RetrieveView = retrieveView;
 
        doc = reportEngine.GetDocumentInformation(docRef, null, null, null, retrieveData);
        docRef = doc.DocumentReference;
        String content = ((CharacterView) doc.View).Content;
 
        // Close document connection.
        doc = reportEngine.GetDocumentInformation(docRef, null, new Action[] { new Close() }, null, null);
 
        // Output view to web browser.
        Response.Write(content);
 
 
 
    } finally {
 
        if(boSession != null)
            boSession.Logout();
 
    }
 
}

There is another thread on this issue here:

https://www.sdn.sap.com/irj/scn/thread?threadID=1055065&tstart=0

I hope someone can point me in the right direction on this.

Thanks,

GR