Using DB Adapters – Part 2 – Selecting-from & Deleting-Into Table

Continuing from 1st Post, now we will Select access-card-number from Table ‘globalFreeAccessCards’ and then delete it from the same.
Refer Service Task ‘ Get Access Card Number from Database’ and ‘Update Global – Free – Access Table in Database’

Now the basic idea is to select a access card number from Database Table ‘globalFreeAccessCards’ and then return it as an access card number which can be assigned to Employee. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.
So as per our requirement , we need to execute below DB operations @ Database.

  • Select a Free Access Card Number from globalFreeAccessCards Table
  • Delete selected Access-card-number from globalFreeAccessCards table.

We will now create one DB adapter, which we will have two operations .
Steps:

a. Open Composite.xml

b Drag and Drop Database Adapter on External-references Section

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

As of now, we need to select & Delete a row into Employee Table, so will only select insert operation.

Click on Import- Tables and select Employee Table.

Get the MAX accessCardNumber number from Table.

Now we need to go back again to BPMN process and define implementation for Service Task ‘Get Access Card Number from Database’.

To get on Payload-DataObject – Max-Access-Card-Number Value from DB, we will go to transformation section and map Input through XSL.

Below is the XSL mapping which will pass on Access-Card-Number data into Employee Dataobject.

Deletion from Table
We will use same DB Adapter again to delete from table.
Service Task: ‘Update Global – Free – Access Table in Database’

XSL

Sample Code:

Using DB Adapters – Part 1 – Inserting into Table

August 1, 20129 comments

This post will help you while creating DB adapters in a SOA composite.

Let’s take a scenario where we are creating an application for HR System with Sub-Module asAccessCardGeneration for a new Joiner.

Start node will be an initiator node which we will be exposed as Webservice. Now once we receive a Message from an external system [ assume a Dot net application which HRs use for creating employee Id], instance would be created.

Below is data association which we will put into our Start node. To pass of Data from Webservice Request to BPM’s Data Object.

Now the basic idea is to insert input received from Webservice into a DB Table and then return an access card number from Global-Free-Access-Card table. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.

DB Scripts to create Basic Tables:

create table employee
(fullName varchar(15),
Email varchar(20),
EmployeeId number(10),
ContactNumber varchar(12),
DateOfJoining date
);

create table globalFreeAccessCards
(AccessCardNumber number(10)
);

create table globalAssignedAccessCards
(AccessCardNumber number(10),
EmployeeId number(10)
);

So as per our requirement , we need to execute three DB operations with Database.

· Insert Webservice Data into employee Table

· Select a Free Access Card Number from globalFreeAccessCards Table and then Delete selected Access-card-number from table.

· Update Selected Access Card Number into globalAssignedAccessCards Table with Assigned Employee-Id.

We will not create three DB adapters, once each for above mentioned respective table.
Steps:
à Open Composite.xml
à Drag and Drop Database Adapter on External-references Section

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

àAs of now, we need to insert a row into Employee Table, so will only select insert operation.

à Click on Import- Tables and select Employee Table.

àSet EmployeeId as Primary Key

Now we need to go back again to BPMN process and define implementation for Service Task ‘Insert Employee Record Into Database’.
To pass on Payload-DataObject – Employee’s Value into DB, we will go to transformation section and map Input through XSL.

–> Below is the XSL mapping which will pass on Payload data into Employee Table.

In my next Post, I will show, how to execute Select and Update through DBadapters and process deployment with Sample Code.

2 thoughts on “Using DB Adapters – Part 2 – Selecting-from & Deleting-Into Table

  1. mukatiraj

    Very well step by step explained. Can you also provide details about UMS adapter implementation? That would be great help. Thanks

    Like

    1. admin

      Hi Rajesh,

      Sure , we will do that.
      Also you can request any blog using our request-a-blog section.

      Regards,
      DnAFizz Team

      Like

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s