on 09-10-2010 7:34 AM
Hi,
A report has been designed using Crystal Reports 2008.
The designed report is published on RAS and uses DSN to an SQLServer to execute. The executed report output file (.rpt)
is stored at a location on the disk.
I need to display the same report using Crystal Reports viewer (deployed as a WAR file on JBoss). However, when a user changes the report parameters and applies them, the report fails with the error
'JRCCommunicationAdapter - detected an exception: Error finding JNDI name (EKT_UAT)'
My questions are:
1. Can a report, designed to use DSN, be re-executed using JDBC parameters?
2. If so, how? How do I get round the JNDI name problem.
cheers,
Gaurav
Spot on Ted,
I have changed the DB parameters prior to the refresh so that it should take in the new params for connectivity.
The method is pasted below. Please let me know if I am missing anything in it.
In spite of this it tries to locate the property which uses the DSN name (EKT_UAT). What am I doing wrong?
your help is much appreciated.
<P STYLE="margin-bottom: 0in"> public static void
changeDataSource(ReportClientDocument clientDoc,</P>
<P STYLE="margin-bottom: 0in"> String reportName,
String tableName,</P>
<P STYLE="margin-bottom: 0in"> String username, String
password, String connectionURL,</P>
<P STYLE="margin-bottom: 0in"> String
driverName,String jndiName) throws ReportSDKException {</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> PropertyBag propertyBag = null;</P>
<P STYLE="margin-bottom: 0in"> IConnectionInfo connectionInfo
= null;</P>
<P STYLE="margin-bottom: 0in"> ITable origTable = null;</P>
<P STYLE="margin-bottom: 0in"> ITable newTable = null;</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Declare variables to hold
ConnectionInfo values.</P>
<P STYLE="margin-bottom: 0in"> // Below is the list of values
required to switch to use a JDBC/JNDI</P>
<P STYLE="margin-bottom: 0in"> // connection</P>
<P STYLE="margin-bottom: 0in"> String TRUSTED_CONNECTION =
"false";</P>
<P STYLE="margin-bottom: 0in"> String SERVER_TYPE = "JDBC
(JNDI)";</P>
<P STYLE="margin-bottom: 0in"> String USE_JDBC = "true";</P>
<P STYLE="margin-bottom: 0in"> String DATABASE_DLL =
"crdb_jdbc.dll";</P>
<P STYLE="margin-bottom: 0in"> String JNDI_DATASOURCE_NAME =
jndiName;</P>
<P STYLE="margin-bottom: 0in"> String CONNECTION_URL =
connectionURL;</P>
<P STYLE="margin-bottom: 0in"> String DATABASE_CLASS_NAME =
driverName;</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // The next few parameters are
optional parameters which you may want to</P>
<P STYLE="margin-bottom: 0in"> // uncomment</P>
<P STYLE="margin-bottom: 0in"> // You may wish to adjust the
arguments of the method to pass these</P>
<P STYLE="margin-bottom: 0in"> // values in if necessary</P>
<P STYLE="margin-bottom: 0in"> // String TABLE_NAME_QUALIFIER
= "new_table_name";</P>
<P STYLE="margin-bottom: 0in"> // String SERVER_NAME =
"new_server_name";</P>
<P STYLE="margin-bottom: 0in"> // String CONNECTION_STRING =
"new_connection_string";</P>
<P STYLE="margin-bottom: 0in"> // String DATABASE_NAME =
"new_database_name";</P>
<P STYLE="margin-bottom: 0in"> // String URI = "new_URI";</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Declare variables to hold
database User Name and Password values</P>
<P STYLE="margin-bottom: 0in"> String DB_USER_NAME = username;</P>
<P STYLE="margin-bottom: 0in"> String DB_PASSWORD = password;</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Obtain collection of tables
from this database controller</P>
<P STYLE="margin-bottom: 0in"> if (reportName == null ||
reportName.equals("")) {</P>
<P STYLE="margin-bottom: 0in"> Tables tables =
clientDoc.getDatabaseController().getDatabase().getTables();</P>
<P STYLE="margin-bottom: 0in"> for(int i = 0;i <
tables.size();i++){</P>
<P STYLE="margin-bottom: 0in"> origTable =
tables.getTable(i);</P>
<P STYLE="margin-bottom: 0in"> if (tableName == null
origTable.getName().equals(tableName)) {</P> <P STYLE="margin-bottom: 0in"> newTable = (ITable)origTable.clone(true);</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // We set the Fully qualified name to the Table Alias to keep the</P> <P STYLE="margin-bottom: 0in"> // method generic</P> <P STYLE="margin-bottom: 0in"> // This workflow may not work in all scenarios and should likely be</P> <P STYLE="margin-bottom: 0in"> // customized to work</P> <P STYLE="margin-bottom: 0in"> // in the developer's specific situation. The end result of this</P> <P STYLE="margin-bottom: 0in"> // statement will be to strip</P> <P STYLE="margin-bottom: 0in"> // the existing table of it's db specific identifiers. For example</P> <P STYLE="margin-bottom: 0in"> // Xtreme.dbo.Customer becomes just Customer</P> <P STYLE="margin-bottom: 0in"> newTable.setQualifiedName(origTable.getAlias());</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Change properties that are different from the original datasource</P> <P STYLE="margin-bottom: 0in"> // For example, if the table name has changed you will be required</P> <P STYLE="margin-bottom: 0in"> // to change it during this routine</P> <P STYLE="margin-bottom: 0in"> // table.setQualifiedName(TABLE_NAME_QUALIFIER);</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Change connection information properties</P> <P STYLE="margin-bottom: 0in"> connectionInfo = newTable.getConnectionInfo();</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Set new table connection property attributes</P> <P STYLE="margin-bottom: 0in"> propertyBag = new PropertyBag();</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Overwrite any existing properties with updated values</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Server Type", SERVER_TYPE);</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Use JDBC", USE_JDBC);</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Database DLL",DATABASE_DLL );</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("JNDI Datasource Name",JNDI_DATASOURCE_NAME );</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Connection URL", CONNECTION_URL);</P> <P STYLE="margin-bottom: 0in"> propertyBag.put("Database Class Name", DATABASE_CLASS_NAME);</P> <P STYLE="margin-bottom: 0in"> // propertyBag.put("Server Name", SERVER_NAME); //Optional property</P> <P STYLE="margin-bottom: 0in"> // propertyBag.put("Connection String", CONNECTION_STRING); //Optional property</P> <P STYLE="margin-bottom: 0in"> // propertyBag.put("Database Name", DATABASE_NAME); //Optional property</P> <P STYLE="margin-bottom: 0in"> // propertyBag.put("URI", URI); //Optional property</P> <P STYLE="margin-bottom: 0in"> connectionInfo.setAttributes(propertyBag);</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Set database username and password</P> <P STYLE="margin-bottom: 0in"> // NOTE: Even if the username and password properties do not change</P> <P STYLE="margin-bottom: 0in"> // when switching databases, the</P> <P STYLE="margin-bottom: 0in"> // database password is not saved in the report and must be set at</P> <P STYLE="margin-bottom: 0in"> // runtime if the database is secured.</P> <P STYLE="margin-bottom: 0in"> connectionInfo.setUserName(DB_USER_NAME);</P> <P STYLE="margin-bottom: 0in"> connectionInfo.setPassword(DB_PASSWORD);</P> <P STYLE="margin-bottom: 0in"><BR> </P> <P STYLE="margin-bottom: 0in"> // Update the table information</P> <P STYLE="margin-bottom: 0in"> clientDoc.getDatabaseController().setTableLocation(origTable, newTable);</P> <P STYLE="margin-bottom: 0in"> }</P> <P STYLE="margin-bottom: 0in"> }</P> <P STYLE="margin-bottom: 0in"> }</P> <P STYLE="margin-bottom: 0in"> // Next loop through all the subreports and pass in the same</P> <P STYLE="margin-bottom: 0in"> // information. You may consider</P> <P STYLE="margin-bottom: 0in"> // creating a separate method which accepts</P> <P STYLE="margin-bottom: 0in"> if (reportName == null |
---|
!(reportName.equals(""))) {</P>
<P STYLE="margin-bottom: 0in"> IStrings subNames =
clientDoc.getSubreportController().getSubreportNames();</P>
<P STYLE="margin-bottom: 0in"> for (int
subNum=0;subNum<subNames.size();subNum++) {</P>
<P STYLE="margin-bottom: 0in"> Tables tables =
clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().getDatabase().getTables();</P>
<P STYLE="margin-bottom: 0in"> for(int i = 0;i <
tables.size();i++){</P>
<P STYLE="margin-bottom: 0in"> origTable =
tables.getTable(i);</P>
<P STYLE="margin-bottom: 0in"> if (tableName ==
null || origTable.getName().equals(tableName)) {</P>
<P STYLE="margin-bottom: 0in"> newTable =
(ITable)origTable.clone(true);</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // We set the
Fully qualified name to the Table Alias to keep</P>
<P STYLE="margin-bottom: 0in"> // the method
generic</P>
<P STYLE="margin-bottom: 0in"> // This
workflow may not work in all scenarios and should likely</P>
<P STYLE="margin-bottom: 0in"> // be
customized to work</P>
<P STYLE="margin-bottom: 0in"> // in the
developer's specific situation. The end result of this</P>
<P STYLE="margin-bottom: 0in"> // statement
will be to strip</P>
<P STYLE="margin-bottom: 0in"> // the existing
table of it's db specific identifiers. For</P>
<P STYLE="margin-bottom: 0in"> // example
Xtreme.dbo.Customer becomes just Customer</P>
<P STYLE="margin-bottom: 0in">
newTable.setQualifiedName(origTable.getAlias());</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Change
properties that are different from the original</P>
<P STYLE="margin-bottom: 0in"> // datasource</P>
<P STYLE="margin-bottom: 0in"> //
table.setQualifiedName(TABLE_NAME_QUALIFIER);</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Change
connection information properties</P>
<P STYLE="margin-bottom: 0in"> connectionInfo
= newTable.getConnectionInfo();</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Set new
table connection property attributes</P>
<P STYLE="margin-bottom: 0in"> propertyBag =
new PropertyBag();</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Overwrite
any existing properties with updated values</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Trusted_Connection", TRUSTED_CONNECTION);</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Server Type", SERVER_TYPE);</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Use JDBC", USE_JDBC);</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Database DLL",DATABASE_DLL );</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("JNDI Datasource Name",JNDI_DATASOURCE_NAME
);</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Connection URL", CONNECTION_URL);</P>
<P STYLE="margin-bottom: 0in">
propertyBag.put("Database Class Name",
DATABASE_CLASS_NAME);</P>
<P STYLE="margin-bottom: 0in"> //
propertyBag.put("Server Name", SERVER_NAME); //Optional
property</P>
<P STYLE="margin-bottom: 0in"> //
propertyBag.put("Connection String", CONNECTION_STRING);
//Optional property</P>
<P STYLE="margin-bottom: 0in"> //
propertyBag.put("Database Name", DATABASE_NAME); //Optional
property</P>
<P STYLE="margin-bottom: 0in"> //
propertyBag.put("URI", URI); //Optional property</P>
<P STYLE="margin-bottom: 0in">
connectionInfo.setAttributes(propertyBag);</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Set database
username and password</P>
<P STYLE="margin-bottom: 0in"> // NOTE: Even
if the username and password properties do not</P>
<P STYLE="margin-bottom: 0in"> // change when
switching databases, the</P>
<P STYLE="margin-bottom: 0in"> // database
password is not saved in the report and must be</P>
<P STYLE="margin-bottom: 0in"> // set at
runtime if the database is secured.</P>
<P STYLE="margin-bottom: 0in">
connectionInfo.setUserName(DB_USER_NAME);</P>
<P STYLE="margin-bottom: 0in">
connectionInfo.setPassword(DB_PASSWORD);</P>
<P STYLE="margin-bottom: 0in"><BR>
</P>
<P STYLE="margin-bottom: 0in"> // Update the
table information</P>
<P STYLE="margin-bottom: 0in">
clientDoc.getSubreportController().getSubreport(subNames.getString(subNum)).getDatabaseController().setTableLocation(origTable,
newTable);</P>
<P STYLE="margin-bottom: 0in"> }</P>
<P STYLE="margin-bottom: 0in"> }</P>
<P STYLE="margin-bottom: 0in"> }</P>
<P STYLE="margin-bottom: 0in"> }</P>
<P STYLE="margin-bottom: 0in"> }</P>
Edited by: Gauravcj on Sep 13, 2010 8:39 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
So you want the report to refresh from the database?
If so, the best way is to design a report that connects to the database directly, and use that successful report as 'template' to specify the parameters for the report you want to change. There's info missing from your parameters.
Sincerely,
Ted Ueda
Thats brilliant.
I assumed that when you said
"If so, the best way is to design a report that connects to the database directly"
it meant, that I HAD to use a JDBC datasource and it worked.
In terms of parameters, when I view the report from InfoViewApp, a drop down appears but from the WebViewer, its only a text box. (The report designer did something to make the dropdown appear in RAS)
How do I get the dropdown of possible values in the webViewer?
cheers,
G
I assumed that when you said
"If so, the best way is to design a report that connects to the database directly"
it meant, that I HAD to use a JDBC datasource and it worked.
To clarify, what I mean is that:
Because of all the different connectivities Crystal supports - you'll note that the Supported Platforms are very specific as to RDBMs vendor, version and driver - beyond the "Generic" ones, the connectivity parameters are difficult to get right.
So if someone wants to get a complete list of connection properties, it's best to let the CR Designer handle it. The people who wrote the Designer wrote code to figure out all the connectivity details, and encapsulate it in a finite set of connection parameters. Those are the values you see in the Designer. It's neither obvious or straightforward, since the supported connectivities aren't just "Generic JDBC/ODBC/etc".
So you'd design a report against the RDBMs you want. Then you'd write code to read the connection property strings back. You'd use that as a 'template', when you want to change from one connectivity to another.
This doesn't even get into the bit of complexity that you might encounter if two different drivers to the same RDBMs present data in slightly incompatible data types. Rare, but it happens. In that case, you have to remap db fields.
Missing parameters - set the parameters for the report, get the report source, and pass it to the viewer class (CrystalReportViewer). The Viewer won't ask for parameters already set, it'll only ask for parameters not set.
A few unintuitive things can case missing parameters to appear. An infrequent but common case is where you have subreport parameters linked to main report db fields, and changing database connection breaks that link. Again, passing the report to the viewer after making your changes should help identify such cases.
Sincerely,
Ted Ueda
The change in parameters is triggering a database refresh attempt.
So the parameters aren't those used with saved data.
JNDI error comes up since when the Java CR engine sees a non-Java-based-data-connectivity, it tries to find a JNDI data source with the same name.
I'm assuming you want to use saved data filtering parameters.
Sincerely,
Ted Ueda
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.