Introduction

Stored procedure is a powerful mechanism to interact with a relational database. It allows business logic to be embedded inside database as an API. Stored procedure is stored in a precompiled format within the database itself resulting in faster execution speed. Client programs can be restricted to accessing database only via stored procedures. Thus enforcing fine grained security, preservation of data integrity and improved productivity. All major Database engines support stored procedures. If your application uses stored procedures and you want to expose them to outside business systems, WSO2 Enterprise Service Bus is the answer that you were looking for.

Objective

This tutorial will look at how to call a MySQL 5 Stored Procedure using WSO2 Enterprise Service Bus(ESB) with the help of db mediators. WSO2 Enterprise Service Bus stored procedure support is not limited to MySQL 5 but it can be tried out with any other relational database engine of the ESB user's preference.

Prerequisites

Setting up mysql database server

1. Install mysql server. MySQL version used for this tutorial is MySQL-5.0.75.

2. Connect to mysql server.

heshan@heshan-laptop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 300
Server version: 5.0.75-0ubuntu10.3 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

3. Create a sample databasae.

DROP DATABASE IF EXISTS ESB_SP_SAMPLE;
CREATE DATABASE ESB_SP_SAMPLE;

4. Grant necessary permission level to the user.

GRANT ALL ON ESB_SP_SAMPLE.* TO 'user'@'localhost' IDENTIFIED BY 'user123';

5. Create a table using the following statement.

USE ESB_SP_SAMPLE;

DROP TABLE IF EXISTS company;
CREATE TABLE company(name VARCHAR(10), id VARCHAR(10), price DOUBLE, location VARCHAR(10));

6. Inserts some data to the company table using following statements

INSERT INTO company VALUES ('WSO2','c1',2.9563,'SL');
INSERT INTO company VALUES ('IBM','c2',3.7563,'US');
INSERT INTO company VALUES ('SUN','c3',3.8349,'US');
INSERT INTO company VALUES ('MSFT','c4',3.2938,'US');

7. Create necessary Stored Procedures.

DROP PROCEDURE If EXISTS getCompany;
CREATE PROCEDURE getCompany(compName VARCHAR(10)) SELECT name, id, price,location FROM company WHERE name = compName;

DROP PROCEDURE If EXISTS updateCompany;
CREATE PROCEDURE updateCompany(compPrice DOUBLE, compName VARCHAR(10)) UPDATE company SET price = compPrice WHERE name = compName;

8. Add mysql-connector-java-5.1.12-bin.jar to the classpath. This can be done by putting the jar into the WSO2 ESB's lib directory.

NOTE: The attached mysql.txt contains all the sql that is being used in this tutorial.

Setting up ESB server

1. Download latest version of WSO2 ESB from https://wso2.org/downloads/esb. This tutorial will be demonstrated upon the latest release of the WSO2 ESB. ie. WSO2 ESB-3.0.0.

2. Install it as a standalone server. Install location will be referred to as ESB_HOME from here onwards.

3. When using MySQL, mysql-connector-java.jar should be added to the ESB classpath(i.e. ESB_HOME/repository/components/lib/). For testing the samples attached in this tutorial; MySQL-5.0.75 and mysql-connector-java-5.1.12-bin.jar was used.

You can use any other database product instead of MySQL. Then you have to change the database connection details accordingly. Also you have to copy the required database driver jars to the ESB classpath.

4. Start WSO2ESB-<VERSION> using the startup scripts.

eg. wso2server.bat for windows environments
wso2server.sh for linux environments

5. Open a web browser and navigate to https://localhost:9443/

6. Login to ESB. The default username/password will be admin/admin. Go through the User Interface and get yourself familiarized with WSO2 ESB environment.

Following two subsections will demonstrate how Stored Procedures can be called with the use of ESB's db mediators. In the first sample, dblookup mediator is taken into consideration and in the next sample both dblookup and dbreport mediators are taken into consideration.

Calling Stored Procedures from dblookup mediator

The dblookup mediator is capable of executing an arbitrary SQL select statement, and then set some resulting values as local message properties on the message context. The DB connection used maybe looked up from an external DataSource or specified in-line, in which case an Apache DBCP connection pool is established and used. For configuration information about db mediators, refer dbreport mediator guide. Following is the configuration used to demonstrate this sample.

<definitions xmlns="http://ws.apache.org/ns/synapse">

    <sequence name="myFaultHandler">
        <makefault>
            <code value="tns:Receiver" xmlns:tns="https://www.w3.org/2003/05/soap-envelope"/>
            <reason expression="get-property('ERROR_MESSAGE')"/>
        </makefault>
        <send/>
        <drop/>
    </sequence>

    <sequence name="main" onError="myFaultHandler">
        <in>
            <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/ESB_SP_SAMPLE</url>
			<user>user</user>
			<password>user123</password>
                    </pool>
                </connection>
                <statement>
                    <sql>call getCompany(?)</sql>
                    <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                               xmlns:m0="https://services.samples" 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="https://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="https://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="https://localhost:9000/services/SimpleStockQuoteService"/>
                        </endpoint>
                    </send>
                </case>
		<case regex="c4">
                    <log level="custom">
                        <property name="text"
                                  expression="fn:concat('Company ID - ',get-property('company_id'))"/>
                    </log>
                    <send>
                        <endpoint>
                            <address uri="https://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="https://www.w3.org/2003/05/soap-envelope"/>
                        <reason value="** Unrecognized Company ID **"/>
                    </makefault>
                    <send/>
                    <drop/>
                </default>
            </switch>
            <drop/>
        </in>

        <out>
            <send/>
        </out>

    </sequence>

</definitions>

Running the sample.

1. Start the WSO2 ESB server with the attached synapse configuration named dblookup_synapse_config.xml.

2. Start the Axis2 server and deploy the SimpleStockQuoteService if not already done.

3. Run the client.

ant stockquote -Daddurl=https://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=https://localhost:8280/ -Dsymbol=WSO2

Calling Stored Procedures from dbreport mediator

The dbreport mediator is very similar to the dblookup mediator, but writes information to a Database, using the specified insert SQL statement. For configuration information about dblookup mediators , refer dblookup mediator guide. Following is the configuration used to demonstrate this sample.

<definitions xmlns="http://ws.apache.org/ns/synapse">

    <sequence name="main">
        <in>
            <send>
                <endpoint>
                    <address uri="https://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/ESB_SP_SAMPLE</url>
			<user>user</user>
			<password>user123</password>
                    </pool>
                </connection>
                <statement>
                    <sql>call updateCompany(?,?)</sql>
                    <parameter expression="//m0:return/m1:last/child::text()"
                               xmlns:m0="https://services.samples" xmlns:m1="https://services.samples/xsd" type="DOUBLE"/>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
                               xmlns:m0="https://services.samples" xmlns:m1="https://services.samples/xsd" 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/ESB_SP_SAMPLE</url>
			<user>user</user>
			<password>user123</password>
                    </pool>
                </connection>
                <statement>
                    <sql>call getCompany(?)</sql>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
                               xmlns:m0="https://services.samples" xmlns:m1="https://services.samples/xsd" 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>

Running the sample.

1. Start the WSO2 ESB server with the attached synapse configuration named dbreport_synapse_config.xml.

2. Start the Axis2 server and deploy the SimpleStockQuoteService if not already done.

3. Run the client.

ant stockquote -Daddurl=https://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=https://localhost:8280/ -Dsymbol=WSO2

Conclusion

WSO2 Enterprise Service Bus is capable of calling database Stored Procedures via the db mediators. If your application uses stored procedures and you want to expose them to outside business systems, WSO2 ESB is the answer that you were looking for.