Skip to Content
avatar image
Former Member

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

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);
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    May 25, 2012 at 08:55 AM

    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);  
    }
    %>

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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • avatar image
    Former Member
    Jul 09, 2014 at 09:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.