Bulk Update database properties in Crystal Reports

[Former Member]

Hello Everyone

In my situation for one of the apps, I have more than 200 reports organized in diff folders. For both migrating these reports from one environment to other and for updating databases password, I had a need to update database credentials in bulk for all reports.

I am putting the code here, in case anyone needs this in a similar situation.

public class dbUpdate {

  public static void main(String[] args) throws ReportSDKException, FileNotFoundException, IOException {

    Properties prop = new Properties();

    prop.load(new FileInputStream("config/setup.properties"));

       String UpdateDBProps = prop.getProperty("UpdateDBProps");

       String encryptPass = prop.getProperty("encryptPass");

       String decryptPass = prop.getProperty("decryptPass");

       String TopLevelFolder = prop.getProperty("TopLevelFolder");

       String BOCmcHostPort = prop.getProperty("BOCmcHostPort");

       String BOUserName = prop.getProperty("BOUserName");

       String BOPassword = prop.getProperty("BOPassword");

       String dbServerName = prop.getProperty("dbServerName");

       String dbuserName = prop.getProperty("dbuserName");

       String dbpassword = prop.getProperty("dbpassword");

       String logFile = prop.getProperty("logFile");

       logger.setLogFilename(logFile);

       try

       {

      if (encryptPass.equalsIgnoreCase("true")) {

       String eBOPassword= AESencrypt.encrypt(BOPassword)  ;

       String edbpassword= AESencrypt.encrypt(dbpassword)  ;

       logger.write("boPass Plain: " + BOPassword + " boPass Encrypted: "  + eBOPassword);

       logger.write("dbPass Plain: " + dbpassword + " dbPass Encrypted: "  + edbpassword);

       System.exit(0);

     } //if encryptpass is true

      if (decryptPass.equalsIgnoreCase("true")) {

        String dBOPassword= AESencrypt.decrypt(BOPassword)  ;

        String ddbpassword= AESencrypt.decrypt(dbpassword)  ;

        logger.write("boPass Plain: " + BOPassword + " boPass Decrypted: "  + dBOPassword);

        logger.write("dbPass Plain: " + dbpassword + " dbPass Decrypted: "  + ddbpassword);

        System.exit(0);

     } //if decryptpass is true

         dbpassword = AESencrypt.decrypt(dbpassword);

         BOPassword = AESencrypt.decrypt(BOPassword);

       }

       catch (Exception ex)

       {

         ex.printStackTrace();

       }

       //System.out.println("BOCmcHostPort:" + BOCmcHostPort + " dbServerName:" + dbServerName + " dbuserName:" + dbuserName + " dbpassword: " + dbpassword + " Plain");

       logger.write("Update DB: "+ UpdateDBProps + " Root Folder: " + TopLevelFolder + " BOCmcHostPort:" + BOCmcHostPort + " dbServerName:" + dbServerName + " dbuserName:" + dbuserName + " dbpassword: " + dbpassword + " Plain");

       logger.write("Date Timestamp Reportpath and name; existing Original dbserver name/password - Custom DBserver name/password and if updated dbserver name/password - Custom DBserver name/password ");

   Integer TopLevelSIID = Integer.parseInt(TopLevelFolder);

  // TODO Auto-generated method stub

        String auth = "secEnterprise";

        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(BOUserName, BOPassword, BOCmcHostPort, auth);

           logger.write("LOGIN SUCCESSFUL\n");

           }  //try

           catch (Exception error)

           {

           loggedIn = false;

           String failure = "error";

           }  //catch

           if (!loggedIn)

           {

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

            logger.write("FAILED LOGIN\n");

           }  //if not logged in

           else

           {

           // Query for all Crystal reports from the Enterprise CMS.

            try {

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

             // Get all branches

             IInfoObjects branches = iStore.query("Select SI_ID From CI_INFOOBJECTS Where SI_INSTANCE=0 AND SI_KIND=Folder AND SI_PARENT_FOLDER= " + TopLevelSIID);

             //Check this for recursive - This is customized for Global Billing - Top Root folder/Branch/Batched or ondemand 

             System.out.println("\nTop Level Folder = " + TopLevelSIID + " Member Count= " + branches.getResultSize());

                for (Integer b =0; b < branches.getResultSize(); b++) { // for each branch SIID

                IInfoObject binfoObject = (IInfoObject) branches.get(b);

              String branchName = binfoObject.getTitle();

              Integer branchSIID = binfoObject.getID();

              //logger.write("SI_ID - " + branchSIID + " Branch Name - "+ branchName);

              String query="Select SI_ID From CI_INFOOBJECTS Where SI_INSTANCE=0 AND SI_KIND=Folder AND SI_PARENT_FOLDER= " + branchSIID;

              IInfoObjects folders=iStore.query(query); //batch or ondemand

              for (Integer c =0; c < folders.getResultSize(); c++) { // for each branch SIID

                  IInfoObject cinfoObject = (IInfoObject) folders.get(c);

              String cfolderName = cinfoObject.getTitle();

              Integer cfolderSIID = cinfoObject.getID();

              //logger.write("SI_ID - " + cfolderSIID + " demand or batch - "+ cfolderName);

              //Get all Reports of above folder

              String rquery="Select SI_ID From CI_INFOOBJECTS Where SI_INSTANCE=0 AND SI_PARENT_FOLDER= " + cfolderSIID;

              IInfoObjects cfolders=iStore.query(rquery); //batch or ondemand

             for (Integer i = 0 ; i < cfolders.getResultSize(); i++ ) {

              IInfoObject infoObject = (IInfoObject) cfolders.get(i);

              String reportName = infoObject.getTitle();

              Integer reportSIID = infoObject.getID();

              //logger.write(branchName + "/" + cfolderName + "/"+ reportName);

              //logger.write("SI_ID - " + reportSIID + " and Report Name - "+ reportName);

              //Specify SI ID for testing

              query="SELECT SI_ID, SI_NAME, SI_LOGONINFO, SI_PROCESSINFO FROM CI_INFOOBJECTS WHERE SI_INSTANCE='false' AND SI_PROGID='CrystalEnterprise.Report' AND SI_ID="+ reportSIID;

              //** Below for testing Single reports

              reportSIID=48848;

              query= query + " AND SI_ID= " + reportSIID + " AND SI_PARENT_FOLDER= " + cfolderSIID;

              //** Below for testing Single reports

              IInfoObjects reports=iStore.query(query);

              if (reports.getResultSize() > 0) {

              IReport report=(IReport)reports.get(0);

              //IProperties reportp=(IProperties)reports.get(0);

              IReportProcessingInfo pluginInterface=(IReportProcessingInfo)report.getPluginProcessingInterface("CrystalReport");

              ISDKList dbLogons=pluginInterface.getReportLogons();

              IReportLogon dbLogon=(IReportLogon)dbLogons.get(0);

              String cdbserver=dbLogon.getCustomServerName();

              String cuser=dbLogon.getCustomUserName();

              String odbserver=dbLogon.getServerName();

              String ouser=dbLogon.getUserName();

              //Update db credentials only if UpdateDBProps=true

              if (UpdateDBProps.equalsIgnoreCase("true")) {

                dbLogon.setCustomPassword(dbpassword);

                dbLogon.setCustomUserName(dbuserName);

                dbLogon.setCustomServerName(dbServerName);

                dbLogon.setCustomServerType(IReportLogon.CeReportServerType.ORACLE);

                //update table prefix

                ISDKSet tprefixes = dbLogon.getReportTablePrefixes();

                if (tprefixes != null && !tprefixes.isEmpty()) {

                Iterator tps = tprefixes.iterator();

                IReportTablePrefix prefix = null;

                if (tps.hasNext()) {

                  prefix = (IReportTablePrefix) tps.next();

                }

                prefix.setMappedTablePrefix(dbuserName.toUpperCase()); //may not need . - +"."

                prefix.setUseMappedTablePrefix(true);

                } //table prefix   

                dbLogon.setPromptOnDemandViewing(false);

                dbLogon.setOriginalDataSource(false);

                dbLogon.setReportLogonMode(1); //#3942# - 1

                //save above values

                report.save();

              } // if UpdateDBProps=true

              ISDKList AdbLogons=pluginInterface.getReportLogons();

              IReportLogon AdbLogon=(IReportLogon)AdbLogons.get(0);

              //cdbdriver=dbLogon.getDatabaseServerType();

              String acdbserver=AdbLogon.getCustomServerName();

              String acuser=AdbLogon.getCustomUserName();

              String aodbserver=AdbLogon.getServerName();

              String aouser=AdbLogon.getUserName();

              String eocred= odbserver + "/" + ouser;

              String uocred= aodbserver +"/" + aouser;

              String eccred = cdbserver + "/" + cuser;

              String uccred = acdbserver + "/" + acuser;

              String dbCred= eocred + " - " + eccred;

              if (UpdateDBProps.equalsIgnoreCase("true")) {

              dbCred= dbCred + " Updated "+ uocred + " - " + uccred;

              }

              logger.write(branchName + "/" + cfolderName + "/"+ reportName + " ; " + dbCred);

               } //if query returned values                

             } //For on demad or batch           

            } //for all reports

              } //for each branch

         } catch(SDKException re){

                   re.printStackTrace();

                  }

         } //else if logged in

     }  //if null session manager

        logger.write("GB Report dbUpdater Completed Successfully ");     

  } //main 

} //outermost class

Enjoy !!!

[akolesnikov]

Hello, thank you for posting. I'm really new with development.

How do I make this run?

Does this code update the pw credentials for what database (oracle, MSsql) for report’s objects and corresponding recurrences?

How can I update user name (schema name) and database server?

How can I update the user name (schema name) and pw only for report’s objects and corresponding recurrences that currently connected to server AAA and schema 123, and in following execution update the reports that using server BBB and schema XYZ.

Thank you