This documentation is for WSO2 Enterprise Service Bus version 5.0.0. For the latest ESB, view the latest WSO2 Enterprise Integrator documentation.

All docs This doc

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Objective: Demonstrate

Table of Contents
maxLevel3

Introduction

This sample demonstrates how to perform a simple database read operations using the DBLookup mediator.operation database using the DBLookup mediator.

In this sample, when a message arrives at the DBLookup mediator, it opens a connection to the database and executes the SQL query. The SQL query uses the ? character for attributes that are specified at runtime, and the parameters define how to calculate the value of those attributes.Here, the DBLookup mediator is used to extract the id of the company from the company database using the symbol that is evaluated using an XPath against the SOAP envelope, and the id base switching is done by a Switch mediator.

Prerequisites

Building the sample

The XML configuration for this sample is as follows: 

Code Block
languagehtml/xml
<definitions xmlns="http://ws.apache.org/ns/synapse">

    <sequence name="myFaultHandler">
        <makefault>
            <code value="tns:Receiver" xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
            <reason expression="get-property('ERROR_MESSAGE')"/>
        </makefault>

        <property name="RESPONSE" value="true"/>
        <header name="To" expression="get-property('ReplyTo')"/>
        <send/>
        <drop/>
    </sequence>

    <sequence name="main" onError="myFaultHandler">
        <in>
            <log level="custom">
                <property name="text"
                          value="** Looking up from the Database **"/>
            </log>
            <dblookup xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <driver>org.apache.derby.jdbc.ClientDriver</driver>
                        <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
                        <user>esb</user>
                        <password>esb</password>
                    </pool>
                </connection>
                <statement>
                    <sql>select * from company where name =?</sql>
                    <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                               xmlns:m0="http://services.samples" type="VARCHAR"/>
                    <result name="company_id" column="id"/>
                </statement>
            </dblookup>

            <switch source="get-property('company_id')">
                <case regex="c1">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <case regex="c2">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <case regex="c3">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
                <default>
                    <log level="custom">
                        <property name="text" value="** Unrecognized Company ID **"/>
                    </log>
                    <makefault>
                        <code value="tns:Receiver"
                              xmlns:tns="http://www.w3.org/2003/05/soap-envelope"/>
                        <reason value="** Unrecognized Company ID **"/>
                    </makefault>
                    <property name="RESPONSE" value="true"/>
                    <header name="To" action="remove"/>
                    <send/>
                    <drop/>
                </default>
            </switch>
            <drop/>
        </in>

        <out>
            <send/>
        </out>

    </sequence>

</definitions>

Prerequisites:

  • Setting up Derby database as explained above.
  • Start the Synapse configuration numbered 360: i.e. wso2esb-samples -sn 360
  • Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

This sample demonstrates simple database read operations through the ESB. When a message arrives at the DBLookup mediator, it opens a connection to the database and executes the SQL query. The SQL query use the '?' character for attributes that will be filled at runtime, and the parameters define how to calculate the value of those attributes. In this sample a DBLookup mediator has been used to extract the 'id' of the company from the company database using the symbol that is evaluated using an XPath against the SOAP envelope. Then 'id' base switching will be done by a Switch mediator.

When the IBM stock quote is requested as follows:

...

This configuration file  synapse_sample_360.xml is available in the <ESB_HOME>/repository/samples directory.

To build the sample

  1. Start the ESB with the sample 360 configuration. For instructions on starting a sample ESB configuration, see Starting the ESB with a sample configuration.

    The operation log keeps running until the server starts, which usually takes several seconds. Wait until the server has fully booted up and displays a message similar to "WSO2 Carbon started in n seconds."

  2. Start the Axis2 server. For instructions on starting the Axis2 server, see Starting the Axis2 server.

  3. Deploy the back-end service SimpleStockQuoteService. For instructions on deploying sample back-end services, see Deploying sample back-end services.

Executing the sample

The sample client used here is the Stock Quote Client, which can operate in several modes. For further details on this sample client and its operation modes, see Stock Quote Client.

To execute the sample client

  • Run each of the following commands from the <ESB_HOME>/samples/axis2Client directory.

    First, run the following command, specifying IBM as the stock symbol:

    Code Block
    languagebash
    ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

The ESB console shows:

Code Block
INFO LogMediator text = ** Looking up from the Database **INFO LogMediator text = Company ID ? c1

For the SUN stock quote:

...

  • Then, run the following command, specifying SUN as the stock symbol.

    Code Block
    languagebash
    ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=SUN

    Next, run the following command, specifying MSFT as the stock symbol.

    Code Block
    languagebash
    ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=

...

  • MSFT

Analyzing the output

When you run the command that requests the IBM stock quote, you will see the following output on the ESB console:

Code Block
INFO LogMediator text = ** Looking up from the Database **INFO LogMediator text = Company ID ? c2

...

c1

When you run the command that requests the SUN stock quote, you will see the following output on the ESB console:

Code Block
antINFO LogMediator stockquotetext -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=MSFT
= ** Looking up from the Database **INFO LogMediator text = Company ID ? c2

When you run the command that requests the MSFT stock quote, you will see the following output on the ESB console:

Code Block
INFO LogMediator text = ** Looking up from the Database **
INFO LogMediator text = Company ID ? c2

For If you run a command for any other symbolssymbol, you will see that the ESB console showsdisplays the following:

Code Block
INFO LogMediator text = ** Unrecognized Company ID **

and You will also observe that the client gets a response with the following message.

...