RDBMS Event Generator

Creating a Data Source

Before you can create RDBMS events and define channel rules for your new events, you must create a data source that points to the database and hence the Table on which the channel rule (Event) will be defined. For more information on defining channel rules for RDBMS Event Generators, see "Defining Channel Rules for a RDBMS Event Generator" in Managing WebLogic Integration Solutions Guide.

After you create your data source, you must also create the message broker channels that the RDBMS event publishes to, before you can define channel rules for any RDBMS events.

The following sections detail how you create a data source and message broker channels:

• Creating a Data Source
• Creating Message Broker Channels

 

---

Creating a Data Source

Before you create a data source, you must be aware of the database connection parameters for the data source you want to connect to. If you are unaware of the database connection parameters, contact your system administrator.

The steps involved in creating a data source are:

• Adding the Driver to the Classpath
• Creating your Data Source Connection
• Defining the Connection Pool Configuration
• Configuring a New Data Source

Adding the Driver to the Classpath

The first step in creating a data source is adding the required driver to the WebLogic classpath.

To add a driver to the WebLogic classpath:

Locate the <bea_home>\weblogic81\samples\domains\<domain name> directory.
Open the setDomainEnv.cmd and scroll to the bottom of the file where is reads:

@REM SET THE CLASSPATH

set CLASSPATH=%PRE_CLASSPATH%;%WEBLOGIC_CLASSPATH...

Add the following statement immediately AFTER @REM SET THE CLASSPATH:

set CLASSPATH=%WL_HOME%\server\lib\driver.jar;%CLASSPATH%

where driver.jar is the jar file for the database driver that you want to add to the classpath. For example, for an Oracle driver, the command would read:

set CLASSPATH=%WL_HOME%\server\lib\ojdbc14.jar;%CLASSPATH%

Save the file.

Note: Before you use a MS SQL XA driver to create events, you must follow the steps detailed in "Installing Stored Procedures for JTA" in The MS SQL Service Driver.

Creating your Data Source Connection

The next step in creating a data source is creating the connection that you will use to connect to the data source.

To create a data source connection:

Start the WebLogic Server using the startWebLogic.cmd command or from WebLogic Workshop.
Launch the WebLogic Server Console by entering http://localhost:7001/console in a Web browser or by selecting Tools—>WebLogic Server—>WebLogic Console....
Enter your username and password and click Sign In. The default username and password are both "weblogic."

The WebLogic Server Home page is displayed, as shown in the following figure.




 

Click Connection Pools under JDBC in the Service Configurations section.

The JDBC Connection Pools page is displayed, as shown in the following figure.




 

Click Configure a new JDBC Connection Pool.

The Configure JDBC Connection Pool page is displayed, as shown in the following figure.




 

Select the database type that you are going to connect to from the Database Type drop-down list.

For a list of the supported databases, see Supported Databases.

Select the database driver you want to use from the Database Driver drop-down list.

Note: WebLogic Server JDBC certified drivers are marked with *.

Click Continue.

The Define Connection Properties page is displayed, as shown in the following figure.




 

The fields that appear on this page depend on the database and driver selected on the previous page.

Enter a name for your connection in the Name field.
Enter the other connection details and click Continue.

Note: If you want to configure a trigger type event, it is recommended that the database account user name you enter in the Database User Name field is the same as the schema name of the table on which you want to configure the trigger type event. The account name you use must also have permission to CREATE/DROP tables and triggers. If you are configuring an Oracle database, the account name you use must also have permission to CREATE/DROP tables, triggers, and sequences.

The Test Database Connection page is displayed, as shown in the following figure.




 

The fields that appear on this page depend on the database and driver selected on the previous page.

Make sure the details displayed on this page are correct and click Test Driver Configuration.

It is recommended that you test the driver configuration before proceeding to the next step. If you do not test the driver configuration and you have made an error in the previous steps, you will not be able to connect to the required database when you try to configure your RDBMS Event Generator.

An error message will appear at the top of the page if there is a problem with your connection settings.

When your configuration passes the test, the Create and Deploy page is displayed, as shown in the following figure.




 

If there is more than one server in the domain you specified, check the server you want to deploy on and click Create and deploy.

The connection is created and deployed and you are returned to the JDBC Connection Pools page.

Defining the Connection Pool Configuration

The next step is defining the connection pool configuration for your data source.

To define the connection pool configuration:

On the JDBC Connection Pools page click the name of your new connection.

The Connection Pool Configuration page is displayed, as shown in the following figure.




 

Click the Connections tab.
Enter your configuration details in the fields provided.
If you are using a XA driver, click Show in the Advanced Options section.
Make sure that the Supports Local Transaction option is selected.
Click Apply.

Configuring a New Data Source

The final step in creating your data source connection is creating a new data source.

To create a new Data Source:

On the WebLogic Server Home page, click Data Sources under JDBC in the Service Configurations section.

The JDBC Data Sources page is displayed, as shown in the following figure.




 

Click Configure a new JDBC Data Source.

The Configure the Data Source page is displayed, as shown in the following figure.




 

Enter a name for the new data source in the Name field.
In the JNDI Name field, enter the JNDI path to where the data source is bound.
If you are using a non-XA driver, check the Emulate Two-Phase Commit for non-XA Driver checkbox.
Click Continue.

The Connect to Connection Pool page is displayed, as shown in the following figure.




 

Select the connection you created in Creating your Data Source Connection from the Pool Name drop-down list.
Click Continue.

The Target the Data Source page is displayed, as shown in the following figure.




 

Select the servers and clusters on which you want to deploy this JDBC data source. In most cases, you should deploy the data source to the same servers and clusters as the associated connection pool. The deployment targets of the associated connection pool are selected by default.
Click Create.

The new data source is created and you are returned to the JDBC Data Sources page.

Before you can create your RDBMS Event Generators in the WebLogic Integration Administration Console and define channel rules for these new event generators, you must create the message broker channels to which your new event generators will publish.

 

---

Notes on Creating a Data Source

You should consider the following:

• For DB2, Oracle, and MS SQL databases, the event generator uses DetabaseMetadata.getUserName() as the schema name to create database artifacts for Tables, Triggers, and Sequences (Oracle only).
• If you configure two tables with the same name in different schemas, you will get a StringIndexOutOfBoundsException. For example, the exception will occur if there are 2 tables with the same name in different schemas, like MASAA01.prod and dbo.prod in the same Catalog with the login name MASAA01, and the user creates an event on dbo.prod.

The exception will occur in this case, since a MS SQL Stored procedure called 'sp_columns' is used to retrieve the columns in the Table and their data types. The stored procedure takes only the Table name and not the fully qualified Table name. So it always returns the columns from MASAA01.prod instead of the dbo.prod columns, even though the dbo.prod columns should be received.

• If you want to create Trigger type events on a Sybase database, you must configure the events on the dbo schema.

 

---

Creating Message Broker Channels

This section describes how you create the message broker channels that your RDBMS Event Generator will publish to. You need to create three different types of message broker channel:

• XML channel
• String channel
• RawData channel

To create message broker channels:

Start WebLogic Workshop and ensure that the server is running.
Click File—>New—>Application and provide a name for the application.
In the Application pane, right-click the Schemas folder and select New—>Channel File.




 
Enter a name for the channel and click Create.

The code for the new channel is displayed.

If you have a pre-configured XML channel file, replace the code for the newly created channel with your own custom code. If you do not have your own custom code, you can use the code displayed below:

<?xml version="1.0"?> 

<!--

	 A sample channel file 

	 Change "channelPrefix" and <channel/> elements to customize 

	 The following namespaces are used in this sample file. These 

	 namespaces refer to schemas that must be present in a schema 

	 directory. 

-	 xmlns:eg=http://www.bea.com/wli/eventGenerator" is used 

	 for event generator metadata references 

-	 xmlns:dp="http://www.bea.com/wli/control/dynamicProperties" is 

	 used by the file event generator to pass payload for pass-by-filename 

-	 xmlns:oagpo="http://www.openapplications.org/003_process_po_007" is used for a sample payload description 

--> 

<channels xmlns="http://www.bea.com/wli/broker/channelfile" 

		 channelPrefix="/SamplePrefix" 

		 xmlns:eg="http://www.bea.com/wli/eventGenerator" 

		 xmlns:dp="http://www.bea.com/wli/control/dynamicProperties" 

		 xmlns:oagpo="http://www.openapplications.org/003_process_po_007"> 

	 <channel name ="Samples" messageType="none"> 

		 <!-- A simple channel passing XML --> 

		 <channel name ="SampleXmlChannel" messageType="xml"/> 

		 <!-- A simple channel passing rawData --> 

		 <channel name ="SampleRawDataChannel" messageType="rawData"/> 

		 <!-- A simple channel passing a string --> 

		 <channel name ="SampleStringChannel" messageType="string"/> 

	 </channel>

</channels>

Click File—>New—>Process File.
Name the new file and click Create.
Double-click this new process file.
Double-click the Starting Event node.

The Node Definition dialog is displayed.




 

Select the Subscribe to a Message Broker Channel and start via an event and close the dialog.
Double-click on the node again.

The Node Details dialog is displayed.




 

Select the SampleXmlChannel from the Channel Name drop-down list. If you have configured your own XMLChannel message broker, select it instead.
If desired, you can click View Code in the bottom left-hand corner and enter the following System.out message:

System.out.printIn(System.currentTimeMillis()+"-"+x0);

Click the Receive Data tab and enter the variable details.
Close the file and click Build—>Build Application.

This creates the XML channel and the JPD is automatically deployed on the server.

To create the string channel, repeat the above steps but select SampleStringChannel instead of the SampleXmlChannel when you select the Channel Name.
To create the RawData channel, repeat the above steps but select SampleRawDataChannel instead of the SampleXmlChannel when you select the Channel Name.

For the XML Channel, there is a type-safe way of retrieving the XML contents:

Right-click on the Schemas folder in the Workshop project and click Import. Select the generated .XSD file from the Server domain directory. The .XSD is created in a folder with the same name as the channel rule definition.
Workshop automatically compiles the .XSD file into Java classes
For an event having the name testFirst, which publishes 2 columns of type CHAR and DATE, TableRowSet.xsd is the generated schema file. The contents of the subscription(..) method file outlined below illustrate how to use XmlObject using the generated classes.

import com.bea.wli.rdbmsEG.testFirst.TableRowSetDocument;

import com.bea.wli.rdbmsEG.testFirst.TableRowSetDocument.TableRowSet;

import com.bea.wli.rdbmsEG.testFirst.TableRowSetDocument.TableRowSet.TableRow;

....

public void subscription(com.bea.xml.XmlObject x0)

    {

        //#START: CODE GENERATED - PROTECTED SECTION - you can safely add code above this comment in this method. #//

        // input transform

        // parameter assignment

        //#END  : CODE GENERATED - PROTECTED SECTION - you can safely add code below this comment in this method. #//

        final TableRowSetDocument doc = TableRowSetDocument.Factory.newInstance();

        doc.set(x0); 

        System.out.println("The document: " + doc);

        final TableRowSet rowSet = doc.getTableRowSet();

        final TableRow[] rows = rowSet.getTableRowArray();

        for(int i = 0; i < rows.length; i++)

        {

            System.out.println("---" + (i + 1) + "---");

            System.out.println("CHAR1: " + rows[i].getCHAR1());

            System.out.println("DATE1: " + rows[i].getDATE1());

        }

    }

For a RawData Channel, the code snippet below illustrates how it can be used to retrieve data published by the RDBMS Event Generator:

import com.bea.data.RawData;

import java.io.ByteArrayInputStream;

import java.io.ObjectInputStream;

import weblogic.jdbc.rowset.WLCachedRowSet;

.

    public void subscription(com.bea.data.RawData x0)

    {

        //#START: CODE GENERATED - PROTECTED SECTION - you can safely add code above this comment in this method. #//

        // input transform

        // parameter assignment

        //#END  : CODE GENERATED - PROTECTED SECTION - you can safely add code below this comment in this method. #//

        try

        {

            ByteArrayInputStream arrayInputStream = new ByteArrayInputStream(rawData.byteValue());

            ObjectInputStream objectInputStream = new ObjectInputStream(arrayInputStream);

            WLCachedRowSet rowSet = (WLCachedRowSet) objectInputStream.readObject(); 

            System.out.println("-- Event --\r\n");

            rowSet.moveToCurrentRow();

            while (rowSet.next())

            {

                Map map = rowSet.getCurrentRow(); 

                for (Iterator iterator = map.keySet().iterator(); iterator.hasNext();)

                {

                    Object key = iterator.next();

                    System.out.println("  Column: " + key + ", Value: " + map.get(key));

                }

            }

        }

        catch(Exception e)

        {

            e.printStackTrace();

        }

    }

For more information on message broker channels, see How do I: Create Message Broker Channels? in Publishing and Subscribing to Channels in the WebLogic Workshop Help.

The Message Broker module in the WebLogic Integration Administration Console allows you to monitor and manage all the Message Broker channels in your application. To learn more, see Message Broker in Managing WebLogic Integration Solutions.

Once you have created your data source and message broker channels, you can create RDBMS Event Generators and define channel rules for these generators in the WebLogic Integration Administration Console.

For information on creating RDBMS Event Generators and defining channel rules for these generators, see Event Generators in Managing WebLogic Integration Solutions.