Automation for updating the custom logon details for Crystal Reports

Imagine, you have more than 500 connection details to be configured as a part of deployment activity from UAT to the production server. In such situations, you will curse yourself by measuring the amount of manual intervention in your hands. As the number of report increases, this activity will become more disruptive. So what is the solution for this? Automation!

Here the automation for updating the connection details is achieved with the help of Business Objects Java SDK. Below is the diagram showing the process flow.

Below cases need to be checked before considering this approach.

1) The Cluster Unique Identifier (CUID) should be the same for a single Crystal Report in both the production and UAT server.

2) Have a proper understanding of the kind of databases, the report is connecting to. My code snippet is based on Oracle database configuration. If the database differs, then minor changes should be made to the code before going for this approach.

Below are the Java code snippet and xml files used for this automation.

Downloadconnections.java:Java class to extract connection details from UAT server.

Downloadconnections.xml:XML file for storing connection details from UAT.

Mapxml.java:Java class for mapping UAT connections to Production using the properties file.

Database.properties:Properties file where UAT to Production connection detail mappings are stored.

database.database1=newdatabase1database.database2=newdatabase2database.database3=newdatabase3database.username.database1.username=newusername1database.username.database2.username=newusername2database.username.database3.username=newusername3database.password.database1.username=password1database.password.database2.username=password2database.password.database3.username=password3

Updateconnections.xml:XML file having the connection details for Production server.

Updateconnections.java:Java class used for updating the connection details in Production server.

import java.util.*;import java.io.*;import com.crystaldecisions.sdk.plugin.desktop.report.*;import com.crystaldecisions.sdk.properties.*;import com.crystaldecisions.sdk.plugin.desktop.common.*;import com.crystaldecisions.sdk.occa.infostore.*;import com.crystaldecisions.sdk.framework.*;import javax.xml.parsers.*;import org.w3c.dom.*;import org.xml.sax.*;class Updateconnections { public static void main(String[] args) { try { String username = “user1”; String password = “pass1”; String cmsName = “servername:port”; String authType = “secEnterprise”; String reportName = “NEWCR”; ISDKSet prefixes = null; IReportTablePrefix prefix = null; IInfoObjects oInfoObjects = null; IInfoObject oInfoObject = null; IEnterpriseSession enterpriseSession = null; Exception failure = null; boolean loggedIn = true; if (enterpriseSession == null) { ISessionMgr sm = CrystalEnterprise.getSessionMgr(); enterpriseSession = sm.logon(username, password, cmsName, authType); } IInfoStore boInfoStore = (IInfoStore) enterpriseSession.getService( “”, “InfoStore”); File file = new File(“Updatedconnections.xml”); DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder(); Document doc = builder.parse(file); NodeList nodes = doc.getElementsByTagName(“report”); for (int i = 0; i < nodes.getLength(); i++) { Element element = (Element) nodes.item(i); String cuid = element.getAttribute(“cuid”); oInfoObjects = (IInfoObjects) boInfoStore.query(“select * from ci_infoobjects where si_kind = ‘CrystalReport’ and SI_CUID='” + cuid + “‘”); int jx = oInfoObjects.getResultSize(); if (jx == 0) { System.out.println(“The report cuid for report ” + element.getElementsByTagName(“reportname”).item(0).getChildNodes().item(0).getNodeValue() + “is not present in the server”); } else { oInfoObject = (IInfoObject) oInfoObjects.get(i); IReport oReport = null; oReport = (IReport) oInfoObject; IReportLogon rptLogon = null; NodeList datalines = element.getElementsByTagName(“datasource”); for (int m = 0; m < datalines.getLength(); m++) { Element dataline = (Element) datalines.item(m); String datasource = dataline.getAttribute(“cuid”); rptLogon = (IReportLogon) oReport.getReportLogons().get(m); rptLogon.setOriginalDataSource(false); rptLogon.setCustomServerType(2); NodeList lines1 = dataline.getElementsByTagName(“originalsource”).item(0).getChildNodes(); String source = lines1.item(0).getNodeValue(); rptLogon.setCustomServerName(source); NodeList lines2 = dataline.getElementsByTagName(“username”).item(0).getChildNodes(); String user = lines2.item(0).getNodeValue(); rptLogon.setCustomUserName(user); NodeList lines3 = dataline.getElementsByTagName(“password”).item(0).getChildNodes(); String pass = lines3.item(0).getNodeValue(); rptLogon.setCustomPassword(pass); NodeList lines4 = dataline.getElementsByTagName(“tableprefix”); prefixes = rptLogon.getReportTablePrefixes(); int len = rptLogon.getReportTablePrefixes().size(); Iterator iterator = prefixes.iterator(); for (int j = 0; j < lines4.getLength(); j++) { Element line = (Element) lines4.item(j); StringBuffer sb = new StringBuffer(); Node child = line.getFirstChild(); CharacterData cd = (CharacterData) child; String text; if (cd == null) { text = “”; } else { sb.append(cd.getData()); text = sb.toString().trim(); System.out.println(text); } prefix = (IReportTablePrefix) iterator.next(); if (len > 1) { prefix.setUseMappedTablePrefix(true); prefix.setMappedTablePrefix(text); } } rptLogon.setPromptOnDemandViewing(false); } } boInfoStore.commit(oInfoObjects); } } catch (Exception exi) { exi.printStackTrace(); } }}