Exploring the WebLogic Integration 8.1 RDBMS Event Generator
Pages: 1, 2, 3

Customizing the RDBMS Event Generator

The default event generator is simple and straightforward. It makes use of a database trigger to fire events to a channel based on the changes being made in the database. But there is no customization supported in the WebLogic Integration Administration Console. There, you can specify the table to monitor along with the event/trigger type and the columns to publish. But the Administration Console doesn't have provisions to accommodate the following:

  1. Specify the business rules to fire the event.
  2. Monitor a specific column of the table.

Though this cannot be achieved through the Administration Console, as we've just seen, the RDBMS Event Generator implementation is based on the database triggers to fire the events. It can be tailored to our business demands by appropriately modifying the triggers inside the database or by writing our own procedures to insert records into the shadow table. Though these aren't supported by BEA, we've been doing this for a while with great results.

Example Application

As an example of using the RDBMS Event Generator, we will develop a small Inventory Management Application. The purpose behind holding the inventory is to cater to the orders immediately. This system requires building in an intelligence to order fresh stocks immediately when the quantity of stock reaches the reorder point. But the details on whether the quantity on hand has reached the reorder point can be tracked down only from persistent stores such as a database or a file system.

This scenario demands an event generator that can inform the Fresh Stock Ordering Application to procure fresh stocks whenever the item reaches the reorder point.

Here's the structure of the Inventory table:

NAME                  NULL?     TYPE

--------------------- -------- --------------

PRODUCT_ID            NOT NULL VARCHAR2(10)

QTY_ON_HAND                    NUMBER

QTY_ON_ORDER                   NUMBER

COST                           NUMBER(14,2)

Let's now look at how to create and use an event generator.

Creating a RDBMS Event Generator

Here are the steps you need to follow to create the RDBMS Event Generator for the example Inventory Management Application:

  1. Start the WebLogic Integration Domain Server.
  2. Log in to Administration Console.
  3. Click the Event Generator, and then choose RDBMS ->Create New.
  4. Specify a unique Event Generator Name.
  5. On the RDBMS Event Generator Definition page, click Define New Channel Rule.
  6. On the RDBMS Event Generator Channel Rule Definition page, specify
    • Channel to which the event is to be published
    • Channel Description
    • Event Name (WebLogic Integration uses this name to create the triggers, tables, and sequences inside the database)
    • Polling Interval
    • Data source used to connect to the database; schema used must have privileges for creating triggers, tables, and sequences
    • Max Rows per Poll
    • Max Rows per Event
    • Publish As (Specify the User to impersonate); if this is not specified, messages will be published to the channels as if originating from an anonymous user.
    • Event Type (this aids WebLogic Integration in choosing the appropriate trigger (Insert/Update/Delete)
    • Table Name; specify table and column names on the table to publish by selecting the link Table Name and Select Table Columns to publish
    • Note: Use uppercase for the schema and table names in case you want to specify the table name manually
  7. Click Submit.

Figure 1 shows some of these settings for our example.

Event2
Figure 1. Defining the channel rule for the RDBMS Event Generator (click the image for a larger version)

Here's a look at the INVENTORY_BEA_SDW table. Recall that records within this shadow table will be used by WebLogic Integration to identify the events fired from the database.

NAME                  NULL?    TYPE

---------------------- -------- -------------

PRODUCT_ID                      VARCHAR2(10)

OLD_PRODUCT_ID                  VARCHAR2(10)

QTY_ON_HAND                     NUMBER

OLD_QTY_ON_HAND                 NUMBER

QTY_ON_ORDER                    NUMBER

OLD_QTY_ON_ORDER                NUMBER

COST                            NUMBER(14,2)

OLD_COST                        NUMBER(14,2)

BEA_SEQ_ID             NOT NULL NUMBER(28)

BEA_POLLED_AT          NOT NULL NUMBER(19)

Here's a look at the trigger that WebLogic Integration created on the table being monitored:

CREATE OR REPLACE TRIGGER INVENTORY_BEA_TRG

AFTER UPDATE ON SYSTEM.INVENTORY FOR EACH ROW

DECLARE TEMP INTEGER;

BEGIN

SELECT SYSTEM.INVENTORY_BEA_SEQ.NEXTVAL INTO

TEMP FROM DUAL;

INSERT INTO SYSTEM.INVENTORY_BEA_SDW

(PRODUCT_ID, OLD_PRODUCT_ID, QTY_ON_HAND, 

OLD_QTY_ON_HAND, QTY_ON_ORDER, OLD_QTY_ON_ORDER, 

COST, OLD_COST, BEA_SEQ_ID)

VALUES (:NEW.PRODUCT_ID,:OLD.PRODUCT_ID,:NEW.QTY_ON_HAND,

:OLD.QTY_ON_HAND,:NEW.QTY_ON_ORDER,:OLD.QTY_ON_ORDER,

:NEW.COST,:OLD.COST,TEMP);

END;

Looking at the trigger, it's evident that an event will be triggered when the inventory table records are updated. But, as always, business demands more than this. For our example, let's assume that this event should be triggered only when the quantity on hand becomes less than a predefined threshold. How can this be customized to meet the business requirements?

Triggers can be easily modified provided you have some knowledge on the RDBMS. For example, business rules can be included as a part of a trigger and applied to the database. Here's a modified trigger that we used:

CREATE OR REPLACE TRIGGER INVENTORY_BEA_TRG 

AFTER UPDATE OF QTY_ON_HAND ON SYSTEM.INVENTORY

FOR EACH ROW

DECLARE TEMP INTEGER;

BEGIN

SELECT SYSTEM.INVENTORY_BEA_SEQ.NEXTVAL INTO

TEMP FROM DUAL;

                         
IF :NEW.QTY_ON_HAND < 10 THEN

INSERT INTO SYSTEM.INVENTORY_BEA_SDW

(PRODUCT_ID, OLD_PRODUCT_ID, QTY_ON_HAND, 

OLD_QTY_ON_HAND, QTY_ON_ORDER, OLD_QTY_ON_ORDER, 

COST, OLD_COST,BEA_SEQ_ID)

VALUES (:NEW.PRODUCT_ID,:OLD.PRODUCT_ID,:

NEW.QTY_ON_HAND,:OLD.QTY_ON_HAND,:NEW.QTY_ON_ORDER,

:OLD.QTY_ON_ORDER,:NEW.COST,:OLD.COST,TEMP);

                         
END IF;

END;

Pages: 1, 2, 3

Next Page ยป