Advertisements



Database Polling in OSB – Part I

Most of us have used DB Adapter in BPEL to perform operation in database. One of the features that is widely used is database polling when working with database. OSB also supports JCA DB adapter since it’s initial release in 11g. In a series of posts, i will show you different polling strategies in ACTION from OSB perspective.

I had used a use case of migrating data (smaller volume) from SI_EMPLOYEE table (source) to SI_EMPLOYEE_COPY (target) using DB adapter in OSB for this demo. And JDeveloper 11.1.1.6 and OSB 11.1.1.6 are used for this demo.

This post assumes that reader has basic understanding of working with DB Adapter in BPEL, OSB and using JDeveloper and Eclipse IDEs.

Create Business Service:

This business service merges the rows into target table using DB adapter. So create DB adapter files in JDeveloper by selecting the merge operation.

InsertDBOperation

In the next step of the wizard, select target table and finish the DB wizard to create all necessary files (.JCA, mappings, properties, WSDL and XSD). Import all of these files into Eclipse IDE and create business service from JCA file.

BusinessService

The JCA properties from configuration file can be verified and modified from the JCA transport tab of the business service.

JCA Transport

Delete Polling Strategy:

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Delete the Row(s) that were Read’.

Poll

table

AfterReadDelete

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

eclipsepoll

Complete the message flow in proxy service by adding Routing and Replace activity to transform $body as required by business service.

xquery

Deploy OSB project. Now insert a few rows in source table. Verify that polling happens and target table is populated with records and also the records got deleted from source table.

Before Polling:

b4poll

After Polling:

aftrpoll

Logical Delete Polling Strategy:

In this polling strategy, a column in the table is marked with particular value to designate the logical delete thus avoids DB adapter to pick it up the same record again and again. Currently our table does not have any column for this purpose, so add a column ISACTIVE VARCHAR2(1) in source table.

Create DB adapter files in JDeveloper by selecting Poll operation, source table and the polling strategy (After Read) as ‘Logical Delete’.

Poll

table

logdelete

Enter ‘Read Value’ as ‘N’ and ‘Unread Value’ as ‘Y’ so that DB adapter would only poll active records.

logdeleteac

In the next step of DB wizard, one can verify SQL statement used for polling and finish the wizard.

pollsql

Import all of these files (JCA, mappings file, properties file, WSDL and XSD) into Eclipse IDE and create proxy service from .JCA file. Also the JCA properties can be verified in ‘JCA Transport’ tab including the polling strategy and the polling interval (60 sec in this scenario).

logdelete1

Complete the message flow in proxy service by adding Routing and Replace activity to transform the payload as required by business service.

logdelete2

Deploy OSB project and disable the proxy DeleteRecordPS so that only our new proxy service will poll DB table.

disable

Insert a few rows in source table with value ‘Y’ for ‘ISACTIVE’ column.Verify that polling happens and target table is populated with records and also the records in source table are marked with value ‘N’.

Before Polling:

activewithY

withzero

After Polling:

withN

with107

The configuration jar of this demo OSB project can be downloaded from here.

Advertisements

0 Responses to “Database Polling in OSB – Part I”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Advertisements

Pages

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 344 other followers

Enter your email address to follow this blog and receive notifications of new posts by email.