Skip to Content
0
Former Member
Mar 01, 2009 at 10:29 PM

update database logon in reports using java

53 Views

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

}