Writing a Data Service to execute a stored procedure with WSO2 DSS

The WSO2 Data Services Server augments Service Oriented Architecture (SOA) development efforts by providing an easy to use platform for integrating data stores, creating composite data views, and hosting data services. This blog post is written to help users to getting started with WSO2 DSS with writing a data service to execute a stored procedure which is defined in a database.

Prerequisites

Before starting the implementation, you need the following things to be downloaded and installed.

1. Download WSO2 Data Services Server and install.

2. Download mysql server and install.

Once you have installed both WSO2 DSS and mysql in your enviornment, you can proceed to the next step.

Creating the stored procedure in mysql

1. Connect to mysql server with the following command.

mysql -u root -p

Enter password:

2. Create a sample databasae.

DROP DATABASE IF EXISTS ESB_SP_SAMPLE;

CREATE DATABASE ESB_SP_SAMPLE;

3. Create a table using the following statement.

USE ESB_SP_SAMPLE;

DROP TABLE IF EXISTS company;

CREATE TABLE company(name VARCHAR(10), id VARCHAR(10), price DOUBLE, location VARCHAR(10));

4. Inserts some data to the company table using following statements

INSERT INTO company VALUES ('WSO2','c1',2.9563,'SL');

INSERT INTO company VALUES ('IBM','c2',3.7563,'US');

INSERT INTO company VALUES ('SUN','c3',3.8349,'US');

INSERT INTO company VALUES ('MSFT','c4',3.2938,'US');

5. Create necessary Stored Procedures.

DROP PROCEDURE If EXISTS InsertRequestData;

CREATE PROCEDURE InsertRequestData(compName VARCHAR(10), compId VARCHAR(10), compPrice DOUBLE, compLocation VARCHAR(10)) INSERT INTO company VALUES(compName,compId,compPrice,compLocation) ;

Configuring the DSS to work with the mysql database

Download the mysql connector library from here and copy that file in to DSS_HOME/lib/extensions/ (create a directory if not exists) directory.

If you are a Windows user, you need to run wso2server.bat file, or, for a Linux user you need to run wso2server.sh script using command prompt or shell to start WSO2 Data Services server.

Writing the Data service to access the stored procedure

Now you have configured the DSS to work with mysql and created the stored procedure. Then you need to create the dataservice configuration file as below.

StockQuoteDataService.dbs

----------------------------------------

<data enableBatchRequests="true" name="StockQuoteDataService">

  <description>Sample Data Service</description>

  <config id="DemoDataSource">

     <property name="driverClassName">com.mysql.jdbc.Driver</property>

     <property name="url">jdbc:mysql://localhost:3306/ESB_SP_SAMPLE</property>

     <property name="username">root</property>

     <property name="password">root123</property>

  </config>

  <query id="insertRequestData" useConfig="DemoDataSource">

     <sql>Call ESB_SP_SAMPLE.InsertRequestData(?,?,?,?)</sql>

     <properties>

        <property name="org.wso2.ws.dataservice.query_timeout">100</property>

        <property name="org.wso2.ws.dataservice.force_jdbc_batch_requests">true</property>

     </properties>

     <param name="name" ordinal="1" sqlType="STRING"/>

     <param name="id" ordinal="2" sqlType="STRING"/>

     <param name="price" ordinal="3" sqlType="DOUBLE"/>

     <param name="location" ordinal="4" sqlType="STRING"/>

  </query>   

  <operation name="insertRequestData" returnRequestStatus="false">

     <call-query href="insertRequestData">

   <with-param name="name" query-param="name"/>

        <with-param name="id" query-param="id"/>

        <with-param name="price" query-param="price"/>

        <with-param name="location" query-param="location"/>        

     </call-query>

  </operation>  

</data>

In this configuration file, you can find 3 sections which are used to create the data service.

<config id="DemoDataSource">

     <property name="driverClassName">com.mysql.jdbc.Driver</property>

     <property name="url">jdbc:mysql://localhost:3306/ESB_SP_SAMPLE</property>

     <property name="username">root</property>

     <property name="password">root123</property>

  </config>

This is the data source configuration section which defines the database related information.

  <query id="insertRequestData" useConfig="DemoDataSource">

     <sql>Call ESB_SP_SAMPLE.InsertRequestData(?,?,?,?)</sql>

     <properties>

        <property name="org.wso2.ws.dataservice.query_timeout">100</property>

        <property name="org.wso2.ws.dataservice.force_jdbc_batch_requests">true</property>

     </properties>

     <param name="name" ordinal="1" sqlType="STRING"/>

     <param name="id" ordinal="2" sqlType="STRING"/>

     <param name="price" ordinal="3" sqlType="DOUBLE"/>

     <param name="location" ordinal="4" sqlType="STRING"/>

  </query>   

This is the actual query which is going to execute in the database once this data service is called.

  <operation name="insertRequestData" returnRequestStatus="false">

     <call-query href="insertRequestData">

   <with-param name="name" query-param="name"/>

        <with-param name="id" query-param="id"/>

        <with-param name="price" query-param="price"/>

        <with-param name="location" query-param="location"/>        

     </call-query>

  </operation>  

This part defined the actual operation clients are calling when they are accessing the data service from client side. This includes all the input parameters and output parameters for the query.

Now save this configuration in a file called StockQuoteDataService.dbs and save that file in DSS_HOME/repository/deployment/server/dataservices directory. Now you can see in the DSS console, the data service is deployed successfully. 

Accessing the data service

You can use the tryIt tool to access the data service and see your data is updating to the database. 

Cheers !!!!