How to call MySQL 5 stored procedure from Data Services ?

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. Since procedure is stored (in a precompiled format) within the database itself, execution is faster. Client programs can be restricted to accessing database only via stored procedures. Thus enforcing fine grained security, validation rules to data that goes in/comes out of your database.

All major Database engines support stored procedures. If your application uses complex stored procedures & you want to expose them to outside business systems, WSO2 Data Services is the answer for you.

Objective

In this tutorial I will show you how to call MySQL 5 Stored Procedure using WSO2 Data Services. But Data Services stored procedure support is not limited to MySQL 5. You can try it with any other relational database engine of you choice.

Prerequisites

1. Download latest version of WSO2WSAS from http://dist.wso2.org/products/wsas/java/

2. Install it as a standalone server (Install location will be referred to as WSAS_HOME hereafter.)

3. Start WSO2WSAS-<VERSION> (run WSAS_HOME/bin/wso2wsas.bat | wso2wsas.sh)

4. Open a web browser & navigate to https://localhost:9443/

5. Login to WSAS (using Username/password = admin/admin) & get yourself familiar.

6. Stop WSAS. (Don't worry. We will be starting back soon :-) )

Step 1 : Creating & Populating Sample Database

Use following sql to create & populate sample database, which we will be using for this tutorial.

DROP DATABASE IF EXISTS DATASERVICE_SAMPLE;
CREATE DATABASE DATASERVICE_SAMPLE;
GRANT ALL ON DATASERVICE_SAMPLE.* TO 'dsuser'@'localhost' IDENTIFIED BY 'user123';

USE DATASERVICE_SAMPLE;

DROP TABLE IF EXISTS Employees;

CREATE TABLE Employees(
        employeeNumber INTEGER,
        lastName VARCHAR(50),
        firstName VARCHAR(50),
        extension VARCHAR(10),
        email VARCHAR(100),
        officeCode VARCHAR(10),
        reportsTo INTEGER,
        jobTitle VARCHAR(50) 
);


insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'President');
insert into Employees values (1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales');
insert into Employees values (1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing');
insert into Employees values (1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com','6',1056,'Sales Manager (APAC)');
insert into Employees values (1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com','4',1056,'Sale Manager (EMEA)');
insert into Employees values (1143,'Bow','Anthony','x5428','abow@classicmodelcars.com','1',1056,'Sales Manager (NA)');
insert into Employees values (1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com','1',1143,'Sales Rep');

DROP PROCEDURE If EXISTS getEmployee;

Alternatively you can find a file called 'mysql.txt' containing all the sql we will be using. Once you login to mysql console, you can run this file (as root) as follows.

sumedha@sumedha:~/articles/data-service/store-procedure-1$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.24a-Debian_9ubuntu2-log

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

mysql> \. mysql.txt

Step 2 : Writing a simple stored procedure

Following sql will create a stored procedure called 'getEmployee' which accepts employee number as an integer.

CREATE PROCEDURE getEmployee(empNo INTEGER) SELECT employeeNumber,lastName,firstName,email FROM Employees where employeeNumber = empNo;

Step 3 : Updating WSAS libraries (Only if your using WSAS 2.0)

If your using WSAS 2.0,  you need to update Data Service library to enable Stored Procedure support.

In order to do this,

1.Download wso2data-service-2.3.jar_.zip from bottom of this document.

2.Extract it to a temporary location & you will find wso2data-service.2.3.jar

3.Place wso2data-service-2.3.jar inside WSAS_HOME/lib. Make sure you delete wso2data-service-2.0.jar located inside the same folder.

Step 4 : MySQL JDBC Driver

1. Download JDBC driver for MySQL from http://www.mysql.com/products/connector/.

2. Copy jar file containing driver class (i.e. mysql-connector-java-<VERSION>-bin.jar) into WSAS_HOME/lib

Step 5 : Creating a Data Service with stored procedure

Paste following content into a file called 'StoredProcedureSampleService.dbs'. (Alternatively you can use the attached file at the bottom of this document. Please change the '.txt' extension to '.dbs' before using.)

<data name="StoredProcedureSampleService">    
    <config>        
        <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>        
        <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/DATASERVICE_SAMPLE</property>        
        <property name="org.wso2.ws.dataservice.user">dsuser</property>        
        <property name="org.wso2.ws.dataservice.password">user123</property>    
    </config>

    <query id="getEmployeeSP">
        <sql>call getEmployee(?)</sql>
        <result element="Employees" rowName="Employee">
            <element name="EmployeeNumber" column="employeeNumber" />
            <element name="LastName" column="lastName" />
            <element name="FirstName" column="firstName" />
            <element name="Email" column="email" />
        </result>
        <param name="employeeNo" sqlType="INTEGER" />
    </query>

    <operation name="getEmployee">
        <call-query href="getEmployeeSP">
            <with-param name="employeeNumber" query-param="employeeNo" />
        </call-query>
    </operation>
</data>

Start WSA Server. Go to Service -> Upload Service Artifact (.aar,.jar,.zip,.dbs). Browse to location where you saved 'StoredProcedureSampleService.dbs' & upload it to WSAS. (Figure 01 & Figure 02)

Figure 01

Figure 02 

Newly created service ('StoredProcedureSampleService') will soon appear on Services screen as follows. (Figure 03)

Figure 03

Step 6 : Invoking Data Service (using SOAP HTTP bindings)

Since 'StoredProcedureSampleService' is similar to any other web service, you can invoke this as you would invoke anyother web service. I will show you how to invoke this using HTTP bindings.

1. Click on 'Services' link (from menu on you left side)

2. Click on 'StoredProcedureSampleService' link under 'Services' heading. (Figure 04)

Figure 04

3. You will be taken to a screen similar to following. Copy the http endpoint reference (i.e. http://127.0.0.1:9762/services/StoredProcedureSampleService in my case.) as highlighted in figure 05.

Figure 05

4. Open a new browser window & paste the copied URL. (http://127.0.0.1:9762/services/StoredProcedureSampleService)

5. Append '/getEmployee?employeeNo=1165' to the end of it.

6. Final URL should look like following.(Only IP part should be different)

http://127.0.0.1:9762/services/StoredProcedureSampleService/getEmployee?employeeNo=1165

7. Submit the URL (press enter) and you will get following result in your browser window. (Figure 06)

Figure 06

Step 7 : Playing around

1. Try replacing '1165' with any of the following.

1002, 1056, 1076, 1088, 1102, 1143, 1165

(These are the Employee numbers used in our insert statements. (Step 1)

2. You can try invoking the service using other clients. (Take a note of highlighted section).(Figure 07)

Figure 07

Step 8 : Java Client Program

Listed bellow is a simple java client program, that will invoke the service. Inorder  to compile & run this, simply add all the jar files in WSAS_HOME/lib to you classpath. (You can find source attached in a file called 'StoreProcedureClient.java_.txt'. Please remove '_.txt' part from file name before using it.)

import org.apache.axiom.om.OMAbstractFactory;
import org.apache.axiom.om.OMElement;
import org.apache.axiom.om.OMFactory;
import org.apache.axiom.om.OMNamespace;
import org.apache.axis2.AxisFault;
import org.apache.axis2.addressing.EndpointReference;
import org.apache.axis2.client.Options;
import org.apache.axis2.client.ServiceClient;
import org.apache.axis2.transport.http.HTTPConstants;


public class StoreProcedureClient {
    public static void main(String args[]){
    	EndpointReference targetEPR = new EndpointReference(
    			"http://127.0.0.1:9762/services/StoredProcedureSampleService");
        try {
            OMElement payload = getPayload();
            Options options = new Options();
            options.setTo(targetEPR);
            options.setProperty(HTTPConstants.CONNECTION_TIMEOUT, 10000);
            ServiceClient sender = new ServiceClient();
            
            sender.setOptions(options);
            System.out.println("Request : "+payload);
            OMElement result = sender.sendReceive(payload);
            System.out.println("Response : "+result);
        } catch (AxisFault axisFault) {
            axisFault.printStackTrace();
        }        
    }
    
    private static OMElement getPayload() {
        OMFactory fac = OMAbstractFactory.getOMFactory();
        OMNamespace omNs = fac.createOMNamespace(
                "http://ws.apache.org/axis2/xsd", "ns1");
        OMElement method = fac.createOMElement("getEmployee", omNs);
        OMElement employeeNo = fac.createOMElement("employeeNo", omNs);
        employeeNo.setText("1165");
        
        method.addChild(employeeNo);
        return method;
    }
}

Additional reading (if your still interested :-) )

1. (Getting started with Data Service with WSO2 WSAS 2.0) http://www.wso2.org/blog/sumedha/2573

2. (How to expose Excel 97-2002 spreadsheet as a Data Service using WSAS - 2.0) http://www.wso2.org/blog/sumedha/2581

3. (Getting started with Data Service Samples - Movie - might take sometime to load) http://www.wso2.org/blog/sumedha/2590

Comments

You are more than welcome to send us your comments/suggestions/queries. Please use the comments section bellow and let us know what you think.

References

1. Contents in 'StoredProcedureSampleService.dbs' complies to grammer defined by following specification. (http://wso2.org/wiki/display/wsf/Data+Services+and+Resources)