BPEL SOA Suite Essentials for WLI Users

Comparison of the WLI Database Event Generator and the Oracle Database Adapter

by Nacho Lafuente and Miquel Lopez-Miralpeix

Published April 2009

Downloads for this article:
 Oracle SOA Suite

[ Page 1] [ Page 2] [Page 3]

Select the "Receive Data" tab to select where the event information will be temporarily stored. As this process is newly created, there are no variables and you will have to create a new one. Select "Create a new variable" option from the "Select variables to assign" combo box.

The variable that needs to be created should be of type XML and named "inputEvent". WLI will assign the XML document that represents the received event to this variable.

To finish the WLI process, you will insert a perform node to print out the value of the " inputEvent" variable.

Select the Perform node from the Node Palette, drag it to the process definition in the main window and drop it just after the initial node and before the Finish node.

The resulting process should look like this:

Double-click the Perform node and click on the " View code" link to show the process source code for this node.

Print out the value of the variable using the following code snippet.

public void perform() throws Exception {
            System.out.println("DatabaseEG inputEvent: " + inputEvent);
    }

Testing the WLI Process

Before receiving real database events, the newly created WLI process can be unit tested.

The WLI Process application should be deployed to a running domain before testing.

First, the WLI domain has to be started either manually or using Workshop for WebLogic.

Follow the steps below to start the WLI domain using Workshop for WebLogic and add the application to the server:

  • Select J2EE Perspective using Window > Open Perspective

  • Select Servers tab at the lower part of the GUI

  • Add a new server by right-clicking on the Servers tab. Select Oracle WebLogic Server version 10gR3 and fill in the domain directory. You should end up with a view similar to the picture below.
  • Start the configured WLI domain by clicking on the Run or Debug icons on top of the Servers tab. The Console tab should trace the output for the running WLI domain.
<26-mar-2009 19H47' CET> <Info> <WebLogicServer> <BEA-000377> 
<Starting WebLogic Server with Java HotSpot(TM) Client VM
Version 1.5.0_11-b03 from Sun Microsystems Inc.>
  • Now right-click on the configured domain and select "Add and Remove Projects" to show the following dialog.
  • Click " Add" to add the DatabaseEGEAR project to the domain and then "Finish". If the domain is running, the selected application will be deployed. If it is not running, please proceed to start the domain.

  • Select the process file DatabaseEG.java file at the Package Explorer and right-click. Select R un As > Run on Server. If this is the first time that any component is running on the server, the dialog below will be shown. This dialog is will help to select the server in which the required component will be executed. Select the pre-selected server and enable " Set Server as project default..." checkbox to avoid this dialog in the future.
  • A new web browser is opened to show the WLI Process Test Browser. Select " Test Form" tab to open a user-friendly HTML form that will be used to test this process.

Enter any valid XML into x0 text box and click on " subscription" button. This sample uses the <foo/> XML document.

The server log should show a line like the following.

DatabaseEG inputEvent: <foo/>

This trace demonstrates that the WLI process is working when an XML document is published to the message broker channel.

Creating the Database Event Generator

You will now configure a real database event generator so that the WLI process will receive events coming from the database. Event Generators are configured using the WLI Administration Console which is accessible from http://localhost:7001/wliconsole - adjust host and port to your specific domain.

Open the WLI console and select " Event Generators" from the left hand-side menu. Click on RDBMS > Create New to create a new Database event generator.

Fill in " databaseEG" as the generator name and click on Submit. Click on " Define a New Channel Rule" to create the specific event generator that will publish database events to a message broker channel.

Fill in the following information for the common event properties.

A Trigger Event Type will be selected for the purpose of this sample. This database event generator will be configured to publish a new event every time that a new employee is added to the HR.EMPLOYEES table. If you are using a different schema, you should choose another table.

Click on the " Table Name" link to select the required table. Navigate the schema, object type (TABLE) and finally select the required EMPLOYEES table. You can also introduce HR.EMPLOYEES in the " Table Name" text box.

To complete the database event generator configuration, you should select what table columns should be selected when a new row is inserted. Click on " Select table columns to publish..." link to show the following dialog.

Click on " Check All Columns" and Submit to publish all available columns. Now click on " Submit" at the main form to finish up the event generator configuration.

The newly created database event generator should be created and the following table reflects that it is already running

.

Functional Testing

Now everything is fully configured to test the whole use case. You will insert a new employee into HR.EMPLOYEES table by typing the following at the command prompt.

C:\WINDOWS>sqlplus HR/HR
SQL*Plus: Release 10.2.0.1.0 - Production on Lun Oct 27 23:06:03 2008
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> insert into EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, 
HIRE_DATE, JOB_ID) VALUES (500, 'Nacho', 'Lafuente', 'nacho.lafuente@bea.com', 
SYSDATE, 'AD_VP');
1 fila creada.
SQL> commit;
Confirmacion terminada.
SQL> exit
Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

The event generator poller has been configured using a frequency of 30 seconds. After that time has passed, the following trace should appear at the server log.

DatabaseEG inputEvent: <TableRowSet xsi:schemaLocation="http://www.bea.com/
WLI/RDBMS_EG/databaseeg TableRowSet.xsd" 
xmlns="http://www.bea.com/WLI/RDBMS_EG/databaseeg" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:wld="http://www.bea.com/2002/10/weblogicdata">
  <TableRow wld:RowId="0" wld:RowState="Inserted">
    <EMPLOYEE_ID>500</EMPLOYEE_ID>
    <FIRST_NAME>Nacho</FIRST_NAME>
    <LAST_NAME>Lafuente</LAST_NAME>
    <EMAIL>nacho.lafuente@bea.com</EMAIL>
    <PHONE_NUMBER xsi:nil="true"/>
    <HIRE_DATE>2008-10-27T23:12:34+01:00</HIRE_DATE>
    <JOB_ID>AD_VP</JOB_ID>
    <SALARY xsi:nil="true"/>
    <COMMISSION_PCT xsi:nil="true"/>
    <MANAGER_ID xsi:nil="true"/>
    <DEPARTMENT_ID xsi:nil="true"/>
</TableRow>
</TableRowSet> 

This trace demonstrates that the configured database event has been detected and published to WLI. The WLI process has received the event and printed the XML document that represents the event.

Implementing the use case in BPEL PM

This chapter will demonstrate how the same use case will be implemented in BPEL PM.

The use case will retrieve new employees based on the HIRE_DATE column. The steps are the following:

  1. Create a sequencing table as helper.
  2. Create a BPEL process
  3. Create a DBAdapter Partner Link
  4. Add a Receive activity to the BPEL Process

The process will then be ready for deployment.

Sequencing table

We need a helper table for knowing the last processed date in order to look for employees with HIRE_DATE newer than the date stored in this table.

The SQL statement for creating the table will look like this:

CREATE TABLE SEQUENCING_HELPER (TABLE_NAME VARCHAR2(32) NOT
NULL,LAST_READ_DATE DATE);

BPEL process

Step 0 - Installation of JDeveloper

This is very simple: go to OTN , download JDeveloper 10g and just unzip it.

Step 1 - Start JDeveloper

Double-click on jdeveloper.exe

Step 2 - Create an application

Click on File -> New ... and choose Application. Fill in the details and choose No Template as Application Template.

Step 3 - Create a BPEL Project

Click on File -> New ...

Enter the minimum details of the project (such as name and namespace) and the type of service: empty in this case.

DBAdapter Partner Link

STEP 1 - Drag'n'Drop the DB Adapter from services and enter the service name.

STEP 2 - Specify the database connection. If the database connection for querying schema information was not created under the JDeveloper connection's tab, it has to be created now.

It's important to note that the JNDI Name of the connection has to match the one defined in the Application server (if no name matches, the details of JDev connection will be used during execution time).

STEP 3 - Specify the type of service: Poll for New or Changed Records in a Table.

STEP 4 - Import the Employees table by choosing it with " Import tables ..." button.

STEP 5 - Remove any unneeded relationships (e.g. Remove all)

STEP 6 - Select the necessary attributes (e.g. keep all)

STEP 7 - Select the polling strategy: Update a Sequencing Table.

STEP 8 - Provide helper table details

STEP 9 - Set transaction and performance details (e.g. polling frequency).

STEP 10 - Review the select statement used for polling rows.

Receive activity

We need to create a receive activity for the BPEL Process to receive the messages generated by the Inbound DBAdapter partner link.

STEP 1 - Drag'n'drop a Receive activity from the process activities palette.

STEP 2 - Link it to the Partner link (drag left arrow to partner link).

STEP 3 - Change the name and check the Create Instance check box.

STEP 4 - Create a new variable by clicking OK (see previous screen shot).

The process is now ready to be deployed on a BPEL PM Server and tested. Just create a row in the database (as was explained in the WLI functional test) and you will see a new instance in the BPEL Console as a result.

Key Takeaways and Recommendations

Both WLI and BPEL PM are able to interact with databases in a similar way, and both tools are able to create process instances responding to database events, albeit using different polling strategies.

BPEL PM creates "inbound" interactions based on events coming from the database, while WLI requires the configuration of a Database Event Generator to retrieve those events. BPEL PM creates "outbound" interactions from any on-demand operation that is executed by a BPEL process instance, whereas WLI has no equivalent concept of outbound operations for process instances.

Note that, for inbound operations, WLI generates two different transactions: one to collect the message and the other to execute the associated process. Although BPEL PM can operate in the same fashion, it can also execute both operations in the same transaction, if desired. BPEL PM also offers a wide range of strategies for generating inbound interactions. WLI is limited to only two different polling strategies.

In addition, the Oracle DB Adapter also supports the mapping of multiple related tables to nested xml. Only flat xml mappings are available in WLI and these are extremely limited, especially as they do not support outbound selects. The built-in O/R mapping tool TopLink is also an important differentiator. A key benefit of TopLink is database platform portability and third party database support.

Finally, the SQL abstraction in the Oracle DB Adapter means that there is no need to code SQL. This improves maintainability and means that the same service can, for example, be deployed to DB2 in one scenario and Oracle DB in another. The table below summarizes the characteristics of each product:

Characteristic BPEL PM WLI
DBAdapter/RDBMS EG Configuration User's Guide User's Guide
Message structure XSD XSD
Transactional behaviour for event and process Separate or single (customizable) Always separate. There is one transaction that starts when polling the database and commits upon successful delivery of the event to the WLI message broker. The second transaction starts when the WLI process receives the event from the message broker subscription and eventually commits upon successful finalization of the process.
Polling strategies (inbound)
  • Physical Delete strategy polls the database table for records and deletes them after processing.
  • Logical Delete strategy involves updating a special field on each row processed, and updating the WHERE clause at run time to filter out processed rows.
  • Sequencing Table: Last-Read Id strategy involves using a helper table to remember a sequence value.
  • Sequencing Table: Last Updated strategy involves using a helper table to remember a last_updated value.
  • Control Table polling strategy involves using a control table to store the primary key of every row that has yet to be processed. This approach is non-intrusive as the creation of a shadow table with triggers is not necessary. It also allows polling for child updates
  • All these strategies can be combined with the possibility of setting the number of concurrent threads (see Batching & Debatching)
Only two different strategies available: event trigger and SQL polling.

Event Trigger strategy is similar to BPEL's sequencing table strategy as it involves a shadow trigger and table that store the values that have been deleted/insert/updated on the source table. Those shadow components (table and trigger) are created automatically by WLI.

SQL Polling strategy provides an slightly different approach to polling compared to BPEL. This strategy implies that two SQL sentences are indicated: the first will be used to retrieve some records (pre SQL), and the latter will be dynamically enriched with previous results (post SQL). The most common usage of this strategy is to retrieve records using a complicated query, and it usually contains a delete sentence as post SQL. BPEL also covers this use case by delegating complex queries to TopLink executing engine - the configuration of those complex queries is hidden and customized using the Oracle AS Database Adapter configuration Wizard.
Operations (outbound)
  • Insert for inserting new records into database.
  • Update for updating existing records from database.
  • Write for inserting or updating records without caring if records exist or not.
  • Delete for removing records from database.
  • Merge first reads the corresponding records in the database, calculates any changes, and then performs a minimal update.
  • Select for querying records given a criterion. Several queries can be defined as separate operations.
  • PureSQL allows the execution of arbitrary SQL bypassing TopLink.
  • QueryByExample, unlike the SELECT operation, does not require selection criteria to be specified at design time.
WLI does not provide specific outbound operations and it leverages WLI Controls and generic Beehive controls (see Controls). There is a database control that provides out-of-the-box database operations, e.g. including select, update, insert or delete.
SQL Abstraction Based on Oracle Toplink. No SQL coding required in most cases Any valid JEE strategy. WLI applications usually execute database operations by invoking the Beehive database control, JEE JDBC logic or 3rd party ORM engines like TopLink or Kodo.
Batching & Debatching support
  • NumberOfThreads: Number of concurrent threads.
  • MaxTransactionSize: Max number of rows fetch from DB to adapter in one transaction.
  • MaxRaiseSize: Max number of rows sent from adapter to BPEL as one message
  • No Of Threads (similar to BPEL's NumberOfThreads)
  • Max Rows Per Poll (similar to BPEL's MaxTransactionSize) - defines the maximum number of records to be retrieved by each processor thread in each polling cycle
  • Max Rows Per Event (similar to BPEL's MaxRaiseSize)- defines the number of records that will be part of the payload of a single event
[ Page 1] [ Page 2] [Page 3]

About the Authors
Nacho LaFuente Nacho Lafuente is a Consulting Technical Manager at Oracle SOA Consulting. He has been related to technology solutions in the middleware stack over the last 10 years and has participated into delivering many integration solutions to customers. Prior to joining Oracle at 2008 he was the Enterprise Architect Lead for Iberia at BEA Systems.
Nacho LaFuente Miquel López-Miralpeix is a Senior Principal Consultant within Oracle Consulting, specialized in SOA and integration, mainly focused on evangelizing, designing and deploying production solutions. He has been in Oracle for last three years, after being 10 years in different Oracle partners.