Skip to Content
0
Former Member
May 01, 2012 at 11:42 PM

running a report based on stored proc

50 Views

Hi all,

I have 2 reports (both without any parameters). 1st report is created using SELECT statement while the 2nd report is created using a Stored Proc. I generated JSPs for each of these 2 reports in Eclipse where I am setting runtime database credentials. I deploy them in my web application and test them. The 1st report is retrieving data but the 2nd report is not retrieving data. My database is SQL Server 2008 R2. The JSPs code is same for both reports. This is the JSP code for 2nd report:

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="ISO-8859-1" %><%@ page import="com.businessobjects.samples.CRJavaHelper,com.crystaldecisions.report.web.viewer.CrystalReportViewer,com.crystaldecisions.sdk.occa.report.application.OpenReportOptions,com.crystaldecisions.sdk.occa.report.application.ReportClientDocument,com.crystaldecisions.sdk.occa.report.lib.ReportSDKExceptionBase" %><%try {          String reportName = "SP_noparm.rpt";          ReportClientDocument clientDoc = (ReportClientDocument) session.getAttribute(reportName);     if (clientDoc == null) {          clientDoc = new ReportClientDocument();          clientDoc.setReportAppServer(ReportClientDocument.inprocConnectionString);          // Open report          clientDoc.open(reportName, OpenReportOptions._openAsReadOnly);          // ****** BEGIN SET RUNTIME DATABASE CREDENTIALS ****************            {               String connectString = "jdbc:jtds:sqlserver://DBS2008R2/TestDB;tds=8.0;lastupdatecount=true";               String driverName = "net.sourceforge.jtds.jdbc.Driver";               String JNDIName = "TestDS";               String userName = "test_dbo";              // TODO: Fill in database user               String password = "test";          // TODO: Fill in password               // Switch all tables on the main report and sub reports               CRJavaHelper.changeDataSource(clientDoc, userName, password, connectString, driverName, JNDIName);               // logon to database               CRJavaHelper.logonDataSource(clientDoc, userName, password);          }          // ****** END SET RUNTIME DATABASE CREDENTIALS ****************          // Store the report document in session          session.setAttribute(reportName, clientDoc);          }     // ****** BEGIN CONNECT CRYSTALREPORTPAGEVIEWER SNIPPET ****************       {          // Create the CrystalReportViewer object          CrystalReportViewer crystalReportPageViewer = new CrystalReportViewer();          String reportSourceSessionKey = reportName+"ReportSource";          Object reportSource = session.getAttribute(reportSourceSessionKey);          if (reportSource == null)          {                              reportSource = clientDoc.getReportSource();                              session.setAttribute(reportSourceSessionKey, reportSource);          }                    // set the reportsource property of the viewer                    crystalReportPageViewer.setReportSource(reportSource);                    // Process the report                    crystalReportPageViewer.processHttpRequest(request, response, application, null);           }          // ****** END CONNECT CRYSTALREPORTPAGEVIEWER SNIPPET ****************} catch (ReportSDKExceptionBase e) {          out.println(e);}%>

I debug the SQL using the SQL Server Pofiler and I can see that 1st report is issuing the SELECT statement but the 2nd report never called the stored proc. Any reason? Do the CRJavaHelper.changeDataSource method do not work with stored procs?

this is the stored proc on which the 2nd report 'SP_noparm.rpt' is based on:

CREATE PROCEDURE [dbo].[test_SP_noparm]   AS  BEGIN            -- SET NOCOUNT ON added to prevent extra result sets from            -- interfering with SELECT statements.            SET NOCOUNT ON;        -- Insert statements for procedure here            SELECT employeeid, firstname, lastname from employee   END