Samples Tutorial

     Previous  Next    Open TOC in new window    View as PDF - New Window  Get Adobe Reader - New Window
Content starts here

Creating Data Services Based on SQL Statements

The SQL-Exit feature lets developers re-use SQL statements that are currently available in the source system. These user-defined SQL statements are bound in XQuery as external functions, in the same manner as all ALDSP sources.

 


Objectives

After completing this lesson, you will be able to:

 


Overview

Configuring the SQL-exit data source involves the following steps:

  1. Create the .xsd schema that describes the SQL results.
  2. Create the data service, including annotations, describing the result set.
  3. Associate an XML Type for the data service to the schema previously created.

When a user-defined SQL statement is used within other functions, the ALDSP engine will bind the SQL statement as a sub-query in a new SQL statement. To disable this functionality, the metadata property is Subquery, stored in the function's pragma, can be set to value false.

 


22.1 Creating a Data Service from a User-Defined SQL Statement

The SQL statement that will be used to create a new data service involves a join between the CUSTOMER and ADDRESS data services. You need to manually add all the necessary metadata to the new data service, before this query can execute. To do so, you will use metadata previously imported from the CUSTOMER and ADDRESS tables.

Objectives

In this exercise, you will:

 


Instructions

  1. Open the SQL_Statement.txt file, located in the <beahome>\weblogic81\samples\LiquidData\EvalGuide folder.
  2. Copy the text within the file. The text is:
  3. select "A"."CUSTOMER_ID", "A"."FIRST_NAME", "A"."LAST_NAME", "B"."ADDR_ID", "B"."CITY", "B"."STATE", "B"."ZIPCODE", "B"."COUNTRY" from "RTLCUSTOMER"."CUSTOMER" "A", "RTLCUSTOMER"."ADDRESS" "B" where "A"."CUSTOMER_ID" = "B"."CUSTOMER_ID" AND "B"."STATE" = ?
  4. Create a new folder in the DataServices project and name it SQL. You will use this folder to store a new data service based on user-defined SQL statements.
  5. Right-click the SQL folder and select Import Source Metadata.
  6. Select Relational from the Data Source Type and click Next.
  7. Select the SQL statement radio button and click Next. The SQL Statement page opens.
  8. Paste the copied text into the SQL Statement field.
  9. Select VARCHAR from the Type column for Position 1 and click Next. The Summary page opens.
  10. Figure 22-1 SQL Statement


    SQL Statement

  11. Rename the data service to MySQL.
  12. Figure 22-2 Summary for SQL-Based Data Service


    Summary for SQL-Based Data Service

  13. Click Finish. The MySQL data service and associated schema files are added to the SQL folder.

 


22.2 Testing Your SQL Data Service

You are now ready to test whether the MySQL data service can retrieve all customers who reside in California.

Objectives

In this exercise, you will:

Instructions

  1. Open MySQL.ds in Test View.
  2. Select MySQL(x1) from the Function drop-down list.
  3. In the parameter box enter CA
  4. Click Execute. The result set will show customer and address information for the state of California.
  5. Figure 22-3 Test Results for an SQL-Based Data Service


    Test Results for an SQL-Based Data Service

 


Lesson Summary

In this lesson, you learned how to:


  Back to Top       Previous  Next