Exploring the WebLogic Integration 8.1 RDBMS Event Generator

by Mahadevan Krishnan
03/21/2007

Abstract

Event generators are widely used in integration applications where the business process decisions are driven by the events generated in the enterprise systems. BEA WebLogic Integration provides native event generators such as the File Event Generator, the JMS Event Generator, the Timer Event Generator, HTTP Generators, and the RDBMS Event Generator. These event generators help the enterprise application track the events asynchronously and enable it to take the appropriate actions as they occur. This article focuses on effectively using the RDBMS Event Generator provided by WebLogic Integration 8.1.

Introduction

It is very common that the data that drives a business is persisted in a database management system. WebLogic Integration's RDBMS Event Generator can assist enterprise applications track the changes to the database by raising significant events through Message Broker Channels. Enterprise applications that need to react to these events can simply subscribe to the appropriate channels.

Java processes can communicate asynchronously within the WebLogic Server instance by making use of message brokers. Message brokers provide guaranteed message delivery between a publisher and a subscriber. Message brokers are subdivided into Message Broker Channels, whose properties are very similar to Java Messaging Services, except Message Broker channels are optimized for the effective usage within WebLogic Integration processes.

An RDBMS Event Generator can be created from the WebLogic Integration Administration Console. WebLogic Integration's creation of triggers and tables in the database forms the basis for the RDBMS Event Generator implementation. It uses triggers to track the changes to the database, and it uses tables for storing the results to be published to a Message Broker Channel. The WebLogic Integration Administration Console provides the ability to create a RDBMS Event Generator by specifying a trigger type as Insert, Update, or Delete on the table level. It doesn't allow the developer to customize the trigger as part of the business requirements. This article briefs you on the RDBMS Event Generator along with the step-by-step procedure for creating RDBMS Event Generators with a customized trigger.

After reading this article you'll know how to create your own RDBMS Event Generator using WebLogic Integration 8.1 and the code to react to the generated events.

The Chemistry behind the RDBMS Event Generator

A couple of questions that come to mind immediately when creating an RDBMS Event generator are:

1. How does WebLogic Integration interact with databases when you create a RDBMS Event Generator through the WebLogic Integration Administration Console?
2. How does WebLogic Integration generate events?

The moment you configure the event generator, WebLogic Integration makes use of the data source and connection pools defined in WebLogic Server to get a connection to the database by using the specified schema. After acquiring the necessary connections to the database, it does the following:

1. Creates database triggers on the tables configured in the Administration Console according to the specified trigger type.
2. Creates a shadow table on the schema that aids WebLogic Integration in identifying the events generated from the database and passing the events on to the subscribers through the Message Broker Channels.
3. Create a database sequence to generate sequence numbers as and when the event is fired. This helps WebLogic Integration to identify the records uniquely within the shadow table.

As soon as WebLogic Integration finds a record in the shadow table (during polling at a regular interval, as specified in the Administration Console), it publishes constructed XML messages (from the values stored in the shadow table) to the message broker channel for the subscribers to consume. After publishing the XML to the Message Broker Channel, WebLogic Integration records from the shadow table are removed.

As you can see, there are two independent processes here: the database triggers that update tracking database records when certain event trigger conditions are met; and, a regular poll by WebLogic Integration that checks for these tracking database records and that generates the actual Message Broker Channel messages.

The Structure of Trigger and Tables Used by WebLogic Integration

Let's look more closely at the triggers and tables created by WebLogic Integration.

Database sequence

WebLogic creates a sequence inside the database by following a naming convention of EventGeneratorName_BEA_SEQ.

Table and trigger

The table created by WebLogic Integration, inside the database to store the records to be published, follows the nomenclature EventGeneratorName_BEA_SDW. Consider a table containing three fields: Fieldname1, Fieldname2, and Fieldname3. If an RDBMS Event Generator is created to publish Fieldname1 and Fieldname2, WebLogic Integration will create a table with the following structure:

Fieldname1
OLD_Fieldname1
Fieldname2
OLD_Fieldname2
BEA_SEQ_ID
BEA_POLLED_AT

Structure of the trigger

The trigger created by WebLogic Integration to track the events from the database is named EventGeneratorName_BEA_TRG. Here's an example:

CREATE OR REPLACE EVENTGENERATORNAME_BEA_TRG
AFTER TRIGGER_TYPE ON SCHEMANAME.TABLENAME FOR
EACH ROW
DECLARE
TEMP INTEGER;
BEGIN
SELECT SCHEMANAME.EVENTGENERATORNAME_BEA_SEQ.NEXTVAL 
INTO TEMP FROM DUAL;
INSERT INTO SCHEMANAME.EVENTGENERATORNAME_BEA_SDW
(FIELDNAME1, OLD_FIELDNAME1, FIELDNAME2, OLD_FIELDNAME2,
BEA_SEQ_ID) VALUES 
(:NEW.FIELDNAME1,:OLD.FIELDNAME1,:NEW.FILEDNAME2,
:OLD.FIELDNAME2, TEMP);
END;

As you can see, this populates the records that store the event values.