- Set up the CRDB_JavaServer.ini file
- Create a new Web project with a JavaServer Page™ (JSP) that includes Crystal Reports
- Embed Crystal Reports in the JSP, and run it on the server
- Use a JDBC connection at design time and switch to a JNDI connection at run time
The example that you review here is based on Rational Application Developer V 126.96.36.199, with the default Crystal Reports V XI features installed. Most readers have access to the IBM WebSphere® V 6 test environment, including Rational Application Developer trial version users. Therefore, this article's sample Web application is set up to run on the WebSphere V 188.8.131.52 test environment, rather than V 5.1 test environment. It also uses IBM DB2® Universal Database V 8.1, which contains the SAMPLE database that you use to generate reports. For more information on Crystal Reports-based use cases, see the Rational Application Developer Help contents (Help > Help Contents > Developing Web Applications > Crystal Reports > Creating Reports). To find out where to download trial versions of these products, see IBM trial software in Resources. You can follow the Crystal Reports FAQs link on the Rational Application Developer Support page (see Resources) for answers to any licensing, upgrade, or any other quick questions about the Crystal Reports tool in Rational Application Developer.
This step is the same as Step 1 in Part 1 of this series. If you have already read Part 1 and have set up your environment, based on this step, you can go to Step 2. Otherwise, to set up this file, follow these steps:
- In the Rational Application Developer workspace, switch to the Data view (Window > Open Perspective > Data).
- Right-click in the Database Explorer view, and then select New Connection.
- Select Choose a Database Manager and JDBC Driver (see Figure 1).
Figure 1. New Database Connection dialog box
- Click Next.
- In the New Database Connection dialog box, enter the values shown in Figure 2, and then click Test Connection.
Figure 2. Specify connection parameters dialog box
- If the test connection works, click OK (Figure 3).
Figure 3. Test connection works
If you experience any JDBC connection problems, here's where to find help:
- Review your database product's documentation.
- See Resources for a DB2 Infocenter link.
- Check whether you can connect to your database without using the Rational Application Developer Data perspective by running a Java program that tests JDBC connectivity from the command line, instead. (See JDBC Test in Resources), which helps you troubleshoot JDBC connectivity for most databases from most vendors.)
- Find out whether Business Objects supports your development platform (that is, database, operating system, and so on) by checking Business Objects' Supported Platforms in Resources.
Note: The Rational Application Developer support page link in Resources mentions that IBM® z/OS® and iSeries™ platforms are not supported for Crystal Reports by Business Objects. It also suggests some supported methods for working around this limitation on these platforms.
- Keep the New Database Connection wizard open, and then open the CRDB_JavaServer.ini file from the following path:
C:\Program Files\Common Files\Crystal Decisions\2.5\bin\CRDB_JavaServer.ini.
- Modify the following values in the CRDB_JavaServer.ini file, as shown in Figure 4:
PATH:Point to the directory that contains the java.exe file (preferrably from the
jdk1.4.2installation, such as:
CLASSPATH:Copy the Class Location from the New Database Connection wizard, and then append it to the existing
CLASSPATH. If your class location path has spaces in it (such as
Program Files), include the Class Location path for each .jar file in quotation marks (example: "c:\program files\IBM\SQLLIB\java\db2jcc.jar").
IORFileLocation: Provide a qualified path for any temp folder on your local file system(* you need to have READ/WRITE permissons on it).
JDBCURL: Copy the Connection URL value from the New Database Connection wizard, and then replace the existing value with that value.
your database user name. You can change it later in the JDBC (JNDI) Connection Information wizard (Figure 12).
JDBCDriverName: Copy the JDBC driver class from the New Database Connection wizard, and then replace the existing value with that value.
Figure 4. CRDBJavaServer.ini file with JDBC connection settings
Note: It is better to use Microsoft® Windows® Notepad than any other text file editor, because other editors have been known to insert some invisible formatting characters that might cause the error shown in Figure 11, even when the relevant parameters contain correct values.
- Save the CRDB_JavaSever.ini file, and then click Cancel in the Database Connection wizard.
This step is similar to Step 2 in Part 1, except that the sample Web project here uses the WebSphere V 6.0 test environment, rather than V 5.1. Later, in Step 4, you will revisit sub-step 5 of this step (Step 2) to make your report start using JNDI at run time. For now, though, set it up to use JDBC at both design time and run time.
To create a new Web project, follows these steps:
- In your Rational Application Developer workspace, create a new dynamic Web project by selecting File > New > Dynamic Web Project.
- Select the settings shown in Figure 5.
Note: For this example, make sure that you select 2.4 as the servlet version and WebSphere Application Server V 6.0 as the Target Server, as Figure 5 shows.
Figure 5. New Dynamic Web Project dialog box
- Click Next, and then select Crystal Reports features in the Features dialog box, as shown in Figure 6.
Figure 6. Web Project Features dialog box
- Click Finish.
- In your Web project, right-click the WebContent folder, and then select New > Crystal Report.
- Select the settings shown in Figure 7 in the New Crystal Report Wizard.
Figure 7. New Crystal Report Wizard
- Click Finish.
- In the Crystal Reports Gallery dialog box, select the Report Wizard option (Figure 8).
Figure 8. Crystal Reports Gallery dialog box
- Click OK.
- In the Standard Report Creation Wizard dialog box, click Create New Connection > JDBC (JNDI) (Figure 9).
Figure 9. Standard Report Creation Wizard
- If your settings in the CRDB_JavaServer.ini file are configured correctly, the JDBC (JNDI) Connection settings dialog box (Figure 10) will dispay. The Connection URL and Database Classname should already be filled in with the correct values from the CRDB_JavaServer.ini file.
- Click Next.
Figure 10. JDBC (JNDI) Connection settings dialog box
Note: If, instead of the JDBC (JNDI) Connection settings dialog box, you get the error shown in Figure 11, see Resolve Java Server startup failure error in Resources.
Figure 11: JDBC (JNDI) Connection settings dialog box showing error
Note: For a more detailed guide to setting up your CRDB_JavaServer.ini file, see the CRDB_JavaServer.ini settings guide in Resources.
- Next, enter your
DB2 user IDand
passwordin the Connection Information dialog box (Figure 12).
Figure 12. Connection Information dialog box
Note: This dialog box picks up the user name that you provided in the CRDB_JavaServer.ini file (see Figure 4). You can change it here to any other database user name if you wish.
- Click Finish.
- In the Data dialog box shown in Figure 13, select the table that you want to use to generate the report from the tables available in your schema. For this example, choose the Department table.
Figure 13. Data dialog box
- Click Next.
- In the Fields dialog box (Figure 14), select the fields from the table that you want your report to display.
Figure 14. Fields dialog box
- Click Next.
- In the Grouping dialog box (Figure 15), select your grouping preference (for this example, accept the default value).
Figure 15. Grouping dialog box
- Click Next.
- In the Record Selection dialog box (Figure 16), select your record selection preference (for this example, accept the default value).
Figure 16. Record Selection dialog box
- Click Next.
- In the Report Style dialog box (Figure 17), select your report style preference (for this example, accept the default value).
Figure 17. Report Style dialog box
- Click Finish, and then save the changes in the report file (select File > Save).
This step is the same as Step 3 in Part 1 of this series.
To embed the Crystal Report, follow these steps:
- Create a
new JSPin your Web project.
- Embed the Crystal Report that you just created in the JSP by dragging the Report Page Viewer component from the Palette view in the Web perspective, as shown in Figure 18.
Figure 18. Drag the Report Page Viewer component into the JSP
This will open the New Crystal Report Page Viewer dialog box (Figure 19).
Figure 19. New Crystal Report Page Viewer dialog box
- Click Browse, and then select the .rpt file (the actual report file) in your Web project's WebContent folder.
- Click Finish.
- Save the changes in the JSP (File > Save).
- To run the page in the WebSphere V 184.108.40.206 test environment, right-click the JSP in the Project Explorer view, and then select Run > Run on Server to see the JSP that contains the Crystal Report. Figure 20 shows the report in the browser.
Figure 20. Browser showing the JSP that contains the Crystal Report
Some exceptions may occur during this process at run time. The following describes how to handle each kind of exception that may occur.
- If you get a JDBC driver runtime error (Figure 21) during this process, copy the DB2 Universal JDBC driver .jar files (db2jcc.jar, db2jcc_license_cu.jar, and db2jcc_license_cisuz.jar) from the < DB2-Install-directory >\SQLLIB\java directory to your <WebSphere-Application-Serverv6.0.x-Install-directory > \lib\ext directory.
Figure 21. JDBC driver runtime error
Note: If you are using WebSphere V 6.0.x test environment rather than the stand-alone IBM® WebSphere® Application Server V 6.0.x (<WebSphere-Application-Serverv6.0.x-Install-directory> = <RAD-install-directory>\runtimes\base_v6.):
- After you have copied the .jar files, restart the server by right-clicking the server in the Servers view.
- Then select Restart > Start.
- If you get the Database Logon page (Figure 22), provide your
password, and then click OK.
Figure 22. Database Logon page
- If you get the log4j error message in the Console view, see How to resolve log4j errors in Resources:
[2/14/06 17:38:36:435 EST] 00000031 SystemErr R log4j:ERROR setFile(null,true) call failed. [2/14/06 17:38:36:435 EST] 00000031 SystemErr R java.io.FileNotFoundException: C:\Documents and Settings\Administrator\crystal\jpe.log (The system cannot find the path specified) at java.io.FileOutputStream.openAppend(Native Method) at java.io.FileOutputStream.init(FileOutputStream.java:199) at java.io.FileOutputStream.init(FileOutputStream.java:124) at org.apache.log4j.FileAppender.setFile(FileAppender.java:272) at org.apache.log4j.RollingFileAppender.setFile(RollingFileAppender.java:156)
In the previous three steps, you learned how to embed Crystal Reports in your Web applications that use JDBC connections at design time and run time. This step explains how to use JDBC at design time and switch to JNDI at run time. To use JNDI at run time, first you have to create a data source. Then you will re-visit the Standard Report Creation Wizard, sub-step 9 of Step 2, to use this data source for making the JNDI connection at run time. Because your project is targeted to the WebSphere V 220.127.116.11 test environment, you can use the Rational Application Developer Enhanced EAR editor to create this data source.
Note: This data source will be created at the Application scope. If you need to create the data source at the Node, Cell, or Server scope, create it in the administrator's console for WebSphere V 18.104.22.168 test environment. For more information about creating data sources in the WebSphere administrator's console, click Creating Data sources on the WebSphere Application Server's Infocenter (see Resources).
To create your data source, follow these steps:
- In the Project Explorer view, double-click your enterprise application project's Application Deployment Descriptor (application.xml).
- In the Application Deployment Descriptor's editor, click the Deployment tab (see Figure 23).
Figure 23. Deployment tab
- In the Deployment tab, click the Authentication section (Figure 24) to open it.
Figure 24. Add Authentication Entry
- Click Add, beside JAAS Authentication List table (Figure 25), to open the Add JAAS Authentication Entry dialog box.
Figure 25. Add JAAS Authentication Entry dialog box
- Create a JAAS Authentication Entry with the settings that Figure 26 shows, and then click OK.
Note: Provide your
database user name and
password in the User IDand Password fields.
Figure 26. JAAS Authentication Entry settings
- Now click Add (beside the JDBC Provider List table), and then select the settings shown in Figure 27 in the Create a JDBC Provider dialog box.
- Then click Next.
Figure 27. Create a JDBC Provider (1 of 2)
- Create a
robinJDBCProviderwith the settings shown in Figure 28.
- Click Finish.
Figure 28. Create a JDBC provider (2 of 2)
Note: To add the correct JARs in the Class path section of the dialog box shown in Figure 28, first click Remove to remove the environment variable entries. Then click Add External Jars to browse to the file system and select the location of your DB2 Universal JDBC drivers. The default location is the
\Program Files\SQLLIB\java directory.
- Next, select the robinJDBCProvider in the JDBC Provider List, and then click Add (beside the Data Source table) to create a new data source called
robinDatasourcewith the settings shown in Figure 29 and Figure 30:
Figure 29. Create a data source (1 of 2)
Figure 30. Create a data source (2 of 2)
Note: Take special note of the values provided in the Name and JNDI Name fields.
- To specify the following properties in the Resource Properties table (the one right below the datasources table), click Edit and provide these corresponding values:
- Database Name:
- Server Name:
- Port Number:
Note: If you are using an SQL server or an Oracle database rather than DB2:
- On the Deployment tab (Figure 24), click each resource property row in the Resource Properties table, one by one, and click Edit button.
- If the resulting Edit a Resource Property dialog box shows the Required checkbox selected, then make sure that you provide a valid value for that resource property. Details are available in Vendor-specific datasource settings (link provided in Resources). For Oracle, as an example, you must define the URL resource property with the value
jdbc:oracle:thin:@ server_name :1521: database_name. For additional details, refer to your specific database vendor's data source-setup information.
- Now that you have set up your data source to be used at run time, follow Step 2 again, starting from sub-step 5, to create a new Crystal Report in the same Web project, using the same Crystal Report Wizard. (See Figure 7.) The only difference is in the JDBC (JNDI) settings dialog box in sub-step 12 (Figure 10), where you need to provide an additional JNDI Connection Name value, as Figure 31, shows.
Figure 31. JDBC (JNDI) dialog box with additional data source information
For comparison purposes, you can see that when you followed Step 2 before, the JNDI Connection Name field in this dialog box was left blank (as shown previously in Figure 10. JDBC (JNDI) settings dialog box). Figure 32 shows how it looks this time.
Figure 32. JDBC (JNDI) Settings dialog box
- Now, follow Step 3 again to create a new JSP, to embed this newly created Crystal Report in it, and to run this JSP on the server. If you provided all of the correct settings, you should get output similar to Figure 33.
Figure 33. JSP containing the Crystal Report in the browser
Because you provided a JNDI Connection Name value for this Crystal Report, JDBC will be used only at design time. At run time, Crystal Reports will automatically switch to JNDI and look up this
robinDatasource, using its JNDI name for retrieving the data from this database table.
At this point, one of the common questions is: What's the difference between using JNDI rather than JDBC at run time, and what's the advantage? What follows answers those questions.
The following comparison of outputs from the server Console view in Rational Application Developer helps you identify whether JDBC or JNDI is being used at run time for retrieving the data from backend tables. When the JSP's embedded Crystal Report is using JDBC at run time, the server's Console view shows an output similar to what follows. (This merely shows that the JSP is being rendered by the server. You get the same message when running any regular JSP).
[4/8/06 1:05:37:105 EDT] 499ba5aa WebGroup I SRVE0180I: [SampleWebEAR] [/SampleWeb] [Servlet.LOG]: /SampleJSP.jsp: init
However, when you use JNDI at run time instead (during the server startup), three things change:
- First, you will notice that your data source,
robinDatasource, gets bound to its JNDI name as
[8/20/06 0:52:36:469 EDT] 0000001c ResourceMgrIm I WSVR0049I: Binding robinDatasource_CF as eis/jdbc/robinDatasource_CMP [8/20/06 0:52:36:891 EDT] 0000001c ResourceMgrIm I WSVR0049I: Binding robinDatasource as jdbc/robinDatasource ..... ... .
- Second, when you run the JSP on the server, you will see the
Datasource Configurationand your specific
JDBC provider-related messages in the server's Console view.
..... ... . [8/20/06 21:07:18:016 EDT] 0000001e ServletWrappe A SRVE0242I: [SampleWebEAR] [/SampleWeb] [/sampleJSP.jsp]: Initialization successful. [8/20/06 21:07:23:141 EDT] 0000001e ConnectionFac W J2CA0294W: Deprecated usage of direct JNDI lookup of resource robinDatasource. The following default values are used: [Resource-ref settings] res-auth: 1 (APPLICATION) res-isolation-level: 0 (TRANSACTION_NONE) res-sharing-scope: true (SHAREABLE) loginConfigurationName: null loginConfigProperties: null [Other attributes] res-resolution-control: 999 (undefined) res ref or CMP bean name: null Database pool properties: null primeID: 0 isCMP1_x: false (not CMP1.x) isJMS: false (not JMS) [8/20/06 21:07:23:891 EDT] 0000001e InternalGener I DSRA8203I: Database product name : DB2/NT [8/20/06 21:07:23:906 EDT] 0000001e InternalGener I DSRA8204I: Database product version : SQL08026 [8/20/06 21:07:23:906 EDT] 0000001e InternalGener I DSRA8205I: JDBC driver name : IBM DB2 JDBC Universal Driver Architecture [8/20/06 21:07:23:906 EDT] 0000001e InternalGener I DSRA8206I: JDBC driver version : 2.10.27 [8/20/06 21:07:23:906 EDT] 0000001e WSRdbDataSour I DSRA8208I: JDBC driver type : 4
- Third among the differences in using JNDI at run time -- and the advantage -- is that when you use JDBC at run time, your end users will encounter the Database Logon Web page (as Figure 34 shows) every time that they access the JSP that contains the embedded report. But when you are using JNDI at run time, your end users do not have to explicitly provide their database user names and passwords every time. This is because the data source setup, using the Enhanced EAR editor, already set up your database credentials. At run time, the data source takes care of transferring your database credentials for you. Moreover, now you can get the benefits of connection pooling and other application server-provided functionality.
Figure 34. Database logon page
Some exceptions may occur during this process at run time. The following describes how to handle these exceptions.
Note: These exceptions are specific to using JNDI at run time. If you have get error messages while using JDBC at run time, review the exceptions explained previously at the end of Step 3.
If you get the error shown in Figure 35, Unexpected Query Engine error, see Resources for a link to Resolve Unexpected Query Engine error on Rational Application Developer Support site. However, if you are running Rational Application Developer V 22.214.171.124 or a later version, you are not likely to get this specific error. (That IBM Technote reports that this issue occurs only with Rational Application Developer V 6.0 and V 126.96.36.199 versions).
Figure 35. Unexpected Query Engine error
Just for your reference, in addition to getting that error in your browser, you will also get the following exception trace in the server's Console view:
..... ... . [8/20/06 0:53:59:828 EDT] 00000033 SystemOut O 2006-08-20 24:53:59 com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Unexpected Query Engine error---- Error code:-2147467259 Error code name:failed at com.crystaldecisions.sdk.occa.report.lib.ReportSDKException .throwReportSDKException(Unknown Source) at com.crystaldecisions.reports.reportengineinterface .JPEReportSource.getPage(Unknown Source) at com.crystaldecisions.report.web.event.aa.a(Unknown Source) at com.crystaldecisions.report.web.event.aa.a(Unknown Source) ..... ... .
If you get the
Error finding JNDI Name (robinDatasource) message in your browser (Figure 36), make sure that you have provided your data source's JNDI Name, not the Datasource Name itself in the Crystal Report Wizard's Connection dialog box (see sub-step 13 of Step 4 to check your settings).
Figure 36. JNDI Name Not Found error
If you need to confirm your JNDI Connection Name value in Connection dialog box of the Crystal Reports wizard, right-click in your report's white space > Database > Set Datasource Location, and then right-click your JDBC connection in the resulting Set Datasource Location dialog box, as shown in Figure 37, and then click Properties to check your JDBC Connection Properties (Figure 38).
Figure 37. Set Datasource Location
Figure 38. JDBC Connection Properties
If you got the JNDI name not found error, then you may have mistakenly provided your Datasource Name (
robinDatasource) rather than your JNDI Connection Name (
jdbc/robinDatasource) in the JNDI connection name field of the Crystal Report Wizard. To fix this, folow these steps:
- Right-click in the report's white space again > Database > Log On or Off Server. (See Figure 39.)
Figure 39. Log On or Off Server
- Then, in Data Explorer dialog box, under the Current Connections node, click your JDBC connection, and then click Log off> > Close. (See Figure 40.)
Figure 40. Data Explorer
- Next, right-click again in the report's white space Database Expert, and then click the JDBC (JNDI) node again in the left pane, and follow sub-step 13 of Step 4 to set up your report with the correct JNDI connection name.
- Now save the changes in your report (File > Save), and then right-click the WebSphere Test Environment V 188.8.131.52 server in the Servers view and right-click Restart Project to restart your EAR project on the server.
- Then run the JSP again on the server. This time, the report should display in the JSP correctly, without any errors.
Note: If there is any confusion about JNDI name, provide the same value (for example,
robinDatasource) in the Datasource Name and JNDI Name fields in the Modify Data Source dialog box (see sub-step 10 (Figure 30) in Step 4), and then provide the same value (
robinDatasource) in the JNDI Connection Name field in the JDBC (JNDI) dialog box (sub-step 13 in Step 4).
This completes Part 2 of this five-part series about embedding Crystal Reports in Web applications. In this article, you learned how to embed Crystal Reports that use JDBC at design time and switch to JNDI at run time. In Part 1 and Part 2 of this series, you have been visually dragging the Report Page Viewer component from the Crystal Reports JSP Tags drawer in the Palette view to embed your designed Crystal Reports in the JSPs.
Part 3 shows how to programmatically embed your designed Crystal Reports in the JSP, using Java™ Reporting Component (JRC) code. JRC provides many additional features, such as setting up parameters for your database's stored procedures, transferring database logon credentials, printing the reports, exporting the reports (in PDF, RTF, or RPT file formats), just to name a few. If your Crystal Reports are more than just basic reports, thus have these more advanced requirements, you will find the subsequent articles in this series very useful.
- Integrate Crystal Reports in Web applications using Rational Application Developer, Part 1:
Part 1 of this series.
- DB2 Infocenter:IBM DB2 UDB v8.x Infocenter.
Crystal Reports FAQs :FAQs about Crystal Reports Tooling in Rational Application Developer.
- JDBC Test
: Command line based JDBC Connectivity Test.
- Business Objects' Supported Platforms: List of Business Objects' supported platforms
Rational Application Developer support page
: Rational Application Developer support page that mentions the specific IBM
platforms that are not supported for Crystal Reports by
- How to resolve log4j errors: Rational Application Developer support page that provides a workaround for this issue.
Resolve Java Server startup failure error
: Business Objects support page that provides the
workaround for this issue.
- CRDB_JavaServer.ini settings guide: Business Objects support page that provides the complete information about configuring JNDI and JDBC connectivity in Crystal Reports 10
- Creating Data Sources: Link to Websphere Application Server Infocenter explaining data source creation steps>
- Vendor-specific datasource settings: Vendor-specific datasource settings
- Resolve "Unexpected Query Engine Error": Resolve "Unexpected Query Engine Error"
- Stay current with developerWorks technical events and Webcasts.
- IBM Rational Application Developer product page: Find technical documentation, how-to articles, education, downloads, and product information about Rational Application Developer.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- IBM Rational Application Developer: Download a trial version from developerWorks.
- Participate in the discussion forum.
- Rational Software Architect, Software Modeler, Rational Application Developer and Web Developer forum: Ask questions about Rational Application Developer.
Robin holds a Masters Degree in Computer Science from California State University, Sacramento. When he wrote this article, he worked as a Support Engineer for RAD and WSAD tools at IBM Toronto Labs. He now works as a Sr. Programmer/Analyst with a major telecommunications company (Rogers Communications Inc.) in Toronto.