Could not add Stored Procedure to a Report Eclipse

[Former Member]

I am using Crystal Reports for Eclipse. In which I have a requirement to add Stored Procedure to the report, so in short.. when the report is called it executes the stored procedure and loads the report based on the parameters.

From the Data Source Explorer I can add tables / Views .. but I am not able to add a stored procedure (I use crystal reports with visual studio and could be able to add the stored procedure).. in this below picture I am trying to add the procudure name APS_RESULTSET but there is no Crystal Reports option when I right click on the stored procedure. (is there any crystal reports eclipse patch is required to add this stored procedure?)

Since I could not added the stored procedure and desgin the report in java.. I used visual studio and designed the report..! and I copied the same report in to my java project and try to change the data source but could not change the datasource as the stored procedure is not listing in the available objects please see below figure..!

So I am wondering of any of you have come across to this kind of situation and had a solution.

Thanks,

[immanuels · Accepted solution]

I have exactly the same problem, i am using the latest crystal report for eclipse sp 18 (Full Eclipse), and cannot add sql server stored procedure as data source.

is there any solution ? thanks

[immanuels · Accepted solution]

I think i have found the solution,  instead selecting stored procedure from data explorer,

just create sql query with right click on project, create new sql file, write the query to execute stored procedure, after that right click and select crystal report > add to report

hope it helps.

[Former Member · Accepted solution]

Hi,

I'm having the same problem and your solution helped me solve it, thanks!

I still have a problem that I can't figure out how to solve,

how can I define the stored procedure parameters?

have you used a stored procedure with parameters?

best regards!

[former_member183750]

Not sure what version of CR for Eclipse you are working with, but the latest is here:

SAP BusinessObjects - SAP Crystal Reports, Version For Eclipse Download

- Ludek

[Former Member]

Thank you for the response...! I am also using the same version. I did manually copied the files in Plugin and Features to my spring folder.

Could you please let me know, if there is any specific version of crystal reports to Spring or CR for Eclipse is sufficient enough to use with Spring?

Also I am experiencing one more thing, I have a test environment and a production environment..

If I test the report from my spring application (having a stored procedure attached to it) by supplying the test credentials it runs .. but if I change the data source to production then it says error in formula (that means that it cannot connect to the server). However the both the environment work well in visual studio.

So not sure on what to do.

[former_member183750]

I asked a couple of guys re. the possibility of Spring being the issue. Their take on this is that this should not matter.

Other comments:

How is he changing the datasource at runtime?

He should be using a Procedure object and not a Table object.  Especially if the Stored proc has a prompt.  The Procedure object will allow setting a prompt value when changing datasoruces

- Ludek

[Former Member]

Below is my Code, a simple JSP page calling a report with 4 parameters.

It works fine with my testing environment but if i change the database parameters (HOST, SID, USERNAME, Password) then it fails..!

Like I mentioned the stored procedure would not showup in the available objects, when I try to change the datasource at design time ...!

Again thank you for trying to help me.,

-------------START--------------------------------------------------------

<%@page import="com.crystaldecisions.sdk.occa.report.reportsource.IReportSource"%>
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<%//Crystal Reports for Eclipse Version 2 imports.%>
<%@ page import="com.crystaldecisions.sdk.occa.report.application.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.data.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.document.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.definition.*"%>
<%@ page import="com.crystaldecisions.sdk.occa.report.lib.*" %>
<%@page import="com.crystaldecisions.report.web.viewer.*"%>
<%@page import="java.util.*"%>

<%@page import ="java.sql.*" %>
<%@page import ="java.sql.CallableStatement" %>
<%@page import ="java.sql.DriverManager" %>
<%@page import ="java.sql.Connection" %>
<%@page import ="java.sql.SQLException" %>
<%@page import ="oracle.jdbc.*" %>

<%
      final String REPORT_NAME = "ScanReport.rpt";
     
      ReportClientDocument boReportClientDocument = new ReportClientDocument();
      boReportClientDocument.open(REPORT_NAME, OpenReportOptions._discardSavedData);
     
      switch_tables(boReportClientDocument.getDatabaseController(), boReportClientDocument);
      IStrings subreportNames = boReportClientDocument.getSubreportController().getSubreportNames();
     
       System.out.println(subreportNames.size());
     for (int i = 0; i < subreportNames.size(); i++) {
      ISubreportClientDocument subreportClientDoc = boReportClientDocument.getSubreportController().getSubreport(subreportNames.getString(i));
           switch_tables(subreportClientDoc.getDatabaseController(), boReportClientDocument);
     }
   
    boReportClientDocument.getDatabaseController().logon("schemaname", "password");
   
      Object reportSource = boReportClientDocument.getReportSource();
     CrystalReportViewer viewer = new CrystalReportViewer();

     Fields reportFields = boReportClientDocument.getDataDefinition().getParameterFields();
     Fields fields = new Fields();
    
   
     ParameterField pfield4 = new ParameterField();
     Values vals4 = new Values();
     ParameterFieldDiscreteValue pfieldDV4 = new ParameterFieldDiscreteValue();
     pfield4.setName("user");
     pfield4.setReportName("");
     pfieldDV4.setValue("HINES");
     vals4.add(pfieldDV4);
     pfield4.setCurrentValues(vals4);

     ParameterField pfield1 = new ParameterField();
     Values vals1 = new Values();
     ParameterFieldDiscreteValue pfieldDV1 = new ParameterFieldDiscreteValue();
     pfield1.setName("operatorname");
     pfield1.setReportName("");
     pfieldDV1.setValue("");      
      //pfieldDV1.setValue("FRED RASH");
     vals1.add(pfieldDV1);
     pfield1.setCurrentValues(vals1);
     
     ParameterField pfield2 = new ParameterField();
     Values vals2 = new Values();
     ParameterFieldDiscreteValue pfieldDV2 = new ParameterFieldDiscreteValue();
     pfield2.setName("P_FROM_DATE");
     pfield2.setReportName("");
     pfieldDV2.setValue("07/30/2000");
     vals2.add(pfieldDV2);
     pfield2.setCurrentValues(vals2);

     ParameterField pfield3 = new ParameterField();
     Values vals3 = new Values();
     ParameterFieldDiscreteValue pfieldDV3 = new ParameterFieldDiscreteValue();
     pfield3.setName("P_TO_DATE");
     pfield3.setReportName("");
     pfieldDV3.setValue("8/29/2013");
     vals3.add(pfieldDV3);
     pfield3.setCurrentValues(vals3);  
 
     fields.add(pfield2);
     fields.add(pfield3);
     fields.add(pfield4);
     fields.add(pfield1);

viewer.setSelectionFormula("");
viewer.setReportSource(reportSource);
viewer.setParameterFields(fields);
viewer.setEnableParameterPrompt(true);
viewer.setEnableLogonPrompt(true); 
//viewer.setDatabaseLogonInfos(connectionInfos);
viewer.setHasViewList(false);
viewer.setDisplayGroupTree(false);
viewer.setEnableParameterPrompt(false);
viewer.setEnableDrillDown(false);
viewer.setHasToggleGroupTreeButton(false);
viewer.setPrintMode(CrPrintMode.ACTIVEX);
viewer.setLeft(10);
viewer.setOwnPage(true);
viewer.setReuseParameterValuesOnRefresh(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);

viewer.dispose();
%>

<%!
private  void switch_tables(DatabaseController databaseController, ReportClientDocument reportClientDoc)
  {
      try
      {  
      ConnectionInfos connectionInfos = (ConnectionInfos)databaseController.getConnectionInfos(null);
      IConnectionInfo newConnectionInfo = getConnectionInfo();
      IConnectionInfo oldConnectionInfo = connectionInfos.getConnectionInfo(0);
      databaseController.replaceConnection(oldConnectionInfo, newConnectionInfo, null, 0);
     
      Tables tables = databaseController.getDatabase().getTables();
      for (int i = 0; i < tables.size(); i++)
      {
              ITable table = tables.getTable(i);
             table.setName(table.getName());
              System.out.println("Table Name -- " + table.getName());
              System.out.println("Table Alias -- " + table.getAlias());
             
              table.setAlias(table.getAlias());

              if (table.getQualifiedName().indexOf("Command") < 0) {
                   table.setQualifiedName("schemaname."+table.getName());
               }
              }
      }
      catch(Exception ex)
      {
            System.out.println(ex.getMessage());
      } 
      }
%>

<%!
private IConnectionInfo getConnectionInfo()
{
IConnectionInfo newConnectionInfo = new ConnectionInfo();
newConnectionInfo.setKind(ConnectionInfoKind.SQL);
PropertyBag newProperties = new PropertyBag();
newProperties.put("Connection URL", "jdbc:oracle:thin:@host:1521:SID");
newProperties.put("Server Type", "Oracle Server");
  newProperties.put("Database DLL", "crdb_jdbc.dll");
newProperties.put("Database Class Name", "oracle.jdbc.driver.OracleDriver");
newProperties.put("Trusted_Connection", "false");
  newProperties.put("PreQEServerType", "Oracle Server");
  newConnectionInfo.setAttributes(newProperties);
newConnectionInfo.setUserName("schemaname");
newConnectionInfo.setPassword("password");
return newConnectionInfo;
}
%>

-------------------------------------END------------------------------------

[former_member203619]

The only time I've seen something like that was when the number of objects in the Oracle database exceede the capability of the report designer to display them.  In that case - stored procedures are generally the first ones to not be displayed.

Do you have a sql server you can test with - or perhaps verify how many objects you have in your database?  I believe the default is 50k objects as the limit.

Also, what user account are you using to log onto Oracle?  I assume it is one that has sufficient rights to see the stored procedures.  There is a kbase about using the wrong account here.

(https://support.wdf.sap.corp/sap/support/notes/1980814)

I assume that you are given that the stored procedure show up in the data explorer - but it doesn't hurt to check.

Shawn

https://support.wdf.sap.corp/sap/support/notes/1980814

Message was edited by: Ludek Uher

[Former Member]

I am not using any ODBC, I use JDBC. Also the support link is not working.