How to use MySQL 5 INOUT variables in Data Services?

Introduction

MySQL 5 stored procedures support INOUT type variables. This type of variable carries a value into the procedure & bring back a value after execution is complete. Data services support exposing stored procedures which use INOUT type parameters.

This tutorial explains how to use INOUT type parameters in your Data Service.

Step 1 : Sample Database

Let's begin by creating a simple database to work with. Our database will have a single table and we will populate it with few records too. Here is the SQL (DDL) to perform said tasks.

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

USE DATASERVICE_SAMPLE1;

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');

Step 2 : Creating procedure

Now we have a database with a table called Salary. The management has decided to update salary of employees who have been within the company before a certain date !!!!. Let us create a procedure to perform this task.

Our procedure ( updateSalary ),

Accepts two parameters.

newSalary (DOUBLE) - IN type parameter specifying new salary value to be set.
revisedOn (DATE) - INOUT type parameter specifying the date to be considered when searching for employees. Once salary has been updated this parameter will contain the current date, on which the update happened. Thus making it a IN <-> OUT parameter.

Here comes the SQL to create 'updateSalary' procedure.

DELIMITER $$
DROP PROCEDURE IF EXISTS `dataservice_sample1`.`updateSalary` $$
CREATE DEFINER=`dsuser`@`localhost` PROCEDURE `updateSalary`(newSalary DOUBLE,INOUT revisedOn DATE)
BEGIN
update salary set salary = newSalary where lastRevisedDate < revisedOn;
set revisedOn = CURRENT_DATE();
END $$

DELIMITER ;

Step 3 : Trying out the procedure in command line

Before creating a data service, let's try out our procedure in command line. Under this section we will connect to our database using mysql command line client & execute the above procedure.

C:\>mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 789
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

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

mysql>

Now we are connected to mysql server. Let's switch to our database and execute our procedure.

mysql> use dataservice_sample1;
Database changed

mysql> set @D='2007-10-01';

Query OK, 0 rows affected (0.00 sec)


mysql> call updateSalary(9900,@D);

Query OK, 5 rows affected (0.29 sec)

We create a session variable called 'D' and assign a date value to it. Then we pass this variable along with new salary value. Once procedure execution is complete, we can observe the new value of 'D' as follows. You might notice that it contains the current date.

mysql> select @D;

+------------+

| @D         |

+------------+

| 2008-01-19 |

+------------+

1 row in set (0.00 sec)


mysql>

Step 4 : Creating a Data Service to consume our procedure

Listed bellow is the configuration file which will create a data service exposing our stored procedure.

<data name="SalaryUpdateService">
   <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_sample1</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">10</property>
   </config>

   <query id="salaryUpdateQuery">
      <sql>call updateSalary(?,?)</sql>
      <param name="newSalary" sqlType="DOUBLE" type="IN" ordinal="1" />
      <result element="SalaryRevision" rowName="LastRevisedOn">
         <element name="Date" column="beforeDate" />
      </result>

      <param name="beforeDate" sqlType="DATE" type="INOUT" ordinal="2" />
   </query>

   <operation name="updateSalary">
      <call-query href="salaryUpdateQuery">
         <with-param name="newSalary" query-param="newSalary" />
         <with-param name="beforeDate" query-param="beforeDate" />
      </call-query>
   </operation>
</data>

Save this to a file called 'SalaryUpdateService.dbs' and upload to WSAS as follows.

Upload service

Step 5 : Invoking the service

Simplest way to try out our service is to use WSAS 'TryIt' feature. Following diagrams illustrate how this can be done.

TryIt

Tryit