WSO2 ESB with Oracle Database

You might have already searched on google 'How to insert data into a database using an ESB, How to read data from a database using an ESB, How to connect Oracle Database and WSO2 ESB or Is it possible to connect Oracle database to WSO2 ESB' and similar subjects. If you have done that, and if you didn't find any other useful resource so that you could easily copy, paste and try that; this article will definitely be useful for you.

We have discussed about Creating a Mock Web Service using the WSO2 ESB, extracting values from a SOAP message using XPath expressions, and then how to create a new Message Payload using those values. With the same technique, we can extract values from any incoming SOAP message and do so many things using a Proxy Service.

In this article we will see how to extract values from a SOAP message, and how to insert the those values into an Oracle Database Table.

For this, we will be using two Mediators called DBReport and DBlookup, those are provided by the WSO2 ESB, in order to perform databases related operations.

• Let's consider a situation, where you have to extract values from a SOAP message like

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Header/>
   <soapenv:Body>
      <m:city xmlns:m="http://demo.wso2.com">
         <m:id>0001</m:id>
         <m:city_name>Katunayake</m:city_name>
         <m:postal_code>11420</m:postal_code>
      </m:city>
   </soapenv:Body>
</soapenv:Envelope>

• And insert the extracted values into a Database Table like

id	postal_code	city_name
0001	11420	Katunayake
0002	...	...

In this case I have used the OracleXE database, and both user name and password for the oracle database is 'wso2'. Therefore, if you are copying code segments from this blog and directly pasting those into your code editors―the Database connection URL, username and password will needed to be modified accordingly.

• Download the Oracle JDBC connector driver ojdbc6.jar and place it inside
  [ESB-HOME]/repository/components/lib direcory
• Create a table called postal_codes, executing the following SQL statement on the Oracle Database

CREATE TABLE postal_codes(
    id          VARCHAR(10) PRIMARY KEY NOT NULL,
    postal_code VARCHAR(8),
    city_name   VARCHAR(50)
);

• Create a proxy service in the WSO2 ESB, and add a DBReport mediator (WRITE). (Change the Database connection URL, Username and Password accordingly)

The relevant proxy service XML code :

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="SampleProxy" transports="https,http" statistics="disable" trace="disable" startOnLoad="true">
    <target>
        <inSequence>
            <dbreport>
                <connection>
                    <pool>
                        <password>wso2</password>
                        <user>wso2</user>
                        <url>jdbc:oracle:thin:@192.168.56.103:1521/XE</url>
                        <driver>oracle.jdbc.driver.OracleDriver</driver>
                    </pool>
                </connection>
                <statement>
                    <sql>INSERT INTO postal_codes (id, postal_code,city_name) VALUES (?,?,?)</sql>
                    <parameter xmlns:m="http://demo.wso2.com" expression="//m:city/m:id" type="VARCHAR" />
                    <parameter xmlns:m="http://demo.wso2.com" expression="//m:city/m:postal_code" type="VARCHAR" />
                    <parameter xmlns:m="http://demo.wso2.com" expression="//m:city/m:city_name" type="VARCHAR" />
                </statement>
            </dbreport>
        </inSequence>
    </target>
    <description />
</proxy>

• Note that the XPath expressions have been used to extract values from the incoming message (into parameters); then those extracted values have respectively been passed into the SQL statement as parameters
• Using SOAP-UI, send the following Message Payload in to the SampleProxy, that you just created.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Header/>
   <soapenv:Body>
      <m:city xmlns:m="http://demo.wso2.com">
         <m:id>0001</m:id>
         <m:city_name>Katunayake</m:city_name>
         <m:postal_code>11420</m:postal_code>
      </m:city>
   </soapenv:Body>
</soapenv:Envelope>

• Then go to the Oracle Database and observe that the information have been saved properly
• To try the DBLookup mediator (READ), the following code segment can be added to the proxy code (Copy the following and paste below the DBReport mediator code)

<dblookup>
    <connection>
        <pool>
            <password>wso2</password>
            <user>wso2</user>
            <url>jdbc:oracle:thin:@192.168.56.103:1521/XE</url>
            <driver>oracle.jdbc.driver.OracleDriver</driver>
        </pool>
    </connection>
    <statement>
        <sql>select * from postal_codes where id =?</sql>
        <parameter xmlns:m="http://demo.wso2.com" expression="//m:city/m:id" type="VARCHAR" />
        <result name="rs_id" column="id" />
        <result name="rs_postal_code" column="postal_code" />
        <result name="rs_city_name" column="city_name" />
    </statement>
</dblookup>
<log level="custom">
    <property name="POSTAL CODE" expression="$ctx:rs_postal_code" />
    <property name="CITY NAME" expression="$ctx:rs_city_name" />
</log>

• This will select values from the Database Table, and store those values within the context as rs_id, rs_postal_code and rs_city_name
• Because of the log mediator that we have added right below the DBLookup mediator, it will extract result values ( rs_id, rs_postal_code and rs_city_name ) from the context [ $ctx: ] and print on the WSO2 ESB server console as,

[2014-03-20 19:17:30,608]  INFO - LogMediator POSTAL CODE = 11420, CITY NAME = Katunayake

If you encounter any timezone issue with Oracle JDBC driver, export the below mentioned JVM parameters on the terminal window before starting the WSO2 ESB (just copy paste and enter).

export JAVA_OPTS="$JAVA_OPTS -Duser.timezone=+05:30"

However, depending on the requirements and the level of utilization of database related operations, WSO2 Data Services Server can be used in order to handle these kind of operations with more flexibility and in an efficient manner.
Reference:
Develop a Data Service
Configure Oracle Datasource
Expose Data Service as a REST resource