DB-AppView Sample Query Readme
A copy of this readme is included with the DB-DB
sample in your Liquid Data installation at
<BEA_HOME>/<WL_HOME>/liquiddata/samples/buildQuery/db-appview/readme.htm.
(The default install home for Liquid Data is bea/weblogic700.)
This readme includes the following topics:
What this
Query Demonstrates
The sample for customer care query illustrates how to use
Liquid Data to extract customer order handling information from two data
sources: a JCA ApplicationView and a relational database containing customer
information.
For example, due to historical reasons MyTelcom has two
separate order management systems: one for wireless service and the other for
broadband. The wireless information can only be accessed as an EIS system (via
J2EE Connector Architecture and Weblogic Application View), and the broadband
information can only be accessed through its relational database. The customer
ID is the same across two systems. Traditionally, an application developer
needs to write a set of data access methods and some application logic to
complete the information extraction and assembly. Using Liquid Data, we
demonstrate the use of one single Xquery statement to declaratively accomplish
the same task.
How to Run
the Query
- Start the Liquid Data Samples server.
- Start the Data View Builder.
- In the Data View Builder, open the following project
file:
<BEA_HOME>/<WL_HOME>/liquiddata/samples/buildQuery/db-appview/03.qpr
- Click the Test tab. (This shows the generated query
statement.)
- Click the "Run Query" button and view the XML
result.
If You
Want to Re-create the Query . . .
Configure the Data Sources
in the Administration Console
- Configure
the Wireless Application View data source as follows:
- JDBC connection pool: pbwpool (username=wireless,
password=wireless)
- JDBC data source: pbbbds
- Use Application Integration (AI) console to create an
Application View definition: avwlds
- Liquid Data "Application View" data source:
AV-WL
- Configure
the BroadBand relational database data source as follows:
- JDBC connection pool: pbbpool (username=broadband,
password=broadband)
- JDBC data source: pbbbds
- Liquid Data "Relational Database" data
source: PB-BB
Build the Query in the
Data View Builder
- Create a new project.
- Open the Data Sources you just defined: “AV-WL” and
“PB-BB”
- Add customerOrders.xsd from the repository as the
target schema
- Define a constant, “JOHN_1” and map it to AV-WL input
->FIRST_NAME, define a constant, “KAY_1” and map it to AV-WL input
->LAST_NAME.
- Map the First_Name, Last_Name, Customer_id from AV-WL
result element to the TARGET Customers->Customer corresponding
attributes similar to the Map Source to Target.
- Drag the Customer_id from AV-WL result element to PB-BB
Customer_Order table -> Customer_ID field and give hints “ppright”.
- Map from SOURCE PB-BB, the Customer_Order->Order_ID,
Customer_Order->Order_Date and Customer_Order_Line_Item->Price to
TARGET customers->customer->orders->order corresponding
attributes
- Drag from SOURCE PB-BB, the Customer_Order->Order_ID
to Customer_Order_Line_Item->Order_ID and give hints “ppright”.
- Click “Condition” tab, drag “eq” from “Functions” tab
to a new row in “Condition”, and then drag from SOURCE PB-BB
Customer_Order_Line_Item->Status and a new constant “OPEN” to the same
row.
- Run the query. (Click the Test tab and click Run Query
button.)
Reference
- You can view the BroadBand database schema file at
<BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/BROADBANDP.sql
- You can view the target schema at
<BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/customerOrders.xsd
- You can view the query statement at
<BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/03.xq