# 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
- How to Run the Query
- If You Want to Re-create the Query . . .

## 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

1. Start the Liquid Data Samples server.
2. Start the Data View Builder.
3. In the Data View Builder, open the following project file: <WL_HOME>/liquiddata/samples/buildQuery/stored_procedure/GetOrderInfo.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. 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

1. Restart the Weblogic Server and Liquid Data
2. 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.
3. On the Liquid Data Admin Console, go to the Liquid Data->Configuration->Data Sources->Relational Databases and choose PB-WL.
4. 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

1. Create a new project.
2. Open the Stored Procedure you just defined from the Sources->Stored Procedures tab
3. Add getorderinfo.xsd from the repository as the target schema
4. Map totalnum and totalorder from SOURCE GetOrderInfo-Function ->results-> element to the TARGET result
5. Open the PB-WL data source from Sources->Relational Databases tab, open the CUSTOMER table.
6. Map the CUSTOMER_ID from Source PB-WL->db->CUSTOMER toGetOrderInfo-Function->Parameters->customer_id.
7. 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
8. 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
9. On the Toolbox->Constants tab, enter the Number constant '500' in the Number box and map it to the GetOrderInfo-Function ->Parameters->order_amount
10. 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