Sample 361: Introduction to dbreport Mediator

Objective: Introduction to the dbreport mediator

<definitions xmlns="http://ws.apache.org/ns/synapse">
    <sequence name="main">
        <in>
            <send>
                <endpoint>
                    <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                </endpoint>
            </send>
        </in>
        <out>
            <log level="custom">
                <property name="text" value="** Reporting to the Database **"/>
            </log>
            <dbreport>
                <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>update company set price=? where name =?</sql>
                    <parameter xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               expression="//m0:return/m1:last/child::text()" type="DOUBLE"/>
                    <parameter xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
                </statement>
            </dbreport>
            <send/>
        </out>
    </sequence>
</definitions>

Prerequisites:

Start the Synapse configuration numbered 361: i.e. wso2esb-samples -sn 361
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

This sample demonstrate simple database write operations. The dbreport mediator writes (i.e. inserts one row) to a table using the message details. It works the same as the dblookup mediator. In this sample , dbreport mediator is used for updating the stock price of the company using the last quote value which is calculated by evaluating an XPath against the response message. After running this sample, user can check the company table using the Derby client tool. It will show the inserted value by the dbreport mediator.

Run the client using,

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

and then execute the following query using database client tool against synapsedb.

select price from company where name='IBM';

It will show some value as follows.

96.39535981018865