Page Comparison

Objective: Introduction to the dblookup mediator

Code Block

language	html/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/xsd" 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 ESB. When a message arrives at dblookup mediator, it opens a connection to the database and executes the SQL query. The SQL query use '?' character for attributes that will be filled at runtime. The parameters define how to calculate the value of those attributes at runtime. In this sample a dblookup mediator has been used to extract 'id' of the company from the company database using the symbol which 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,

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

ESB console shows

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

For the SUN stock quote,

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

ESB console shows

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

and for the MSFT stock quote,

Code Block

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

For any other symbols, ESB console shows

Code Block
INFO LogMediator text = Unrecognized Company ID

and the client gets a response which has following message.

Code Block
Unrecognized Company ID

Versions Compared

Old Version 1

New Version 2

Key