DB Adapter Polling Multiple Records From Database BPEL Process

DB Adapter Polling is one and only inbound operation available and other operations like Select, insert, update, merge, call to a stored procedure and execute pure sql statement are the outbound operations for a DB Adapter.

It means we can use the DB adapter polling operation to initiate a BPEL process which gives the input data to the BPEL process.

We can poll a single record or multiple records from a database with a given time period interval. Also we can poll to the database depending upon some conditions like depends upon the column values.

The number of records poll from a database is also a run time configurable parameter through Weblogic Enterprise Manager.

The below is given an example of a BPEL process which explains all the above mentioned DB Adapter concepts.

• Create SOA project with BPEL a process. Make that BPEL process as Define Service Later.

• In the Composite.xml drag a Database Adapter to the exposed service pane.

• In the DB Adapter wizard give the service name and connection details to the database.
• Choose the operation type as Poll for New or Changed records in a table.

• Click on the Import table button. Choose the table you want to poll. Then click on Next. On the relationship page we can choose a child tables to poll for example if we have Master and child tables then it will poll one master record with all the corresponding child records from the child table.
• Select the primary key for the table. It will took the primary key by default if the table has defined any primary key otherwise we can virtually define the primary key for the polling operation. 
• After that we need to select the columns which we need to poll from the table.
• Then we need to define what the DB Adapter will do after poll the record so that the record will identified as processed. For this we can define the following After read operations.

1. Delete the Rows that were read : It will delete the row from the table after the DB Adapter reads the corresponding record.
2. Logical Delete : It will update a corresponding column value to something different one.
3. Update a Sequencing table/file : It will update another table's column value to store the last read value for example it will store a sequence value used as primary key of the polling DB or the last polling date. 

• Let us choose the logical delete after read operation. I set the process flag column of the employee table here. if polled then adapter update its value from N to P.
• After that on the polling options page set the polling frequency and number of records you want to poll per transaction. set Data&rows per xml document field which will poll that many records from the table on a single polling attempt.

• After that on the selection criteria wizard put where condition of the query. Here I am polling those employees who have salary> 15000

• Connect with wire the DB Adapter with the BPEL process.
• In the BPEL drag a receive activity and receive input from the reference partner DB Adapter. Check the create instance check box on the Receive activity.

Testing the above BPEL DB Adapter :

• Inserted 6 records with process flag is N (unread records)into the Employee table as shown below. 

• After polling the records process_flag of the records become P(polled records). With empname A the salary is 10000 so it should not be polled by the DB adapter.

• As I configured the xml &rows per document as 2 it should create three instances in the weblogic enterprise manager. two of them polls 2 records and one instance polled one record.

Reconfigure the number of xml records polling from Weblogic Enterprise Manager:

• In EM go down to the Services and references section, there it will show the DB adapter. Click on that.

• Then click on the properties tab and set the maxRaiseSize property value.

• Now the DB adapter will poll 5 records at a time. But if you redeploy the project again the EM property modifications will reset.
• All the DB adapter configured property value will resides in the dbadapter_name.jca file.

ALso Check : Test a BPEL process initiated by DB Adapter in Wblogic EM

Please give your valuable feedback on this post.  Happy Coding :)