Stored Procedure Sample Query Readme
A copy of this readme is included
with the stored procedure sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/stored_procedure/readme.htm.
(The default install home for Liquid Data is bea/weblogic700.)
This readme includes the following
topics:
What this Query Demonstrates
This query demonstrates how to use RDBMS's stored
procedures as Liquid Data data sources. Once configured as shown in the
following example, RDBMS's stored procedure will show up as a function
available for use in any Data View Builder client that connects to the
server.
For example, the MIS department
of a wireless service provider uses a database management system to manage
its customer and order information. The engineers have already invested
time and money in developing a full set of stored procedures with embedded
business logic. They do not want to re-implement all those business logic
in XQuery with Liquid Data. Instead, Liquid Data treats stored procedure
as a regular function and users can build queries on top of it. All the
existing investment in stored procedure will be preserved. In this example,
a stored procedure is developed to report the total outstanding balance
and number of outstanding orders of a particular customer. We show that
Liquid Data can use this stored procedure in a joint query with another
data source.
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: <WL_HOME>/liquiddata/samples/buildQuery/stored_procedure/GetOrderInfo.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
. . .
Compile the Stored Procedure Program
Locate the stored procedure source file, pbsp.java, in $LD_HOME\samples\buildQuery\stored-procedure
and compile it with the following command:
javac -classpath $BEA_HOME\weblogic700\samples\server\eval\pointbase\lib\pbserver42ECF183.jar
pbsp.java.
After the compilation, make a series of folders with the following
hierarchy structure: com/bea/ldi/sample,
put the pbsp.class file in the sample directory, and archive the com
folder and its content using the jar command. The resulting archive
(JAR) file will contain the stored procedure binary. Add the stored procedure
jar file to the JAVA classpath in your startWeblogic command file.
Create the Stored Procedure in the PointBase database
on Win2K
In the $LDHOME\samples\config\ld_samples\command,
run the command setenv.cmd and startPointBaseConsole.cmd. Put 'jdbc:pointbase://localhost:9092/LDDB'
as the URL in the 'Connect to Database' window and login as wireless/wireless
in order to login into Pointbase. Run the following sql statement in the
console:
create procedure GetOrderInfo( IN P1 VARCHAR(20), IN P2 INTEGER,
OUT P3 INTEGER, OUT P4 INTEGER)
LANGUAGE JAVA
SPECIFIC GetOrderInfo
EXTERNAL NAME "com.bea.ldi.sample.pbsp::GetOrderInfo"
PARAMETER STYLE SQL;
Configure the Data Sources in the Administration Console
-
Restart the Weblogic Server and Liquid Data
-
Create a stored procedure description file. A sample of the description
file can be found in $LD_HOME\samples\buildQuery\stored-procedure\pbsp.xsd.
Put your description file in $LD_HOME\samples\config\ld_samples\ldrepository\stored_procedures.
-
On the Liquid Data Admin Console, go to the Liquid Data->Configuration->Data
Sources->Relational Databases and choose PB-WL.
-
On the "Configure a Relational Database Data Source Description" screen,
pick your description file at the field of "Stored Procedure Description
File" and click on the "Apply" button.
Build the Query in the Data View Builder
-
Create a new project.
-
Open the Stored Procedure you just defined from the Sources->Stored Procedures
tab
-
Add getorderinfo.xsd from the repository as the target schema
-
Map totalnum and totalorder from SOURCE GetOrderInfo-Function ->results->
element to the TARGET result
-
Open the PB-WL data source from Sources->Relational Databases tab, open
the CUSTOMER table.
-
Map the CUSTOMER_ID from Source PB-WL->db->CUSTOMER toGetOrderInfo-Function->Parameters->customer_id.
-
On the Toolbox->Constants tab, enter the String constant 'JOHN_1' in the
String box and map it to the Source PB-WL->db->CUSTOMER->FIRST_NAME
-
On the Toolbox->Constants tab, enter the String constant 'KAY_1' in the
String box and map it to the Source PB-WL->db->CUSTOMER->LAST_NAME
-
On the Toolbox->Constants tab, enter the Number constant '500' in the Number
box and map it to the GetOrderInfo-Function ->Parameters->order_amount
-
Run the query. (Click the Test tab and click Run Query button.)
Reference
-
You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/getorderinfo.xsd
-
You can view the query statement at <WL_HOME>/liquiddata/samples/buildQuery/stored-procedure/getorderino.xq