Mule ESB with the Oracle Database and IBM WebSphere MQ – Use case 1 of 3

motif

I made a shift to MuleSoft! After spending most of my career in Big Red and Big Blue, I decided to jump from the walls of the big commercial enterprise technology vendors to the fast moving world of open-source technologies, SaaS and the Cloud. I’ve worked with several of the traditional on-premise integration tools from and and now I’ll be working with MuleSoft’s latest and greatest integration platform that brings integration to the cloud.

As my first exercise, I decided to try a simple use-case of using the Mule ESB with the core technologies at Oracle and IBM – the Oracle Database and IBM WebSphere MQ. You’ll see how easy it is to use Mule to service-enable legacy on-premise technologies like relational databases and message queues.

I will demonstrate how I was able to easily accomplish this using this 3 part blog. In the first part, I’ll start by creating a simple message flow to expose an HTTP REST service that retrieves a specific employee record from an Oracle HR database and returns it in JSON format. In the second part, let’s take a look at how to easily turn this into a SOAP XML service without any coding. For the third part, let’s publish the message to WebSphere MQ at the end of the flow for each service request. (Note: Setup steps are at the end of each part for the necessary software.)

Part 1: Service enabling the Oracle HR database with HTTP and JSON

In Mule Studio, create a new Mule Project called HRDataService. Optionally, add a description.

Drag an HTTP endpoint from the palette on the right to the empty message flow diagram. (Tip: Use the Filter to search for HTTP).

Double-click the HTTP endpoint and specific the host name of localhost, port of 8081 and path of hrdataservice.

To be able to access the Oracle database, we need to add the Oracle JDBC client jar file to our build path. Right-click your project, select Build Path and choose Add External Archives. Choose the ojdb6.jar which you can find from the Oracle XE installation directory under /product/11.2.90/xe/jdbc/lib. You should see the jar file show up in your Referenced Libraries.

Back in your Message Flow, click the Global Elements tab and create an Oracle Data Source as shown. Enter the database connectivity details to the HR schema (hr/hr).

In the Global Elements tab, create a Database Connector for Oracle as shown. Call it Oracle_Database and associate the Data Source to the Oracle_Data_Source you previously created. Click Test Connection to verify your configuration.

Click on the Message Flow tab and add a Database endpoint to your flow.

Name it Get Employee Data and set the Exchange Pattern to request-response.

Click the + sign on the SQL Statement Key to create a Query Key named Employee with the following: select * from EMPLOYEES where EMPLOYEE_ID = #[message.inboundProperties[’empid’]].

On the References tab, associate the connector with the Oracle_Database connector you previously created.

Your message flow should now look like this:

Add an Object to JSON transformer to the end of the flow and in the Advanced tab, select the MIME type of text/plain, to make the JSON output easily viewable from a web browser.

From the Package Explorer, right-click the HRDataService.mflow and select Run As > Mule Application. You should see the embedded Mule ESB server boot up in the Console Window. Wait for the message Started app: hrdataservice.

From a Web Browser, access the URL: http://localhost:8081/hrdataservice?empid=100 to see the JSON output for Employee # 100.. Optionally, try a different employee ID greater than 100.

Summary

In a few simple steps, we are able to service-enable the contents of an Employee HR database in Oracle by using Mule to expose an HTTP REST service that returns data in JSON format. JSON is a standard format that is very popular among web and mobile applications. In the next part of this blog, we will see how to turn this into a SOAP web service, which is a standard in use in a lot of internal SOA and on-premise integration projects. For more on Mule, check out: http://www.mulesoft.com/.

 

Setup Steps

Mule Studio 3.4

Oracle XE 11G R2

  • Download and Install Oracle XE 11G R2: http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
  • Validate the installation by using the sqlplus command line utility to connect to the database using the system account.
    • sqlplus system/<password>
  • Unlock the HR schema by issuing these commands:
    • alter user hr account unlock;
    • alter user hr identified by hr;
  • View the Employee table that we will use in this example using these commands:
    • connect hr/hr
    • desc employees


We'd love to hear your opinion on this post

6 Responses to “Mule ESB with the Oracle Database and IBM WebSphere MQ – Use case 1 of 3”

  1. Wonderful article. Keep us posted.

    Agree(0)Disagree(0)Comment
  2. Remove dot (.) end of SQL to avoid errors.

    Agree(0)Disagree(0)Comment
  3. Tried simulating the same but connecting first to mysql and then to ms sql. I keep getting fatal error from studio when I test the connection even thought the jdbc drivers are loaded as external libraries. The same drivers work from normal java apps but not from mule studio. The error I get is “Fatal Test Connection Failure :(“

    Agree(0)Disagree(0)Comment
    • Balajee, there is an issue with the Connection Test with the version you are using. (Should still work fine when you run the flow). Try the latest Mule Studio release (Oct 2013), this is no longer an issue.

      Agree(0)Disagree(0)Comment
  4. Hi Tyrone,

    My Company is specialized on the Oracle Stack including Fusion Middleware. I have been working with OSB for 3 years now. I also have used one of the early versions of Mule for a previous company.

    Here is my problem I am trying to solve via Mule 3.4. I am querying an Oracle Table (aggregate query) and I want to get the output as an xml payload which I can transform with xslt and send an email to my Sys Admins. What I am finding is the output of the query if I use an object to xml transformer the output is a CaseInsensitive HashMap. What I want is an xml with the column name and rowname. Looks like its not possible to do. I am trying to prove our higher ups that Mule is an easy to use alternative to Oracle Service Bus. How can I do that Below is my query

    select distinct tran_date as tran_date, count(tran_id) as kount, round(avg(diff)) as response_time from (select distinct transaction_id as tran_id, max(timestamp) as a, min(timestamp) as b, to_char(max(timestamp), ‘mm/dd/yy’) as tran_date, (sysdate +((max(timestamp) – min(timestamp))* 24 * 60 *60 *1000) – sysdate) as diff FROM mdbt_mds.aarp_common_logs WHERE target_system != ‘LEGACY AAI’ and (timestamp >= TO_TIMESTAMP(SYSDATE -1,’DD-MM-RRRR HH24:MI:SS’) and timestamp < TO_TIMESTAMP(SYSDATE,'DD-MM-RRRR HH24:MI:SS')) group by transaction_id) group by tran_date order by tran_date

    The Object to xml transformer spits out the below xml which is baffling

    0.75
    16
    3
    TRAN_DATE
    10/25/13
    RESPONSE_TIME
    585
    KOUNT
    254375

    My ideal output xml should look like below

    10/27/2013
    25375
    572

    So I can use an xslt to an html page and use an SMTP connectior.

    Could you help with this?

    Regards,
    Hari

    Agree(0)Disagree(0)Comment
    • Hari,
      You should use Data Mapper to transform this to XML. Please see the continuation (part 2) of this blog:
      /mule-esb-with-the-oracle-database-and-ibm-websphere-mq-%E2%80%93-use-case-2-of-3/
      There’s an example there how to use Data Mapper to transform the database output into SOAP. Instead of POJO as the output, choose XML and you can either use a sample XML or an XSD to construct your output format.

      Agree(0)Disagree(0)Comment