Siva's Blog

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.

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.

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

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’.

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).

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

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:

After Polling:

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’.

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

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

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).

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

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

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:

After Polling:

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