Writing a data service to execute stored procedure in WSO2 DSS

This blog post basically describes about the way to execute a stored procedure in WSO2 DSS. You can refer below steps

1. Create a sample database

CREATE DATABASE ESB_SAMPLE;

2. Create a table using below command

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

3. Create the stored procedure as follows

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

Now you can configure the DSS as follows

1. Download mysql connector and copy it to <DSS_HOME>/repository/components/lib

2. Create a data service as follows 

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

  <description>Sample Data Service</description>

  <config id="Demo">

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

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

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

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

  </config>

  <query id="insertData" useConfig="Demo">

     <sql>Call ESB_SAMPLE.InsertData(?,?,?,?)</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="insertData" returnRequestStatus="false">

     <call-query href="insertData">

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

3. Include the data service in <DSS_HOME>/repository/deployment/dataservices.

4. Use TryIt tool to access the data service