How to use result of a one operation as input to another operation using WSO2 Data Services Server ?

While we are dealing with database operations, one of most frequent use-case is to use result of a one query as input to a another query. When you are using WSO2 Data Services Server you have two approaches to achieve this.

1. Using the nested query feature.
2. Using Export Option provided in output mappings.

Here we are going to look at the second approach mentioned above. Before that let's briefly look at the difference of two approaches.

When we are using nested query feature , we call to a some query inside another query. So the inner query takes the input/s from the result of outer query being called and both queries get executed. Other than using the result of one query as input to a another query , the main use-case of nested query feature is to merge two result sets of two queries and build a one result. You can refer to the article "http://wso2.org/blog/sumedha/3175" for more details on nested query feature.

The "Export option" can be used when you need to pass result of one query as input to another. But you cannot merge the two results like we did in "Nested Query". As an example if you have a table with auto-increment field and you need to use this auto-incremented key value in another query then export option can be used to do it.

The important thing when using "Export option" is, the operation that has exported its result elements and the operation that going to consume that exported result should be invoked in a same session. So you need to enable boxcarring in dss to use "Export option".

Let's take a simple example and look at how can we use "Export option"

Step 1 : Creating sample Database

Let's create a database for our sample.

Our database 'DATASERVICE_SAMPLE' has two tables,
Employee - containing employee records
Salary - containing employee salary information.

Two tables can be linked using employeeNumber column.

Given bellow are the SQL for creating & populating our 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 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),
department VARCHAR(50)
);

insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'Developer','Engineering');
insert into Employees values (1003,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales','Marketing');
insert into Employees values (1004,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing', 'Marketing');

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 (1003,30000,'2007/01/20');
INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1004,17500,'2008/01/01');

Step 2: Clicking on the 'Create' link in left menu and enter a name to the Data service.

Here note that you need to enable boxcarring for data-service.

Step 3: Enter the details about the data-source which is using to create the data-service.

 
Step 4: Create query

4.a. Add output mappings and export the query result

4.b. Add query that takes above exported result as input.

Step 5: Add web service operations

Note that since query2 use the result exported from query1, we do not need to provide input parameters through operation when mapping query2 with operation.

operation that use query1

operation that use query2

The final dbs file will look as follows:

<data name="ExportOptionSample" enableBoxcarring="true">               
<config id="datasource1">                              
<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="q1" useConfig="datasource1">                              
<sql>select employeeNumber from Employees where department='Marketing'</sql>                              
<result element="Entries" rowName="Entry">                                             
<element name="employeeNumber" column="employeeNumber" export="employeeNumber" exportType="ARRAY" xsdType="xs:integer" />                              
</result>               
</query>               
<query id="q2" useConfig="datasource1">                              
<sql>update Salary set salary=salary+salary/10 where employeeNumber in (:employeeNumber)</sql>                              
<param name="employeeNumber" paramType="ARRAY" sqlType="INTEGER" />               
</query>               
<operation name="op1">                              
<call-query href="q1" />               
</operation>               
<operation name="op2">                              
<call-query href="q2" />               
</operation>
</data>

Invoke the operations in following order using try-it tool to see the result.

begin_boxcar
op1
op2
end_boxcar


mysql> select * from Salary;

+----------------+--------+-----------------+
| employeeNumber | salary | lastRevisedDate |
+----------------+--------+-----------------+
| 1002 | 13000 | 2007-11-30 |
| 1003 | 33000 | 2007-01-20 |
| 1004 | 19250 | 2008-01-01 |
+----------------+--------+-----------------+
3 rows in set (0.00 sec)