Tuesday, July 3, 2012

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)


    6 comments:

    1. Hi Dinusha,

      your blog is very useful.I have a question,I wanted to know how to pass the input parameter which has multiple values

      example: employee id is the input and it has multiple values.

      Query:select empname,mail_id from employee where empid in('vs23445','bk55576') groupby empname;

      I tried declaring the emp id as below in wso2esb-datasource

      param name="employeeNumbers" paramType="ARRAY" sqlType="INTEGER" type="IN" ordinal="1"


      but not sure how to pass the values in Try it(comma separated ,separate param)

      The service is HTTP GET() request.

      ReplyDelete
      Replies
      1. Hi,

        In try-it you need to pass them as separate elements by repeating the 'employeeNumbers' parameter. Please refer to the "Array representation in the message" section in the following document [1]. It shows exactly what you need.

        [1] http://wso2.org/project/data-services/2.6.3/docs/array_data_type.html

        Delete
    2. Hi Dinusha,

      I have one Query
      Here i am sending the my GIT URL which contain my Query

      https://gist.github.com/anonymous/5300686

      Could you please help me.
      Thanks in Advance
      Anil

      ReplyDelete
    3. Hi Dinusha,

      I am using Mac-Os(10.7.5) and WSO2 ESB 4.0.3 and in this added DataService Hosting3.2.2 feature.
      I have one query which I am using to create data service(Using TeraData as Db):

      SELECT a.TNr,
      c.DNr,
      b.EventName,
      b.EVENT_DATE_GMT,
      b.RCd,
      b.RDesc
      FROM LOGISTICS.ST a
      INNER JOIN
      LOGISTICS.ACT b ON (b.SId=a.SId)
      INNER JOIN
      SupplyChain.Dnumber c ON (c.Id=b.SId)
      WHERE b.EventPhase_Cd='Actual' AND b.Rcd IS NOT NULL AND c.DNr='XXXXXXXX' or
      TNr = 'XXXXXXXXX'QUALIFY Row_number() OVER (PARTITION BY b.SId ORDER BY b.EVENT_DATE_GMT Desc)=1

      and giving all the selected item's present in query as output mapping.
      Added TeraJdbc4_All.jar and tdgssconfig.jar in /repository/components/lib.

      But while trying to run it's giving following error.
      "Input stream is null" in "tryIt" response window
      and in terminal"Polling prepared statement failed"

      Please help me on this.
      It's urgent.

      Thanks,
      Saurabh Suman

      ReplyDelete
    4. This is a very helpful post, thanks very much. The only question I had outstanding was how to use Boxcar from a service call. For those with the same question, there are new operations added to your service, simply call Begin before your operations and End when done or Abort if you wish to roll back.

      ReplyDelete