Sample 361: Introduction to DBReport Mediator

Note that WSO2 EI is shipped with the following changes to what is mentioned in this documentation:

<PRODUCT_HOME>/repository/samples/ directory that includes all Integration profile samples is changed to <EI_HOME>/samples/service-bus/.
<PRODUCT_HOME>/repository/samples/resources/ directory that includes all artifacts related to the Integration profile samples is changed to <EI_HOME>/samples/service-bus/resources/.

Objective: Demonstrate simple database write operations with the /wiki/spaces/EI6xx/pages/49612763.

<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 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>update company set price=? where name =?</sql>
                    <parameter expression="//m0:return/m1:last/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLE"/>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
                               xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
                </statement>
            </dbreport>
            <send/>
        </out>
    </sequence>

</definitions>

Prerequisites:

Create the above configuration and deploy it in the WSO2 EI profile, see Working with WSO2 Integration Studio.
Start the Axis2 server and deploy the SimpleStockQuoteService if not already done

The dbreport mediator writes (inserts one row) to a table using the message details. It works the same as the /wiki/spaces/EI6xx/pages/49612755. In this sample, the 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 value inserted by the DBReport mediator.

Run the client using the following command:

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

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

select price from company where name='IBM';

It will show some value as follows.

96.39535981018865