> Samples Tutorial > Creating Data Services from Stored Procedures

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 from Stored Procedures

Enterprise databases utilize stored procedures to improve query performance, manage and schedule data operations, enhance security, and so forth. Stored procedures are essentially database objects that logically group a set of SQL and native database programming language statements together to perform a specific task.

You can import stored procedure metadata from any relational data available to the BEA WebLogic Server. ALDSP then uses that metadata to generate a physical data service that you can then use in logical data services.

 

Objectives

After completing this lesson, you will be able to:

 

Overview

Imported stored procedure metadata is quite similar to imported metadata for relational tables and views. Stored procedure metadata generally contains:

Note: If a stored procedure includes only one return value and the value is either simple type or a row set that is mapping to an existing schema, no schema file is created.

Handling Stored Procedure Row Sets

A row set type is a complex type, whose name can include:

The row set type contains a repeatable element sequence (for example, called CUSTOMER) with the fields of the row set.

Notes:

 

31.1 Importing a Stored Procedure into the Application

The first step in demonstrating ALDSP's ability to access data through a stored procedure is to import the procedure into the application.

Objectives

In this exercise, you will:

Instructions

  1. Import storedprocs as a Java project, adding it to the Evaluation application. The project is located in <beahome>\weblogic81\samples\LiquidData\EvalGuide.
  2. Build the storedprocs project. The storedprocs.jar file will be added to the Libraries folder.
  3. Shutdown the PointBase database, by stopping WebLogic Server.
    4. Note: Stopping WebLogic Server calls the PointBase shutdown script.
  4. Open the startPointBase.cmd in a text editor such as Notepad. The file is located in <beahome>\weblogic81\common\bin.
  5. In the startPointBase.cmd script, search for the string "@REM Add PointBase classes to the classpath" and add the complete path of the storedprocs.jar file below this line in the script as follows:
    6. set CLASSPATH=<beahome>\user_projects\applications\Evaluation\APP-INF\lib\storedprocs.jar;%POINTBASE_CLASSPATH%
    Note:
    • For reference, the modified startPointBase.cmd is included in samples\liquiddata\EvalGuide.
    • The CLASSPATH depends on your WebLogic Server installation. User can copy the correct path from the Output window of WebLogic Workshop.
  6. Start WebLogic Server, which in turn starts the PointBase database.
  7. Run CreditRatingStoredProcedure.java to define the stored procedures in PointBase.
  8. Click OK at the pop-up message.
  9. Confirm that the stored procedure executed, by reviewing the contents in the Output window. You should see the credit rating for CUSTOMER3.
    10. Note: Your credit rating may be different, based on the changes that you made in Exercise 23.2 Creating an Update Override.
    Figure 31-1 Output Window View of Stored Procedures Compilation


    Output Window View of Stored Procedures Compilation

 

31.2 Importing Stored Procedure Metadata into a Data Service

Importing a stored procedure's source metadata enables the generation of a stored procedure data service.

Objectives

In this exercise, you will:

Instructions

  1. Create a new folder in the DataServices project and name it StoredProcedures.
  2. Import stored procedures metadata, by completing the following steps:
    1. Right-click the StoredProcedures folder.
    2. Choose Import Source Metadata.
    3. Select Relational from the Data Source Type drop-down list, then click Next.
    4. Select cgDataSource from the Data Source drop-down list, then click Next.
    5. Expand the WEBLOGIC\Procedures folders.
    6. Select GETCREDITRATING_SP, click Add, and click Next.
    7. Accept the default settings displayed in the Configure Procedure window, then click Next.
      8. Note: Do not select GETCREDITRATING_SP as a side-effect procedure.
    8. Accept the default settings displayed in the Summary window and click Finish.
  3. Build the DataServices project.
  4. In the Application pane, confirm that there is a new data service, GETCREDITRATING_SP.ds, located in the StoredProcedures folder.
  5. Test the data service, by completing the following steps:
    1. Open GETCREDITRATING_SP.ds in Test View.
    2. Select GETCREDITRATING_SP(x1) from the Function drop-down list.
    3. Enter CUSTOMER3 in the Parameter field.
    4. Click Execute. You should see the credit rating for Customer3
    5. Review the results.

 

Lesson Summary

In this lesson, you learned how to:


  Back to Top       Previous  Next