How to switch database connection at runtime in crystal reports using java swing

[Former Member]

Please help. I am developing a swing application that can open reports made using crystal reports xi. What Im trying to do is to open a report and pass the connection info to that report so that I can dynamically change the database of the report. But for some reason it is not working and the report still produces the information from the database that it was initially set up. Can someone please tell me what I did wrong?  Btw when I try using  com.crystaldecisions.sdk.occa.report.application.ReportClientDocument, I get a server not found error. But when I try to use import com.crystaldecisions.reports.sdk.ReportClientDocument; It's not working. I am not sure what a report server is and I dont think I set it up on my computer. Please help. Here is my code:

import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
ReportClientDocument rpt =  new ReportClientDocument();
        rpt.open(reportPath+fileName, 0);
        Fields fields = null;
        IConnectionInfo connInfo = rpt.getDatabaseController().getConnectionInfos(null).getConnectionInfo(0);
         PropertyBag innerProp = connInfo.getAttributes();
            innerProp.clear();
            PropertyBag propertyBag = new PropertyBag();
            propertyBag.put("Server Type", "JDBC (JNDI)");
            propertyBag.put("Database DLL", "crdb_jdbc.dll");
            propertyBag.put("Database Class Name", "com.mysql.jdbc.Driver");
            propertyBag.put("Use JDBC", "true");
            propertyBag.put("Server Name", DBConnect.getServer());
            propertyBag.put("Generic JDBC Driver Behavior", "No");
            propertyBag.put("URI", "!com.mysql.jdbc.Driver!jdbc:mysql://"+DBConnect.getServer()+":"+DBConnect.getPort()+"/"+DBConnect.getDatabase()+"!ServerType=29!QuoteChar=`");
            connInfo.setAttributes(innerProp);
            connInfo.setPassword(DBConnect.getPassword());
            connInfo.setUserName(DBConnect.getUsername());
        int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;
        rpt.getDatabaseController().replaceConnection(rpt.getDatabaseController().getConnectionInfos(null).getConnectionInfo(0), connInfo, fields, replaceParams);

[Former Member]

Hi Expert,

I am trying to  change crystal report connection by using java code but I am not able to locate the jar files for below package.

import com.crystaldecisions.sdk.occa.managedreports.reportAppFactory;

import com.crystaldecisions.sdk.occa.report.application.databaseController;

import com.crystaldecisions.sdk.occa.report.lib.propertyBag;

Can you please let me know in which jar file above package would be there.

Sample Code:-

import com.businessobjects.rebean.wi.DocumentInstance;

import com.crystaldecisions.sdk.exception.SDKException;

import com.crystaldecisions.sdk.framework.CrystalEnterprise;

import com.crystaldecisions.sdk.framework.IEnterpriseSession;

import com.crystaldecisions.sdk.framework.ISessionMgr;

import com.crystaldecisions.sdk.occa.infostore.IInfoObject;

import com.crystaldecisions.sdk.occa.infostore.IInfoObjects;

import com.crystaldecisions.sdk.occa.infostore.IInfoStore;

import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;

import com.crystaldecisions.sdk.occa.managedreports.reportAppFactory;

import com.crystaldecisions.sdk.occa.report.application.databaseController;

import com.crystaldecisions.sdk.occa.report.data.IConnectionInfo;

import com.crystaldecisions.sdk.occa.report.document.*;

import com.crystaldecisions.sdk.occa.report.definition.*;

import com.crystaldecisions.sdk.occa.report.lib.propertyBag;

class ChangeDataSource {

  void cmsconnect(String cmstext,String usetext,String passtext, String auttext)

  {

       String cms = cmstext;

       String username = usetext;

       String password = passtext;

       String auth = auttext;

       IEnterpriseSession enterpriseSession = null;

       ISessionMgr sessionMgr = null;//CrystalEnterprise.getSessionMgr();  

       Exception failure = null;

       boolean loggedIn = true;

       ReportClientDocument clientDoc = null;

       if (enterpriseSession == null)

       {

      try

      {

          sessionMgr = CrystalEnterprise.getSessionMgr();

          enterpriseSession = sessionMgr.logon(username, password, cms, auth);

          System.out.println("\nLOGIN SUCCESSFUL\n");

      }

      catch (Exception error)

      {

          loggedIn = false;

          failure = error;

      }

      if (!loggedIn)

      {

         System.out.println("\nLOGIN FAILED\n");

      }

   //Step 1..............................................................................................................................

      else

      {

          // Query for the sample report from the Enterprise CMS.

          try {

            IInfoStore iStore = (IInfoStore) enterpriseSession.getService("InfoStore");

            IInfoObjects infoObjects = iStore.query("Select SI_ID From CI_INFOOBJECTS Where SI_Type='CrystalReport'");

            System.out.println("\ninfoObjects size = "+infoObjects.getResultSize());

            IReportAppFactory reportAppFactory = (IReportAppFactory)enterpriseSession.getService("RASReportFactory");

  System.out.println(infoObjects.get(0));

            IInfoObject infoObject = (IInfoObject)infoObjects.get(0);

/* if (infoObjects != null) {

  Iterator lReportListIterator = lReportList.iterator();

  while (lReportListIterator.hasNext()) {

  IInfoObject infoObject = (IInfoObject) lReportListIterator.next();*/

            clientDoc = new ReportClientDocument();

            clientDoc = reportAppFactory.openDocument(infoObject,0, java.util.Locale.US);

            System.out.println("Report "+ infoObject.getTitle() +" Opened");  

           //Step2.......................................................................................................................................

  //switch_connection(clientDoc.getDatabaseController(),infoObject );

  }catch(ReportSDKException re){           

            re.printStackTrace();

         }catch(SDKException re){

            re.printStackTrace();

         }

      }

       }

   }

   private static void switch_connection(DatabaseController databaseController, IInfoObject REPORT_NAME) throws ReportSDKException {

     //Report can be opened from the relative location specified in the CRConfig.xml, or the report location

//tag can be removed to open the reports as Java resources or using an absolute path (absolute path not recommended

//for Web applications).

//final String REPORT_NAME = "change_db_location/simplereport.rpt";

final String DBUSERNAME = "vantech";

final String DBPASSWORD = "vantech";

final String CONNECTION_STRING = "!com.microsoft.jdbc.sqlserver.SQLServerDriver!jdbc:microsoft:sqlserver://10.50.212.103:1433;DatabaseName={database};user={userid};password={password}";

final String TRUSTEDCON = "false";

final String PREQESERVERNAME = "jdbc:microsoft:sqlserver://10.50.212.103:1433";

final String SERVERTYPE = "JDBC (JNDI)";

final String DATABASE_DLL = "crdb_jdbc.dll";

final String DATABASE = "Xtreme";

final String DBCLASSNAME = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

final String USEJDBC = "true";

final String DATABASE_NAME = "Xtreme";

final String SERVERNAME = "jdbc:microsoft:sqlserver://10.50.212.103:1433";

final String CONNECTIONURL = "jdbc:microsoft:sqlserver://10.50.212.103:1433";

final String SERVER = "10.50.212.103";

  //Open report.

  ReportClientDocument boReportClientDocument = new ReportClientDocument();

  boReportClientDocument = reportAppFactory.openDocument(infoObject,0, java.util.Locale.US);

  //boReportClientDocument.open(REPORT_NAME, 0);

  // Set DB Username and Password

  boReportClientDocument.getDatabaseController().logon(DBUSERNAME, DBPASSWORD);

  // Create the two connectioninfo objects to use

  IConnectionInfo oldConnectionInfo =  new ConnectionInfo();

  IConnectionInfo newConnectionInfo = new ConnectionInfo();

       //System.out.println(oldConnectionInfo);

   //System.out.println(ToStringBuilder.reflectionToString(oldConnectionInfo));

// Assign the old Connection info to the reports current info

  DatabaseController dbController = boReportClientDocument.getDatabaseController();

  oldConnectionInfo = dbController.getConnectionInfos(null).getConnectionInfo(0);                                       

  // If this connection needed parameters, we would use this field.

  Fields pFields = null;

  // Create a new propertybag for the new location

  PropertyBag boPropertyBag1 = new PropertyBag();

  // Set new table logon properties

  boPropertyBag1.put("JDBC Connection String", CONNECTION_STRING);

  boPropertyBag1.put("Trusted_Connection", TRUSTEDCON);

  boPropertyBag1.put("PreQEServerName", PREQESERVERNAME);

  boPropertyBag1.put("Server Type", SERVERTYPE);

  boPropertyBag1.put("Database DLL", DATABASE_DLL);

  boPropertyBag1.put("Database", DATABASE);

  boPropertyBag1.put("Database Class Name", DBCLASSNAME);

  boPropertyBag1.put("Use JDBC", USEJDBC);

  boPropertyBag1.put("Database Name", DATABASE_NAME);

  boPropertyBag1.put("Server Name", SERVERNAME);

  boPropertyBag1.put("Connection URL", CONNECTIONURL);

  boPropertyBag1.put("Server", SERVER);

  // Assign the properties to the connection info

  newConnectionInfo.setAttributes(boPropertyBag1);

  // Set the DB Username and Pwd

  newConnectionInfo.setUserName(DBUSERNAME);

  newConnectionInfo.setPassword(DBPASSWORD);

  // The Kind of connectionInfos is SQL

  newConnectionInfo.setKind(ConnectionInfoKind.SQL);

  // set the parameters to replace.

  // The 4 options are:

  // _doNotVerifyDB

  // _ignoreCurrentTableQualifiers

  // _mapFieldByRowsetPosition

  // _useDefault 

  int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;

  // Now replace the connections

  dbController.replaceConnection(oldConnectionInfo, newConnectionInfo, pFields, replaceParams);

  //Store the report source in session, will be used by the CrystalReportViewer.

  //session.setAttribute("reportSource", boReportClientDocument.getReportSource());

  //Launch CrystalReportViewer page that contains the report viewer.

  //response.sendRedirect("CrystalReportViewer.jsp");

   }

I have added the most of the jar files but still not able to resolve the problem.

Thanks,

Sachin

[Former Member]

Hello and good afternoon,

for example:

import ... DatabaseController;

is defined in

CrystalREportsRuntime.jar.

The runtime jars can be found

here.

Regards, Stephan G.

[Former Member]

Here is an almost full code based on jsp that I can be helpfull since it shows the packages that need to be imported. But What I suspect here that might be problematic and confuse your application it is the runtimes used:

Are you using the JRC runtimes located at: http://www.businessobjects.com/campaigns/forms/downloads/crystal/eclipse/datasave.asp

And can be downloaded by clicking on this link:

http://downloads.businessobjects.com/akdlm/crystalreportsforeclipse/2_0/crjava-runtime_12.2.213.zip

//===================================================================

<%@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.*" %>

<%
//Report can be opened from the relative location specified in the CRConfig.xml, or the report location
//tag can be removed to open the reports as Java resources or using an absolute path (absolute path not recommended
//for Web applications).
final String REPORT_NAME = "change_db_location/simplereport.rpt";
final String DBUSERNAME = "<dbusername>";
final String DBPASSWORD = "<password>";

final String CONNECTION_STRING = "!com.microsoft.jdbc.sqlserver.SQLServerDriver!jdbc:microsoft:sqlserver://<servername>:<port>;DatabaseName={database};user={userid};password={password}";
final String TRUSTEDCON = "false";
final String PREQESERVERNAME = "jdbc:microsoft:sqlserver://<servername>:<port>";
final String SERVERTYPE = "JDBC (JNDI)";
final String DATABASE_DLL = "crdb_jdbc.dll";
final String DATABASE = "Xtreme";
final String DBCLASSNAME = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
final String USEJDBC = "true";
final String DATABASE_NAME = "Xtreme";
final String SERVERNAME = "jdbc:microsoft:sqlserver://<servername>:<port>";
final String CONNECTIONURL = "jdbc:microsoft:sqlserver://<servername>:<port>";
final String SERVER = "<servername>";
%>

<%

try {
//Open report.
ReportClientDocument boReportClientDocument = new ReportClientDocument();
boReportClientDocument.open(REPORT_NAME, 0);

// Set DB Username and Password
boReportClientDocument.getDatabaseController().logon(DBUSERNAME, DBPASSWORD);

// Create the two connectioninfo objects to use
IConnectionInfo oldConnectionInfo =  new ConnectionInfo();
  IConnectionInfo newConnectionInfo = new ConnectionInfo();

  // Assign the old Connection info to the reports current info
  DatabaseController dbController = boReportClientDocument.getDatabaseController();
  oldConnectionInfo = dbController.getConnectionInfos(null).getConnectionInfo(0);                                       

  // If this connection needed parameters, we would use this field. 
  Fields pFields = null;
 
  // Create a new propertybag for the new location
  PropertyBag boPropertyBag1 = new PropertyBag();
 
// Set new table logon properties
boPropertyBag1.put("JDBC Connection String", CONNECTION_STRING);
boPropertyBag1.put("Trusted_Connection", TRUSTEDCON);
boPropertyBag1.put("PreQEServerName", PREQESERVERNAME);
boPropertyBag1.put("Server Type", SERVERTYPE);
boPropertyBag1.put("Database DLL", DATABASE_DLL);
boPropertyBag1.put("Database", DATABASE);
boPropertyBag1.put("Database Class Name", DBCLASSNAME);
boPropertyBag1.put("Use JDBC", USEJDBC);
boPropertyBag1.put("Database Name", DATABASE_NAME);
boPropertyBag1.put("Server Name", SERVERNAME);
boPropertyBag1.put("Connection URL", CONNECTIONURL);
boPropertyBag1.put("Server", SERVER);

// Assign the properties to the connection info
newConnectionInfo.setAttributes(boPropertyBag1);

// Set the DB Username and Pwd
newConnectionInfo.setUserName(DBUSERNAME);
newConnectionInfo.setPassword(DBPASSWORD);
 
// The Kind of connectionInfos is SQL
newConnectionInfo.setKind(ConnectionInfoKind.SQL);

// set the parameters to replace.
// The 4 options are:
// _doNotVerifyDB
// _ignoreCurrentTableQualifiers
// _mapFieldByRowsetPosition
// _useDefault 
int replaceParams = DBOptions._ignoreCurrentTableQualifiers + DBOptions._doNotVerifyDB;

// Now replace the connections 
  dbController.replaceConnection(oldConnectionInfo, newConnectionInfo, pFields, replaceParams);

//Store the report source in session, will be used by the CrystalReportViewer.
session.setAttribute("reportSource", boReportClientDocument.getReportSource());
 
//Launch CrystalReportViewer page that contains the report viewer.
response.sendRedirect("CrystalReportViewer.jsp");
 
}
catch(ReportSDKException ex) {
out.println(ex);
}
catch(Exception ex) {
out.println(ex);  
}
%>

//===================================================================

[Former Member]

Hi Alphonse,

I tried your code. But with some change, I am setting the parameter as well.

I am getting parameter value missing error. Can you plz help me on that.

[Former Member]

Hi,

Sorry for the late reply. Actually the message error shows an issue with setting the parameter value through your code. Is it a single value discrete parameter are you setting?

If so, here is a snippet code:

ParameterFieldController paramFieldController = reportClientDoc.getDataDefController().getParameterFieldController();

paramFieldController.setCurrentValue("", "StringParam", new String("Hello"));

What happens if you manually perfom the same operations through CrystalReports Designer, do you get parameter value missing error?

Thanks

Alphonse