Sample 364: Using Mediators to Execute Database Stored Procedures

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/.

Introduction

This sample demonstrates how you can use the DBLookup Mediator and DBReport Mediator to execute database stored procedures. Here the dblookup mediator and dbreport mediator are used to access the database with statements that call a stored procedure in MySQL.

Prerequisites

MySQL database installed and configured. For instructions on setting up the MySQL database, see Setting up the MySQL database.
For a list of general prerequisites, see Prerequisites to Start the ESB Samples

Building the sample

The XML configuration for this sample is as follows:

<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>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>

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

To build the sample

Start the ESB with the sample 364 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."
Start the Axis2 server. For instructions on starting the Axis2 server, see Starting the Axis2 server.
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 the following command from the <ESB_HOME>/samples/axis2Client directory.

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

Analyzing the output

When the client is run, you will see that the ESB invokes the two stored procedures and that the response is mediated back to the client.

If you analyze the debug log output on the ESB console, you will see the following output:

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