Invoking Crystal reports Using Stored Procedures from JSP

[Former Member]

HI Team,

I am unable to generate reports from JSP which is created using Crystal Reports having more than one stored procedure.Its works fine when it is having Single Stored Procedure.

Please help me to resolve the issue. I Have very less time on this.

Thanks

Penchal

[Former Member · Accepted solution]

Hi Penchal,

Can you please give more background of the issue.

Are the crystal reports managed or unmanaged.

Do you get any error message for reports more than one stored procedure?

Thanks,

Prithvi

[Former Member · Accepted solution]

Hi Prithiviraj,

The report is working fine in Crystal Report 2008 designer tool.

The problem is only while invoking from JSP.

Error:
HTTP Status 500 - javax.servlet.ServletException: com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Unexpected database connector error---- Error code:-2147467259 Error code name:failed

Following is the JSP code that I am using:

<%

String reportPath;

Object reportSource;

ReportClientDocument reportClientDocument;

ByteArrayInputStream byteArrayInputStream;

byte[] byteArray;

int bytesRead;

reportPath = request.getParameter("_reportName");

/*

* Instantiate ReportClientDocument and specify the Java Print Engine as the report processor.

* Open a rpt file and export to PDF.  Stream PDF back to web browser.

*/

reportClientDocument = new ReportClientDocument();

reportClientDocument.setReportAppServer(ReportClientDocument.inprocConnectionString);

reportClientDocument.open(reportPath, OpenReportOptions._openAsReadOnly);

Fields<IParameterField> parameterFields = reportClientDocument.getDataDefController().getDataDefinition().getParameterFields();

    System.out.println("Param Fields Size:"+parameterFields.size());

if (parameterFields.size() > 0) {

ParameterFieldController paramController = reportClientDocument.getDataDefController().getParameterFieldController();

for (int i = 0; i < parameterFields.size(); i++) {

    String paramName = parameterFields.getField(i).getName().trim();

    System.out.println("                    -          "+paramName);

    if(request.getParameter(paramName) != null) {

        paramController.setCurrentValue("", paramName, request.getParameter(paramName));

        System.out.println(paramName+":"+request.getParameter(paramName));

    }else {

        System.out.println("Param is Null:"+paramName+":"+request.getParameter(paramName));

        paramController.setCurrentValue("", paramName, "");

        }

    }

}

reportSource = reportClientDocument.getReportSource();

session.setAttribute("ReportSource", reportSource);

byteArrayInputStream = (ByteArrayInputStream) reportClientDocument

        .getPrintOutputController().export(ReportExportFormat.PDF);

response.reset();

response.setHeader("Content-disposition", "inline;filename=crreport.pdf");

response.setContentType("application/pdf");

byteArray = new byte[1024];

while((bytesRead = byteArrayInputStream.read(byteArray)) != -1) {

response.getOutputStream().write(byteArray, 0, bytesRead);

}

response.getOutputStream().flush();

response.getOutputStream().close();

reportClientDocument.close();

%>

Thanks,

Penchal

[Former Member · Accepted solution]

So as per the jsp, you are using unmanaged crystal reports and using Crystal reports for ecllipse jar files. Let me know if my understanding differs from yours?

Are you using the latest jars in your application?

You could find the latest jars from the below location

http://scn.sap.com/docs/DOC-29757

Download the runtime libraries and take a backup of your application, upgrade the jars and test the behaviour.

Thanks,

Prithvi

[Former Member · Accepted solution]

Hi,

It is very helpful solution, it is working for one report which has 2 procedure but not working for complex report.

The Complex report contain more than 10 sub-reports, shared variables, commands, multiple procedures. Few sub-reports are added at the Report header and Group header level.

While opening this report following error is thrown:

org.apache.jasper.JasperException: An exception occurred processing JSP page /ExportPdf.jsp at line 66

63: session.setAttribute("ReportSource", reportSource);

64:

65: byteArrayInputStream = (ByteArrayInputStream) reportClientDocument

66: .getPrintOutputController().export(ReportExportFormat.PDF);

67:

68: response.reset();

69:

Stacktrace:

org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:568)

org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:455)

org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)

org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)

javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

root cause

javax.servlet.ServletException: com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Unexpected database connector error---- Error code:-2147467259 Error code name:failed
     org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:912)
     org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:841)
     org.apache.jsp.ExportPdf_jsp._jspService(ExportPdf_jsp.java:153)
     org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
     javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
     org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
     org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
     org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
     javax.servlet.http.HttpServlet.service(HttpServlet.java:728)

Thanks,

Penchal

[Former Member · Accepted solution]

HI Prithviraj,

Its working.Its because of Old Jars. Thanks a lot for you reply.You saved my time.

Please help me in how to change Database connection dynamically at run time by giving some piece of code.

Thanks

Penchal

[Former Member · Accepted solution]

Refer to the below link. Samples are available at that link.

http://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports+Java++SDK+Samples#CrystalReportsJavaSDKSam...

Thanks,

Prithvi

[Former Member · Accepted solution]

Hi Prithviraj,

I am getting the following error for 2 reports after adding New Jars to the eclipse.

Error

Failed to bind columns.

Please help me on this.

Regards

Penchal

[Former Member · Accepted solution]

Do you observe these errors in the viewer(crystal report viewer) component.

If yes, enable the below in your web.xml to get the full stack trace from the viewer

<context-param>

  <param-name>crystal_exception_info</param-name>

  <param-value>long</param-value>

</context-param>

Does the reports run fine fron the CR Designer?

What is the difference in this report from others?

Thanks,

Prithvi

[Former Member · Accepted solution]

Hi Prithviraj,

The reports working fine in Crystal report. I am facing error while invoking with JSP.I am trying to debug Sub report by sub report I couldn't find any differences.

I found a problem at very simple snippet see the below.

if(isnull({SP_GET_NUMBEROFCOPIESDETAILS.PO_Box}) or ({SP_GET_NUMBEROFCOPIESDETAILS.PO_Box}=" ")) then

{SP_GET_NUMBEROFCOPIESDETAILS.STREET}+ chr(10)  + trim( {SP_GET_NUMBEROFCOPIESDETAILS.SUBURB}) + " " +{SP_GET_NUMBEROFCOPIESDETAILS.STATE_NAME}+" " +{SP_GET_NUMBEROFCOPIESDETAILS.POST_CODE}

else

{SP_GET_NUMBEROFCOPIESDETAILS.PO_Box} +chr(10)  + trim( {SP_GET_NUMBEROFCOPIESDETAILS.SUBURB}) +" " + {SP_GET_NUMBEROFCOPIESDETAILS.STATE_NAME} + " " + {SP_GET_NUMBEROFCOPIESDETAILS.POST_CODE}

The Error is listed below:

SEVERE: Servlet.service() for servlet [jsp] in context with path [/CLSCrystalReport] threw exception [javax.servlet.ServletException: com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Failed to bind columns.---- Error code:-2147467259 Error code name:failed] with root cause

com.crystaldecisions.reports.queryengine.RowsetException: Failed to bind columns.

    at com.crystaldecisions.reports.queryengine.Rowset.Af(SourceFile:1059)

    at com.crystaldecisions.reports.queryengine.Rowset.Ag(SourceFile:998)

    at com.crystaldecisions.reports.queryengine.Rowset.z9(SourceFile:977)

    at com.crystaldecisions.reports.queryengine.Rowset.bL(SourceFile:533)

    at com.crystaldecisions.reports.queryengine.Rowset.zR(SourceFile:245)

    at com.crystaldecisions.reports.queryengine.RowsetBase.zF(SourceFile:162)

    at com.crystaldecisions.reports.queryengine.QueryInfo.vJ(SourceFile:261)

    at com.crystaldecisions.reports.datafoundation.DFQuery.for(SourceFile:628)

    at com.crystaldecisions.reports.datalayer.a.do(SourceFile:1621)

    at com.crystaldecisions.reports.datalayer.a.a(SourceFile:1404)

    at com.crystaldecisions.reports.dataengine.m.b(SourceFile:334)

    at com.crystaldecisions.reports.dataengine.j.b(SourceFile:515)

    at com.crystaldecisions.reports.dataengine.m.o(SourceFile:408)

    at com.crystaldecisions.reports.dataengine.m.a(SourceFile:173)

    at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:114)

    at com.crystaldecisions.reports.dataengine.ContextNode.a(SourceFile:95)

if you need any additional information please let me know.

Thanks

Penchal

[Former Member · Accepted solution]

Hi Prithviraj,

Can you provide some clear Idea to change Database at run time please.

final String DBUSERNAME = "";

final String DBPASSWORD = "";

final String TRUSTEDCON = "";

final String PREQESERVERNAME = "";

final String SERVERTYPE = "JDBC (JNDI)";

final String DATABASE_DLL = "crdb_jdbc.dll";

final String DATABASE = "ORCL";

final String DBCLASSNAME = "oracle.jdbc.driver.OracleDriver";

final String USEJDBC = "true";

final String DATABASE_NAME = "ORCL";

final String SERVERNAME = "";

final String CONNECTIONURL = "j";

final String SERVER = "";

After providing all the above details, then it is prompting for DB password in the browser whats wrong in the approach?

Thanks

Penchal