Please review the code below and let me know where did I go wrong! I am able to login to CMS and able to retrieve the list of reports. Now, I am trying to update the database credentials for a single report using java (My requirement is to update all 200 reports though!).
This is the 100th time I go this error message: Failed to open the connection.report.lib.ReportSDKServerException: Failed to open the connection.
I also wrote a sample java program which tries to connect to the sql server database with same login credentials - this works perfectly fine. But, if I use the same connection url, driver name and login credentials, this report fails to update for whatever reason. Please help me ASAP..Thanks!
import com.crystaldecisions.sdk.exception.*;
import com.crystaldecisions.sdk.framework.*;
import com.crystaldecisions.sdk.occa.infostore.*;
import com.crystaldecisions.sdk.occa.managedreports.*;
import com.crystaldecisions.sdk.occa.report.application.*;
import com.crystaldecisions.sdk.occa.report.data.*;
import com.crystaldecisions.sdk.occa.report.lib.*;
import java.util.Iterator;
class ChangeDataSource {
public static void main(String args[]){
String cms = "134.X.X.X:6400";
String username = "Administrator";
String password = "";
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(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");
}
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_INSTANCE=0 AND SI_PARENT_FOLDER=771");
System.out.println("\ninfoObjects size = "+infoObjects.getResultSize());
IReportAppFactory reportAppFactory = (IReportAppFactory)enterpriseSession.getService("RASReportFactory");
IInfoObject infoObject = (IInfoObject)infoObjects.get(0);
clientDoc = new ReportClientDocument();
clientDoc = reportAppFactory.openDocument(infoObject,0, java.util.Locale.US);
System.out.println("Report "+ infoObject.getTitle() +" Opened");
switch_tables(clientDoc.getDatabaseController());
}catch(ReportSDKException re){
re.printStackTrace();
}catch(SDKException re){
re.printStackTrace();
}
}
}
}
private static void switch_tables(DatabaseController databaseController) throws ReportSDKException {
//Declare the new connection properties that report's datasource will be switched to.
//NOTE: These are specific to using JDBC against a particular MS SQL Server database. Be sure to use the
//DisplayConnectionInfo sample to determine what your own connection properties need to be set to.
final String TABLE_NAME_QUALIFIER = "dbname.dbo.";
final String DATABASE_NAME = "dbname";
final String DBUSERNAME = "userid";
final String DBPASSWORD = "pword";
final String SERVERNAME = "134.X.X.X";
final String CONNECTION_URL = "jdbc:sqlserver://134.X.X.X:1433;DatabaseName=dbname";
final String DATABASE_CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
final String DATABASE_DLL = "crdb_jdbc.dll";
//Obtain collection of tables from this database controller.
Tables tables = databaseController.getDatabase().getTables();
System.out.println("\nTables size = "+tables.size());
//Set the datasource for all main report tables.
for (int i = 0; i < tables.size(); i++) {
//ITable currTable = tables.getTable(i);
ITable table = tables.getTable(i);
//Keep existing name and alias.
table.setName(table.getName());
table.setAlias(table.getAlias());
//System.out.println("Table Name = "+table.getName()+" Alias "+table.getAlias());
//Change properties that are different from the original datasource.
table.setQualifiedName(TABLE_NAME_QUALIFIER + table.getName());
System.out.println(table.getQualifiedName());
//Change connection information properties.
IConnectionInfo connectionInfo = null;
try{
connectionInfo = table.getConnectionInfo();
//System.out.println("conn info: "+connectionInfo);
}catch(Exception re) {
re.printStackTrace();
}
//Set new table connection property attributes.
PropertyBag propertyBag = new PropertyBag();
//Overwrite any existing properties with updated values.
propertyBag.put("Trusted_Connection", "false");
propertyBag.put("Connection URL", CONNECTION_URL);
propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);
propertyBag.put("Server", SERVERNAME);
//propertyBag.put("Database Name", DATABASE_NAME);
propertyBag.put("Server Type", "JDBC (JNDI)");
//propertyBag.put("URI", null);
propertyBag.put("Use JDBC", "true");
propertyBag.put("Database DLL", DATABASE_DLL);
connectionInfo.setAttributes(propertyBag);
System.out.println("\n Properties SET\n");
//Set database username and password.
//NOTE: Even if these the username and password properties don't change when switching databases, the
//database password is *not* saved in the report and must be set at runtime if the database is secured.
connectionInfo.setUserName(DBUSERNAME);
connectionInfo.setPassword(DBPASSWORD);
connectionInfo.setKind(ConnectionInfoKind.SQL);
table.setConnectionInfo(connectionInfo);
//Update old table in the report with the new table.
try {
databaseController.setTableLocation(table, tables.getTable(i));
}catch(ReportSDKException re) {
re.printStackTrace();
}
}
System.out.println("\n All tables updated\n");
}
}