on 2010 Nov 05 9:14 AM
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!!
Request clarification before answering.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
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
Dear Martin:
What the "JRCHelperSample" class stands for? I can't find it in Crsytal report SDK for Java.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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);
}
}
}
}
}
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.
User | Count |
---|---|
80 | |
30 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.