How to call MySQL stored procedures from WSO2 Data Services Server

 Stored procedures allows you to expose pre defined queries as an api allowing users to consume them in a much faster way.

Requirements.

MySQL Database server
WSO2 Data Service Server (DSS) 2.6.3
MySQL JDBC driver

Note: <DSS_HOME> is the root folder of your unpacked DSS server.

1.) Unpack WSO2 DSS and drop MySQL JDBC driver in <DSS_HOME>/repository/componets/lib folder.
2) Start the server using the start script <DSS_HOME>/bin/wso2server.sh  (In linux or wso2server.bat in windows)

Prepare Database

use following queries to generate a database with a stored procedure. [1]

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;
CREATE PROCEDURE getEmployee(empNo INTEGER) SELECT employeeNumber,lastName,firstName,email FROM Employees where employeeNumber = empNo;

 Create Data Service.

1) click on DataService -----> Create

2) Add new Data Source.

3) Click next and Add new Query.

Here ? denotes the parameter required for the SQL query.

4) Add input parameter mapping 

Since we need to have an input parameter for the Data Service lets define it.

5) Add output Mapping.

There are elements in the output result we need to map. There are four output Mappings we need to define and i following screenshot shows adding one of them.

After adding all of them, Here how it looks like.

6) Click save and go to next page to add new operation. Click on "Add query params as operation params" to add input parameters for operation.

7) Click save and then click finish to deploy the Data Service. After few seconds refresh the web services list to view newly added Data Service.

8) Click on "Try this Service" to test the service. And then test your StoredProcedure exposed as a data service through WSO2 Data Services Server.

(here i have used 1002 as the employeeNo)

References.

[1] http://wso2.org/blog/sumedha/2623

[2] http://charithaka.blogspot.com/2010/09/how-to-call-oracle-stored-procedures.html