How to call MySQL 5 Stored Function from Data Service?

Step 1: Creating & Populating sample database

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

CREATE TABLE Salary(
        employeeNumber INTEGER,
        salary DOUBLE,
        lastRevisedDate DATE
);

INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1002,13000,'2007/11/30');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1056,30000,'2007/01/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1076,17500,'2008/01/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1088,7000,'2007/05/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1102,25000,'2006/12/01');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1143,40500,'2006/03/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1165,12000,'2007/02/01');


DELIMITER $$

DROP FUNCTION IF EXISTS AVERAGE_SALARY $$
CREATE FUNCTION AVERAGE_SALARY () RETURNS DOUBLE
BEGIN
    DECLARE totalSalary,average DOUBLE;
    DECLARE noOfEmployees INT;
    SELECT sum(salary) into totalSalary from Salary;
    SELECT COUNT(employeeNumber) into noOfEmployees from Salary;
    SET average = totalSalary/noOfEmployees;
    RETURN average;
END $$

DELIMITER ;

Step 2 : Creating a Data Service with Stored Function

Paste following content into a file called 'MySQLFunctionSample.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="MySQLFunctionSample">
   <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>
      <property name="org.wso2.ws.dataservice.minpoolsize">1</property>
      <property name="org.wso2.ws.dataservice.maxpoolsize">5</property>
   </config>

   <query id="averageSalary">
      <sql>SELECT AVERAGE_SALARY() as AverageSalary</sql>

      <result element="Salary" rowName="Salary">
         <element name="AverageSalary" column="AverageSalary" />
      </result>
   </query>

   <operation name="getAverageSalary">
      <call-query href="averageSalary" />
   </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.