Data Services Platform JDBC driver gives JDBC clients read-only access to the information supplied by data services. With the Data Services Platform JDBC driver, ALDSP acts as a virtual database. The driver allows you to invoke data service functions from any JDBC client, from custom Java applications to database, and from reporting tools, including Crystal Reports.
Objectives
After completing this lesson, you will be able to:
Access ALDSP via JDBC.
Integrate a Crystal Report file, populated by ALDSP, into your Web application.
Access ALDSP via Crystal Reports 11.
Overview
Data services built into ALDSP can be accessed using the Data Services Platform JDBC driver, which provides access to the ALDSP-enabled Server via JDBC APIs. With this functionality, JDBC clients—including business intelligence and reporting tools such as Business Objects and Crystal Reports—are granted read-only access to the information supplied by ALDSP services. The main features of the Data Services Platform JDBC driver are:
Supports SQL-92 SELECT statements.
Provides error handling; if an error is detected in SQL query, then the error will be reported along with an error code.
Performs metadata validation; the translator checks SQL syntax and validates it against the data service schema.
When communicating with ALDSP via a JDBC/ODBC interface, standard SQL-92 query language is supported. The Data Services Platform JDBC driver implements components of the java.sql.* interface, as specified in JDK 1.4x.
Data Services Platform JDBC driver gives JDBC clients read-only access to the information supplied by data services. With the Data Services Platform JDBC driver, ALDSP acts as a virtual database. The driver allows you to invoke data service functions from any JDBC client, from custom Java applications to database, and from reporting tools, including Crystal Reports.
12.1 Running DBVisualizer
WebLogic Platform includes DBVisualizer, which is a third-party database tool designed to simplify database development and management.
Before you start:
The Data Services Platform JDBC driver needs to be in your computer's CLASSPATH:
$BEA_HOME\weblogic81\liquiddata\lib\ldjdbc.jar
Similarly, the WebLogic JAR file needs to be in your computer's CLASSPATH:
$BEA_HOME\weblogic81\server\lib\weblogic.jar
The WebLogic Server needs to be running.
Make sure that your Evaluation application is deployed correctly to WebLogic Server.
Objectives
In this exercise, you will:
Create a database connection that enables DBVisualizer to access your Evaluation application as if it were a database.
Use DBVisualizer to explore your Evaluation application.
Instructions
Publish your Evaluation data service functions for SQL use. For details see "Publishing Data Service Functions for SQL Use" in the Designing Data Services chapter of the Data Services Developer's Guide.
Choose Start Programs BEA WebLogic Platform8.1 Other Development Tools DBVisualizer. The DBVisualizer tool opens.
Figure 12-1 DBVisualizer Interface
Choose Database Add Database Connection.
Select the JDBC Driver tab from the Connection Data section.
Enter the following parameters:
Connection Alias: LD
JDBC Driver: com.bea.dsp.jdbc.DSPJDBCDriver
Database URL: jdbc:dsp@localhost:7001/Evaluation
Userid: weblogic
Password: weblogic
Click Connect.
Figure 12-2 New Database Connection Parameters
Use DBVisualizer to explore your ALDSP application as if it were a database. Data service projects display as database schemas. Functions within a project display as a database view; functions with parameters display as database functions.
Select a tab (Database Info, Data Types, Table Types, Tables, and References) to view that category of information for all data services within your application. For example, selecting the Tables tab displays each data service as a table.
Figure 12-3 Tables
Double-click an element to view the values for a specific data service. For example, double-clicking the DataServices~CustomerDB element from the Table Schema column displays that data services values.
Figure 12-4 Table Column Values
12.2 Integrating Crystal Reports and Data Services Platform
The Data Services Platform JDBC driver makes data services accessible from business intelligence and reporting tools, such as Crystal Reports, Business Objects, Cognos, and so on. In this exercise, you will learn how to use the Date Service Platform JDBC driver in conjunction with Crystal Reports. (For ODBC applications, you can use JDBC to ODBC Bridge Drivers provided by vendors such as OpenLink, available as of this writing at http://www.openlinksw.com.)
Objectives
In this exercise, you will:
Install Crystal Reports View in a Web application.
Import a saved Crystal Report file and JSP into the Web application.
View the report from the Web application.
Instructions
Install Crystal Reports Viewer in the CustomerManagementWebApp by completing the following steps:
Right-click CustomerManagementWebApp.
Choose Install Crystal Reports.
Import a saved Crystal Reports file and JSP that displays the report by completing the following steps:
Right-click CustomerManagementWebApp.
Choose Import.
Navigate to <beahome>\weblogic81\samples\LiquidData\EvalGuide and select the SpendByCustomers.rpt and showCrystal.jsp files:
Click Import. You should see showCrystal.jsp and SpendByCustomers.rpt files within CustomerManagementWebApp.
Right-click the CustomerPageFlow folder.
Choose Import.
Select index.jsp, located in <beahome>\weblogic81\samples\LiquidData\EvalGuide.
Click Import and choose Yes when asked if you want to overwrite the existing index.jsp file.
Open CustomerPageFlowController.jpf, located in CustomerManagementWebApp\CustomerPageFlow.
Click the Start icon (or press Ctrl + F5) to run Workshop Test Browser.
In Workshop Test Browser, click Customer Report to test the report. The first invocation may take time to display.
Figure 12-5 Crystal Report
12.3 (Optional) Configuring JDBC Access through Crystal Reports
Crystal Reports 11 comes with a direct JDBC interface, which can be used to interact with the Data Services Platform JDBC driver.
Objectives
In this exercise, you will:
Install Crystal Reports software, JDBC driver, and Java server files.
Add environment variables.
Create a new JDBC data source in Crystal Reports.
Instructions
Install the Crystal Reports software, per the vendor's installation instructions.
Add the JAVA_HOME as an environment variable. For example:
JAVA_HOME=C:\j2sdk1.4.2_06
where:
C:\j2sdk1.4.2_06
identifies the Java SDK location on your computer.
Make sure that the jvm.dll is in the path variable for your computer. For example:
<$BEA_HOME>\jdk142_04\jre\bin\server
Locate the Crystal Reports configuration file (CRConfig.xml). By default it is located on your Windows system in the following directory:
Program Files/Common Files/Business Objects/3.0/java
Make the following changes to the file:
In the <Classpath> element add the location of ldjdbc.jar and weblogic.jar to the classpath element. For example:
C:\81sp5sql\weblogic81\server\lib\weblogic.jar;
C:\81sp5sql\weblogic81\liquiddata\lib\ldjdbc.jar;
In the < JDBCURL> element to point to the application that you want to connect to. For example:
jdbc:dsp@localhost:7001/Evaluation
In the <JDBCClassName> element point to the ALDSP JDBC driver class name:
com.bea.dsp.jdbc.Driver.DSPJDBCDriver
Set the <JDBCUserName> element to the user. For the Evaluation sample application the user is:
weblogic
Set the GenericJDBCDriver <Option> element to Yes.
Change the <DatabaseStructure> element from the default:
catalogs,tables
to:
catalogs,schemas,tables
Set the <LogonStyle> element to:
Standard
Create a new connection to a JDBC data source in Crystal Reports:
Select JDBC as the connection type in the Connection Standard Report Creation wizard.
Set the JDBC Driver to:
com.bea.dsp.jdbc.Driver.DSPJDBCDriver
Set the URL string to:
jdbc:dsp@localhost:7001/Evaluation
Provide a user name and password. For the Evaluation application that would be weblogic and weblogic.
Login to Crystal Reports. Once authenticated, Crystal Reports will display a view of the Evaluation application.
Lesson Summary
In this lesson, you learned how to:
Access ALDSP via JDBC.
Integrate a Crystal Reports file, populated by ALDSP, into your Web application.
Programs 
