How to change JDBC connection URL after deployment without designer?

[Former Member]

Hi, buddies:

I have a simple but not easy to solve problem after efforts seeking for helps. My expectation is to change my jdbc connection URL by modifying a xml file or use administrative console in crystal report server XI. Not just open the designer, open my rpt file, and user "database expert" to re-map my tables again.

How?

My practice is:

1, in crytal report 2008, create my new rpt file, and use "JDBC" as my database connection;

2, I can connect to the database and drag and draw the tables to rpt after my jdbc connection and classpath is fine.

3, I publish to the testing server which the destination database is identical to my rpt;

4, it is fine to open by setting up the right classpath in CRConfig.xml file.

BUT, I will publish to the production server which my destination for datase is different from my previous URL.

How can I change it? It seems very silly to open each rpt files to reroute the database file-by-file.

Can crystal report provide a confortable way to do it?

(I got some hints by googling the question and it is related to JNDI instead of JDBC, but how can I access the JNDI server when I using crystal report designer 2008).

This problem drive me crazy and desperately want your help!!

[Adam_Stone · Accepted solution]

There are multiple ways you can handle this, each of which will depend on your workflow for viewing the documents. Are you using a custom application to view the reports, or using Infoview/CMC/URL reporting?

Are you looking to create a custom applicaiton to change the connections, or do you want to do it through the CMC?

[Former Member · Accepted solution]

Dear Adam:

Thanks for your reply.

I am using the Java API to call the RPT files in my web application, not using CMC to view the report.

My question is that how to change the JDBC connection url without modifying the .rpt files in crystal report designer. In the CMC, I can't find the config entry for such requirement. Nor I can't change the URL via Java API.

How?

THANKS!

[Former Member · Accepted solution]

Hello,

I do it this way:

	/*
			// ****** BEGIN CHANGE DATASOURCE SNIPPET ****************  
			{
				String connectString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=I:\\Database\\temp.mdb";
				String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
				String JNDIName = "";
				String userName = "dbuser";			// TODO: Fill in database user
				String password = "dbpassword";		// TODO: Fill in password

				// Switch all tables on the main report and sub reports
				JRCHelperSample.changeDataSource(clientDoc, userName, password, connectString, driverName, JNDIName);
			}
			// ****** END CHANGE DATASOURCE SNIPPET **************** 	
		*/

Kind regards

Martin

[Former Member]

Dear Martin:

What the "JRCHelperSample" class stands for? I can't find it in Crsytal report SDK for Java.

[Former Member]

Part 1

/**
	 * Changes the DataSource for a specific Table
	 * @param clientDoc The reportClientDocument representing the report being used
	 * @param reportName	"" for main report, name of subreport for subreport, null for all reports
	 * @param tableName		name of table to change.  null for all tables.
	 * @param username  The DB logon user name
	 * @param password  The DB logon password
	 * @param connectionURL  The connection URL
	 * @param driverName	The driver Name
	 * @param jndiName		The JNDI name
	 * @throws ReportSDKException
	 */
	public static void changeDataSource(ReportClientDocument clientDoc,
				String reportName, String tableName,
				String username, String password, String connectionURL,
				String driverName,String jndiName) throws ReportSDKException {

		PropertyBag propertyBag = null;
		IConnectionInfo connectionInfo = null;
		ITable origTable = null;
		ITable newTable = null;

		// Declare variables to hold ConnectionInfo values.
		// Below is the list of values required to switch to use a JDBC/JNDI
		// connection
		String TRUSTED_CONNECTION = "false";
		String SERVER_TYPE = "JDBC (JNDI)";
		String USE_JDBC = "true";
		String DATABASE_DLL = "crdb_jdbc.dll";
		String JNDI_OPTIONAL_NAME = jndiName;
		String CONNECTION_URL = connectionURL;
		String DATABASE_CLASS_NAME = driverName;

		// The next few parameters are optional parameters which you may want to
		// uncomment
		// You may wish to adjust the arguments of the method to pass these
		// values in if necessary
		// String TABLE_NAME_QUALIFIER = "new_table_name";
		// String SERVER_NAME = "new_server_name";
		// String CONNECTION_STRING = "new_connection_string";
		// String DATABASE_NAME = "new_database_name";
		// String URI = "new_URI";

		// Declare variables to hold database User Name and Password values
		String DB_USER_NAME = username;
		String DB_PASSWORD = password;

		// Obtain collection of tables from this database controller
		if (reportName == null || reportName.equals("")) {
			Tables tables = clientDoc.getDatabaseController().getDatabase().getTables();
			for(int i = 0;i < tables.size();i++){
				origTable = tables.getTable(i);
				if (tableName == null || origTable.getName().equals(tableName)) {
					newTable = (ITable)origTable.clone(true);

					// We set the Fully qualified name to the Table Alias to keep the
					// method generic
					// This workflow may not work in all scenarios and should likely be
					// customized to work
					// in the developer's specific situation. The end result of this
					// statement will be to strip
					// the existing table of it's db specific identifiers. For example
					// Xtreme.dbo.Customer becomes just Customer
					newTable.setQualifiedName(origTable.getAlias());

					// Change properties that are different from the original datasource
					// For example, if the table name has changed you will be required
					// to change it during this routine
					// table.setQualifiedName(TABLE_NAME_QUALIFIER);

					// Change connection information properties
					connectionInfo = newTable.getConnectionInfo();

					// Set new table connection property attributes
					propertyBag = new PropertyBag();

					// Overwrite any existing properties with updated values
					propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);
					propertyBag.put("Server Type", SERVER_TYPE);
					propertyBag.put("Use JDBC", USE_JDBC);
					propertyBag.put("Database DLL",DATABASE_DLL );
					propertyBag.put("JNDIOptionalName",JNDI_OPTIONAL_NAME );
					propertyBag.put("Connection URL", CONNECTION_URL);
					propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);
					// propertyBag.put("Server Name", SERVER_NAME); //Optional property
					// propertyBag.put("Connection String", CONNECTION_STRING); //Optional property
					// propertyBag.put("Database Name", DATABASE_NAME); //Optional property
					// propertyBag.put("URI", URI); //Optional property
					connectionInfo.setAttributes(propertyBag);

					// Set database username and password
					// NOTE: Even if the username and password properties do not 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(DB_USER_NAME);
					connectionInfo.setPassword(DB_PASSWORD);

					// Update the table information
					clientDoc.getDatabaseController().setTableLocation(origTable, newTable);
				}
			}
		}

Edited by: Martin Zwernemann on Nov 16, 2010 9:11 AM

[Former Member]

Part2

		// Next loop through all the subreports and pass in the same
		// information. You may consider
		// creating a separate method which accepts
		if (reportName == null || !(reportName.equals(""))) {
			IStrings subNames = clientDoc.getSubreportController().getSubreportNames();
			for (int subNum=0;subNum<subNames.size();subNum++) {
				Tables tables = clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().getDatabase().getTables();
				for(int i = 0;i < tables.size();i++){
					origTable = tables.getTable(i);
					if (tableName == null || origTable.getName().equals(tableName)) {
						newTable = (ITable)origTable.clone(true);

						// We set the Fully qualified name to the Table Alias to keep
						// the method generic
						// This workflow may not work in all scenarios and should likely
						// be customized to work
						// in the developer's specific situation. The end result of this
						// statement will be to strip
						// the existing table of it's db specific identifiers. For
						// example Xtreme.dbo.Customer becomes just Customer
						newTable.setQualifiedName(origTable.getAlias());

						// Change properties that are different from the original
						// datasource
						// table.setQualifiedName(TABLE_NAME_QUALIFIER);

						// Change connection information properties
						connectionInfo = newTable.getConnectionInfo();

						// Set new table connection property attributes
						propertyBag = new PropertyBag();

						// Overwrite any existing properties with updated values
						propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);
						propertyBag.put("Server Type", SERVER_TYPE);
						propertyBag.put("Use JDBC", USE_JDBC);
						propertyBag.put("Database DLL",DATABASE_DLL );
						propertyBag.put("JNDIOptionalName",JNDI_OPTIONAL_NAME );
						propertyBag.put("Connection URL", CONNECTION_URL);
						propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);
						// propertyBag.put("Server Name", SERVER_NAME); //Optional property
						// propertyBag.put("Connection String", CONNECTION_STRING); //Optional property
						// propertyBag.put("Database Name", DATABASE_NAME); //Optional property
						// propertyBag.put("URI", URI); //Optional property
						connectionInfo.setAttributes(propertyBag);

						// Set database username and password
						// NOTE: Even if the username and password properties do not
						// 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(DB_USER_NAME);
						connectionInfo.setPassword(DB_PASSWORD);

						// Update the table information
						clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().setTableLocation(origTable, newTable);
					}
				}
			}
		}
	}

[Former Member]

Hello,

I divided the code snippet taken from JRCHelperSample.java in two parts. Otherwise it didn't fit in the thread.

Kind regards

Martin

Edited by: Martin Zwernemann on Nov 17, 2010 7:55 AM

[Former Member]

Now, how do I change the schema within the new changed data source? In the old schema it was A and in the new data source it is B, how do I reconfigure all objects to have the qualifier B?

Regards.

[former_member217070]

Hi, Karthikeyanb,

When changing data sources, one of the requirements is that the schemas must match.

Regards,

Bryan

[Former Member]

Re

[Former Member]

Hi,

I have used the same method with the following credentials:

String url = "jdbc:sqlserver://172.21.239.39:1433";

String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";

and it is providing the following error. But the same rpt file is opening when I am not changing the data source.

Again, for my surprise, it is successfully changing the first table, but when it goes for the second iteration inside the loop, the following exception comes.

[5/12/11 17:16:57:174 IST] 00000025 SystemOut     O [Ecolab Portal] 12 May 2011 17:16:57,174- ERROR   - JRCAgent27 detected an exception: JDBC Error: Incorrect syntax near 'SELECT'.
	at 	at com.crystaldecisions.reports.queryengine.driverImpl.o.eC(Unknown Source)
	at 	at com.crystaldecisions.reports.queryengine.driverImpl.o.if(Unknown Source)
	at 	at com.crystaldecisions.reports.queryengine.ax.if(Unknown Source)
	at 	at com.crystaldecisions.reports.queryengine.bc.byte(Unknown Source)
	at 	at com.crystaldecisions.reports.queryengine.bc.new(Unknown Source)
	at 	at com.crystaldecisions.reports.queryengine.bc.for(Unknown Source)
	at 	at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)
	at 	at com.crystaldecisions.reports.reportdefinition.datainterface.g.a(Unknown Source)
	at 	at com.crystaldecisions.reports.dataengine.bj.new(Unknown Source)
	at 	at com.crystaldecisions.reports.common.as.a(Unknown Source)
	at 	at com.crystaldecisions.reports.common.ae.a(Unknown Source)
	at 	at com.businessobjects.reports.sdk.b.k.a(Unknown Source)
	at 	at com.businessobjects.reports.sdk.b.w.m(Unknown Source)
	at 	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)
	at 	at com.crystaldecisions.proxy.remoteagent.x.a(Unknown Source)
	at 	at com.crystaldecisions.proxy.remoteagent.q.a(Unknown Source)
	at 	at com.crystaldecisions.sdk.occa.report.application.dd.a(Unknown Source)
	at 	at com.crystaldecisions.sdk.occa.report.application.ag.a(Unknown Source)
	at 	at com.crystaldecisions.sdk.occa.report.application.av.if(Unknown Source)
	at 	at com.crystaldecisions.sdk.occa.report.application.an.new(Unknown Source)
	at 	at com.crystaldecisions.sdk.occa.report.application.cb.for(Unknown Source)
	at 	at com.crystaldecisions.proxy.remoteagent.u.performDo(Unknown Source)

	at 	at consumptionInventory.ConInvIndividualRptBean.viewConInvIndividualRpt(ConInvIndividualRptBean.java:343)
	at 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:85)
	at 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:58)
	at 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:60)
	at 	at java.lang.reflect.Method.invoke(Method.java:391)
	at 	at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:127)
	at 	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:73)
	at 	at javax.faces.component.UICommand.broadcast(UICommand.java:312)
	at 	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:302)
	at 	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:416)
	at 	at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:77)
	at 	at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:220)
	at 	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:91)
	at 	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:197)
	at 	at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1572)
	at 	at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:762)
	at 	at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:89)
	at 	at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:89)
	at 	at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1924)
	at 	at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:89)
	at 	at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:472)
	at 	at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:411)
	at 	at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:101)
	at 	at com.ibm.ws.tcp.channel.impl.WorkQueueManager.requestComplete(WorkQueueManager.java:566)
	at 	at com.ibm.ws.tcp.channel.impl.WorkQueueManager.attemptIO(WorkQueueManager.java:619)
	at 	at com.ibm.ws.tcp.channel.impl.WorkQueueManager.workerRun(WorkQueueManager.java:952)
	at 	at com.ibm.ws.tcp.channel.impl.WorkQueueManager$Worker.run(WorkQueueManager.java:1039)
	at 	at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1471)

Please help me out.

Thanks in advance.