DB ADAPTER

Extended Use Case

In this article I will extend the use case from Part 1 by an Inbound Database Adapter, which should poll the database for changes.  The extended scenario is shown in the image below using the notation from the Integration Blueprint book. The elements shown in blue are the new ones added to the use case from part 1.
 
The Database Adapter will be configured to listen on the PERSON_CHG_T helper table for new records. This table is filled by a trigger on the PERSON_T table and will hold one row for every change to the PERSON_T table.
For each new row in PERSON_CHG_T I want an new OSB service to be called. This new service will use the data from the inbound request and enrich it by re-using the PersonService proxy service we have built in Part 1.

Prerequisites

The prerequisites for the 2nd part are obviously the same as in Part 1. The following software needs to be installed and available:

• JDeveloper 11g with SOA extension
• Eclipse 3.5.2 with Oracle Enterprise Pack for Eclipse (OEPE) 11.1.1.5.0
• Oracle Service Bus 11.1.1.3
• Oracle Database (XE is good enough)

Additionally you need the completed OSB project from Part 1. The solution can be downloaded from here.

Project Setup

The project setup has been done in Part 1. We will reuse the same Eclipse OSB project with the nested JDeveloper SOA Project and just continue where we have left in Part 1.

Create the Inbound Database Adapter

First let’s create a new Database Adapter.
For that we don’t need a new JDeveloper project, we can reuse the same project we created in Part 1, wrapped inside the adapter folder. I think it’s a good practice to keep all the adapters necessary for one OSB project in only one JDeveloper project.
Let’s go to JDeveloper an open the composite.xml to show the SCA composite view.

1. Drag a new Database Adapter into the SCA composite. Because it’s an Inbound Adapter, we will use the left hand swimmlane named “Exposed Services” for that. This is not strictly necessary when using the OSB but I think it’s a good mnemonic trick to do so (organizing inbound adapters on the left and outbound adapters on the right, as discussed in part 1).
   
2. Give the adapter service a good and meaningful name:
   
3. For the connection we reuse the settings already their from part 1, so we can move forward to the the Operation Type selection. This time we want to use the Database Adapter to “Poll for New or Changed Records in a Table”.
   
4. We want to poll the PERSON_CHG_T table, so let’s select it.
5. We can see that the table only holds an ID and a timestamp. So that’s all we get in the inbound message, whenever a row is inserted into PERSON_CHG_T. This is the reason why we later want to enrich the message with more information in a second step.
    
6. Next we need to define the strategy to use for signaling that a row has been read and successfully processed by the adapter. Because PERSON_CHG_T is a helper table no one else is using, it’s fine to just delete the row.
    
7. Next the Polling Options can be specified. Among others you can specify the polling frequency, which is set to 5 seconds by default, meaning that the Adapter will do the SQL operation shown on the right every 5 seconds. For our sample that’s fine, but in real world you should of course set it to a value matching your requirements.
   
8. Last but not least the Database Adapter allows for setting a selection criteria. We don’t use it this time, as we want to read all the rows which are added to the PERSON_CHG_T.

This finishes the creation of the Inbound Database Adapter and our work in JDeveloper. We can see the Adapter on the right hand swimmlane.
 
The adapter is now prepared to poll the PERSON_CHG_T table for new records every 5 seconds. Each row being read will be send to the service linked to the adapter. So let’s switch to the OSB project in Eclipse and create a new service to handle these messages.

Creating the OSB Service and linking it to the Inbound Database Adapter

When working with Inbound Adapters, an OSB proxy service needs to be used. The adapter will invoke the proxy service whenever a new message “is created” by the adapter.

1. In order to be able to create/generate the proxy service, we need the new adapter artifacts in Eclipse. Just do a refresh on the adapter folder and they will show up.
2. No we can choose Generate Service on the JCA configuration file (PollingPersonService_db.jca) to create the necessary OSB service.
   
3. Based on the JCA settings, OSB knows that it is an Inbound Adapter and will generate a JCA Proxy Service automatically. All we need to specify is the right folder:
4. The proxy settings, created for you, show that a WSDL is used which has been generated as well:
   
5. The transport setting show the usage of the JCA Transport:
   
6. All we need to do is specify what should happen with the message, by defining a meaningful Message Flow. For a start add a Pipeline Pair Node with a nested Stage Node and a Log action to show the message in the OSB log on the console. Make sure to specify a Severity level in the Log action which is shown in the log. If you are unsure what to choose, then “Error” will be fine for that sample and shown by default.
   
7. Now let’s deploy the OSB project and test if the Inbound Adapter works. For that let’s open SQL PLus, connect to SOA_SAMPLE and do an UPDATE on the PERSON_T table. By that the trigger on that table will fire and signal the change by adding a row to the PERSON_CHG_T table. Make sure to commit the change!
8. After a maximum of 5 seconds (remember the polling frequency specified in the Database Adapter wizard) the log should show up on the OSB console window.

We can see that the polling Database Adapter worked. A message has been sent to the OSB proxy service holding the ID of the changed PERSON_T row and a timestamp!
In a real world scenario you would now want to do something more meaningful with this information than just logging it to the console, i.e. you want to inform another system about the change. In order to do that, you might need to send more information than just the ID of the person. The system to inform maybe require the person information, similar to the information returned by the PersonService we developed in Part 1. So let’s reuse that proxy service to enrich our message, implementing the Content Enricher design pattern.

Adding the Content Enricher

To enrich our message, we want to call the PersonService proxy service from the Message Flow of the PollingPersonServiceDB proxy service.

1. Let’s add a Service Callout action and rename the stage to EnrichmentStage. It’s always a good idea to meaningfully name the different nodes used to structure the message flow. This helps you to better understand and document your message flow at development time but also helps in case of errors at runtime, to easier identify the place where the error occurred.
    
2. Configure the Service Callout action to call the PersonService proxy and to invoke the findPerson operation. For the request and response message we define two variables and specify to use a Soap Body. The Service Callout action allows to use separate variables for the request and response message. By that the content of the $body variable from the request to the proxy service stays untouched during the service callout. This is important if you want to merge the response from the service callout with the original request. This is not necessary in our simple example, all we will use is the response directly from the service callout. But usually you will need to merge the two when implementing the Content Enricher pattern in OSB.
    
3. Next we implement the Assign action to set the requestBody variable. 
    
4. Because we specified “Configure Soap Body” in the Service Callout properties, we need to setup the <findPersonRequest> message wrapped in a <soap-env:Body> element. The value of the <personId> element can be retrieved from the $body variable by dragging it into the Expression view and defining the XPath expression shown in the image below
    
   
5. Last but not least you need to add v1 as a custom namespace:
   
6. In the Response Action of the Service Callout we will also use an Assign action, this time to copy the value of the $responseBody variable to the $body variable.
    
7. Let’s change the Annotation of the Log action from before to state the fact that we now log the content of the $body variable after the service callout has been made.
   
8. Let’s test it in the same way as before. Just re-execute the UPDATE on PERSON_T and this time a longer log message with a complete Person instance should be shown.
   

The Content Enrichment worked an the complete and up-to-date person information could now be sent to any system interested.

Conclusion

This finishes the 2nd part of this blog article series.
We have added an Inbound Adapter to the use case to get informed whenever the information changes in the PERSON_T table. By re-using the PersonService from a Service Callout in the Message Flow we were able to enrich the incoming message to a more meaningful “change message”, which could now be used to inform potential external systems of changes happening on the PERSON_T table.
We have used the OSB to implement parts of a typical integration scenario. Similar to one of the scenarios documented in our Integration Blueprint book!
The implementation of a dynamic publish-subscribe mechanism on the OSB, in order to inform the systems interested could be a topic of a next blog article.
The source code for the solution can be downloaded from here. I will again provide a video showing how this extension of the use case has been developed.