Using DB Adapters – Part 1 – Inserting into Table

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 as AccessCardGeneration 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.

9 thoughts on “Using DB Adapters – Part 1 – Inserting into Table

  1. Ravi

    This is really useful. But could you please let me know If I want to insert the webservice data into more than one table how can I do that. Please let me know this ASAP.

    Thanks,

    Like

    1. Hi Ravi,

      While importing tables, if you want to insert into two-more tables, while importing tables, you can import multiple tables.also if you have a primary key-foreign key relationship between tables, you can created 1:1, 1:M relationship between tables on ‘ create relationship’ tab.

      Thanks,
      Durgadas

      Like

  2. Hi, Ravi,

    I’m trying to make a insert on table using Database Adapter, but table has many fields and when I try to do mappings between fields using service call, it has a limit and it’s impossible do mapping all fields? Do you know how to solver this problem ?

    Thanks,

    Ronaldo

    Like

  3. Thanks for the post. It looks like Oracle BPM 11.1.1.7 only limits you to one DB adapter per process.I verified this by creating a second DB adapter, but it does not show up on the service list.

    Like

    1. Interesting. Yes, you can have multiple adapters, but you can only use the first one created. Absolutely none of the other adapters are available under the Service Activity -> Implementation -> Service Call -> Service Chooser. Making multiple adapters completely unusable.

      Like

      1. I wouldn’t mind doing a screen share. Though this is at work. Here in Alaska (-1 PST), I am here between 7AM and 3PM. Or we can take this conversation offsite if you will.

        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 )

Connecting to %s