Sample 364: Executing Database Stored Procedures

Objective: Demonstrate the use of dblookup and dbreport mediators to execute a database Stored Procedures

<definitions xmlns="http://ws.apache.org/ns/synapse"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://ws.apache.org/ns/synapse http://synapse.apache.org/ns/2010/04/configuration/synapse_config.xsd">

    <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>com.mysql.jdbc.Driver</driver>
                        <url>jdbc:mysql://localhost:3306/synapsedb</url>
                        <user>user</user>
                        <password>password</password>
                    </pool>
                </connection>
                <statement>
                    <sql>call updateCompany(?,?)</sql>
                    <parameter xmlns:m0="http://services.samples"
                               xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:last/child::text()" type="DOUBLE"/>
                    <parameter xmlns:m0="http://services.samples"
                               xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
                </statement>
            </dbreport>
            <log level="custom">
                <property name="text" value="** Looking up from the Database **"/>
            </log>
            <dblookup>
                <connection>
                    <pool>
                        <driver>com.mysql.jdbc.Driver</driver>
                        <url>jdbc:mysql://localhost:3306/synapsedb</url>
                        <user>user</user>
                        <password>password</password>
                    </pool>
                </connection>
                <statement>
                    <sql>call getCompany(?)</sql>
                    <parameter xmlns:m0="http://services.samples"
                               xmlns:m1="http://services.samples/xsd"
                               expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
                    <result name="stock_prize" column="price"/>
                </statement>
            </dblookup>
            <log level="custom">
                <property name="text"
                          expression="fn:concat('Stock Prize - ',get-property('stock_prize'))"/>
            </log>
            <send/>
        </out>
    </sequence>

</definitions>

Prerequisites: Set up MySQL database server according to the sample setup guide.

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

Runs the client as follows

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

Then you will get the following console output

INFO LogMediator text = ** Looking up from the Database ** ...
INFO LogMediator text = Company ID - c1 ...
INFO LogMediator text = Stock price - 183.3635460215262