How to use UDT (User Difined Types) within WSO2 Data Services Server ?

Other than the ordinal sql data types, WSO2 Data Services Server support to have UDT types(User Defined Types) in result of SQL query.

This post describes how to use Oracle UDT type result parameters in your data-service.

Step 1: Creating simple UDT type and a table.

Login to the oracle server and execute following sql commands to create new UDT type and table.

Creating UDT type:

CREATE OR REPLACE TYPE  phone_t AS OBJECT (
a_code   CHAR(3),
p_number CHAR(8));
/

Creating table that has field type of above created UDT (phone_t):

CREATE TABLE phone (
per_id    NUMBER(10),
per_phone phone_t);

You can verify the table structure by executing follwing two comands.

set describe depth all;
desc phone;

Insert some sample data to table:

insert into phone(per_id, per_phone) values(1, phone_t('SAM', '12345678'));
insert into phone(per_id, per_phone) values(1, phone_t('NOK', '87654321'));

Step 2: Creating data-service.

Here we are going to create a data-service that has an opertion to reteive all data from the phone table. The important thing to notice is, the phone table contains a UDT field called 'phone_t' and how we should map this into result elements when creating the data-service.

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

Next step is to enter details about the data-source which is using to create the data-service.

Next step is to create a query to extract information along with the structure of the response.

Following image shows how 'phone_t' column(UDT type) is mapped to output mappings in data-service.

Note that we should provide index of UDT attribute alone with the table column name to map the corresponding element in UDT. i.e in our sample UDT 'phone_t' index 0 maps to the attribute 'a_code' and index 1 maps to the attribute 'p_number'.
In 'phone' table 'per_phone' column type is set to UDT type 'phone_t'. So when we provide data-source column name as 'per_phone[0]', it will return the first element(i.e a_code) of phone_t object and 'per_phone[1]' will return the second element(i.e p_number) and so.

Final step is to add web service operation and finish the data-service creation.

The final dbs file will look as follows,

Click on "Try It" link to invoke the service and see the result.

How to use a Carbon JNDI data-source within a Spring Service

If you are familiar with Spring Framework, you may know that Spring Framework has provided templates to data access using JDBC and JNDI data-sources. So it's bit different how we write code to access a database or a JNDI data-source than normal JDBC connection code or JNDI lookup code within Spring Framework.

This post describes you how to access a data-source that created using WSO2 Carbon data-source component, within a Spring Service.

Following code segment shows how to make connection to a JNDI data-source using normal java program.

Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory"); //same context factory class provided when creating JNDI data-source.
env.put(Context.PROVIDER_URL, "rmi://localhost:2195"); //same provider url provided when creating JNDI data-source.

InitialContext ctx = new InitialContext(env);
DataSource ds = (DataSource) ctx.lookup("CarbonJndiDS"); //"CarbonJndiDS" is the JNDI data-source name.

conn = ds.getConnection();

But when it comes to the Spring framework , We do not have to write above code to make connection. Instead of that we need to put some entry to bean xml of the Spring project. By adding following two bean elements to the bean xml of Spring project will do the same job that we done using the above code segment using in a normal java program.

<bean id="jndiTemplate" class="org.springframework.jndi.JndiTemplate">
    <property name="environment">
        <props>
            <prop key="java.naming.factory.initial">com.sun.jndi.rmi.registry.RegistryContextFactory</prop>
            <prop key="java.naming.provider.url">rmi://localhost:2195</prop>
        </props>
    </property>
</bean>

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiTemplate" ref="jndiTemplate"/>
    <property name="jndiName" value="CarbonJndiDS"/>
</bean>

In the above first bean (i.e: "jndiTemplate"), it has defined a bean property called "environment" and has provided JNDI data-source properties that used to create Carbon data-source as sub properties.
In the second bean (i.e: "dataSource"), note the "jndiName" property value. It should be the name of Carbon data-source name.

Once you create the connection to Carbon JNDI data-source using above two beans, then you can use JDBCTemplate provided by Spring framework and code to perform SQL operations.

How to access Carbon data-source from a web-application

WSO2 Application Server shift with inbuilt Carbon data-source feature. This post describes how to create a Carbon data-source using WSO2 Application Server and access it within a web-application.

Step1 : Create sample database

create database wes_db;
use wes_db;
create table wes_engineer(WES_ID integer, WES_NAME varchar(30));
insert into wes_engineer values(1, 'Amy');
insert into wes_engineer values(2, 'Alfred');

Step2 : Create carbon data-source

To be able to refer Carbon data-source within a web-app, first you need to register your Carbon data-source with a JNDI naming service. When you creating a carbon data-source there is option to select data-source repository type. From there select JNDI as repository and provide the jndi_context_class and the provider_url. You can use "com.sun.jndi.rmi.registry.RegistryContextFactory" JNDI service provider inside Carbon server.

      
                                  









Image 01: Adding Carbon data-source



Step3 : Create a simple web-app

This simple web app will access the above data-source and print all data available in wes_engineer table.

Once you registered your carbon data-source with a JNDI service, you can access that data-source directly within your web application by doing a JNDI lookup. Following code segment will illustrate you how to call carbon data-source within web-app.

Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory"); //same context factory class provided when creating carbon data-source.
env.put(Context.PROVIDER_URL, "rmi://localhost:2195"); //same provider url provided when creating carbon data-source.

InitialContext ctx = new InitialContext(env);
DataSource ds = (DataSource) ctx.lookup("jndiDS"); //"jndiDS" is the carbon data-source name that i have created. Please note that you may need to add commons-dbcp.jar and commons-pool.jar to web-app class path to do this cast.

conn = ds.getConnection();

The full TestCarbonDataSource.java  servlet class and project web.xml files are provided below.

TestCarbonDataSource.java

package org.wso2;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.naming.Context;
import javax.sql.DataSource;
import javax.naming.InitialContext;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.*;


public class TestCarbonDataSource extends HttpServlet {

public TestCarbonDataSource() {
super();
}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
performTask(request, response);
}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
performTask(request, response);
}

private void performTask(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
out.println("<br/>");
out.println(testJndiDataSource());
}

public String testJndiDataSource() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
try {

Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.rmi.registry.RegistryContextFactory");
env.put(Context.PROVIDER_URL, "rmi://localhost:2195");
InitialContext ctx = new InitialContext(env);

DataSource ds = (DataSource) ctx.lookup("jndiDS");
conn = ds.getConnection();

st = conn.createStatement();
rs = st.executeQuery("SELECT * FROM wes_engineer");

while (rs.next()) {
String id = rs.getString("WES_ID");
String firstName = rs.getString("WES_NAME");
sb.append("ID: " + id + ", First Name: " + firstName + "
");
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }
try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
}
return sb.toString();
}
}

web.xml

<web-app>
    <servlet>
        <servlet-name>TestCarbonDataSource</servlet-name>
        <servlet-class>org.wso2.TestCarbonDataSource</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>TestCarbonDataSource</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

Once you create web-app, you can host it in WSO2 Application Server or any other application servers.


Image 02: Deploy web-app in Application server.

Once web-app is deployed in Application server click on "Go To URL" link or directly go to the link using browser "http://localhost:9763/carbonDSwebApp/"

  
Image 03: Accessing web-app

You can see the output as bellow.

                               

How to call a Oracle Stored Function from WSO2 Data Service?

1. Create table executing the following command.

CREATE TABLE TEAMS(id INTEGER, team VARCHAR(30));

2. Following is the stored function which returns total number of entries in the above table.

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR, eid IN NUMBER) RETURN INTEGER 

AS myCount INTEGER;

BEGIN

    INSERT INTO TEAMS values(eid, ename);

    SELECT COUNT(*) into myCount from TEAMS;

    RETURN myCount;

END;

/

3.  Create the data service using following content.

<data name="testOracleFunction">

   <config id="or">

      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>

      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:user/pwd@localhost:1521/XE</property>

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

      <property name="org.wso2.ws.dataservice.password">pwd</property>

   </config>



   <query id="q1" useConfig="or">

      <sql>{call ?:=myfunction(?,?)}</sql>

      <result element="TotalTeams" rowName="">

         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />

      </result>

      <param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />

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

      <param name="eid" sqlType="INTEGER" ordinal="3" />

   </query>



   <operation name="op1">

      <call-query href="q1">

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

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

      </call-query>

   </operation>

</data>

Note the sql query used to call the stored function: "{call ?:=myfunction(?,?)}". First input parameter carries the return value of function. Other two parameters are inputs to the function.
Important thing is we need to define a Input parameter with OUT type to get the result of function(i.e the first parameter in above sql query). Then we need to define a Output parameter to get this value as a result set from data service. Following section in the data service is used to do this.

<result element="TotalTeams" rowName="">
         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
</result>
<param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />
     

ORA-14551: cannot perform a DML operation inside a query

While I was calling a oracle function which initially insert data into a table, I got an error "ORA-14551: cannot perform a DML operation inside a query". I thought to share this post since it can be help to anyone who having the same error.

 Bellow is the oracle function:

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR) RETURN INTEGER
AS eid INTEGER;

BEGIN

    INSERT INTO TEAM values(5,ename);

    SELECT id into eid from TEAM WHERE TEAM.name=ename;

    RETURN eid;

END;

/ 

And I called the function using the command,
SQL> select myfunction('test') from dual; 
which cause to give the above error.


Found that we cannot use 'select' to call functions which perform DML(insert. delete, update) operations.  It should be called using the 'call' command as bellow.

SQL> var myvar NUMBER;
SQL> call myfunction('test') into :myvar;  

Call completed.

SQL> print myvar;
MYVAR
----------
     5

Retrieve data from Google spreadsheet and insert data into a database using WSO2 Data Service Server

WSO2 Data Service Server allows to expose the data stored in data sources such as relational databases, CSV files , MS-Excel files and Google spreadsheets as web services.

Think a situation where you have added thousands of data in some google spreadsheets and now you want to insert these data into a database for the propose of using data for multiple applications. If you are going to do this by manually then you will be in a big trouble. You can simply write a data service using the WSO2 Data Service Server which will automate this task. Here no need to have any programming language, we can do it by using some MySQL and data service.

You will be using following features provide by WSO2 Data Service Server when writing this data service.
    - using a Google Spreadsheet as a data source. 
    - nested query feature.
    - call MySQL Stored Procedure from Data Service

The spread sheet that contains data is,
https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0
Column titles in the spread sheet is important when writing the query to retrieve data.

And database that going to insert data is patch_details and we are going to insert data into two tables patch & customer.

mysql> create database patch_details;
mysql> use patch_details;
mysql> CREATE TABLE patch(PATCH_NAME VARCHAR(256), SVN_REVISION VARCHAR(30), DESCRIPTION VARCHAR(256), RELEASE_DATE DATE, PRIMARY KEY(PATCH_NAME));
mysql> CREATE TABLE customer(CUSTOMER_ID INT NOT NULL AUTO_INCREMENT, CUSTOMER_NAME VARCHAR(256),PRIMARY KEY(CUSTOMER_ID)) AUTO_INCREMENT=1 ;

Since there is more than one table to insert data, we can write a stored procedure that will insert the data into tables at once.
If data taken from the google spreadsheet is going to insert into a one table, then no need to write a stored procedure.

CREATE PROCEDURE `insertPatchData`(IN patchName varchar(256), IN svnRevision varchar(256), IN releaseDate varchar(256), IN customerName varchar(256))
BEGIN
    DECLARE customerAvailable INT DEFAULT 0;
    DECLARE patchAvailable INT DEFAULT 0;
    SET customeravAilable = 0;
    SET patchAvailable = 0;

    SELECT STR_TO_DATE(releaseDate, '%m/%d/%Y') INTO @date;
        
    SELECT 1 INTO patchAvailable FROM patch WHERE PATCH_NAME = patchName;
    IF patchAvailable !=1     
    THEN
        INSERT INTO patch (PATCH_NAME, SVN_REVISION, DESCRIPTION, RELEASE_DATE) VALUES (patchName, svnRevision,description, @date);
    END IF;
         
    IF NOT customerName IS NULL
    THEN 
        SELECT 1 INTO customerAvailable FROM customer WHERE CUSTOMER_NAME = customerName;
        IF customerAvailable != 1
        THEN 
            INSERT INTO customer (CUSTOMER_NAME) values (customerName);
        END IF;
    END IF;
END


Now create the data service:
Add google spreadsheet as a data source

<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
</data>

Add patch_details database as a data source.

<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
   <config id="PatchDB">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">root</property>
      <property name="org.wso2.ws.dataservice.minpoolsize"></property>
      <property name="org.wso2.ws.dataservice.maxpoolsize"></property>
      <property name="org.wso2.ws.dataservice.validation_query"></property>
   </config>
</data>

Create a query that retrieve the data from google spread sheet.
In this query the column names of the output mappings should be same as the column titles in spreadsheet.

<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">  
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
   <config id="PatchDB">     
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>     
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>     
      <property name="org.wso2.ws.dataservice.user">root</property>     
      <property name="org.wso2.ws.dataservice.password">root</property>     
      <property name="org.wso2.ws.dataservice.minpoolsize"></property>     
      <property name="org.wso2.ws.dataservice.maxpoolsize"></property>     
      <property name="org.wso2.ws.dataservice.validation_query"></property>  
   </config>  
   <query id="getPatches" useConfig="Gspread">     
      <gspread>        
         <worksheetnumber>1</worksheetnumber>        
         <startingrow>2</startingrow>        
         <maxrowcount>-1</maxrowcount>        
         <hasheader>false</hasheader>     
      </gspread>     
      <result element="patches" rowName="patch">        
         <element name="PatchName" column="Patch Name" xsdType="xs:string" />        
         <element name="Client" column="Client" xsdType="xs:string" />        
         <element name="Date" column="Date" xsdType="xs:date" />        
         <element name="Description" column="JIRA/Issue Description" xsdType="xs:string" />        
         <element name="SVNRevision" column="SVN Revision" xsdType="xs:string" />        
         <element name="Fixedby" column="Fixed by" xsdType="xs:string" />        
         <element name="Notes" column="Additional Comments" xsdType="xs:string" />        
         <element name="Jars" column="Jars " xsdType="xs:string" />     
      </result>  
   </query>
</data>

Then create a operation that use the above query. Using that operation we can test whether query works properly.

<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">  
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
   <config id="PatchDB">     
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>     
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>     
      <property name="org.wso2.ws.dataservice.user">root</property>     
      <property name="org.wso2.ws.dataservice.password">root</property>     
      <property name="org.wso2.ws.dataservice.minpoolsize"></property>     
      <property name="org.wso2.ws.dataservice.maxpoolsize"></property>     
      <property name="org.wso2.ws.dataservice.validation_query"></property>  
   </config>  
   <query id="getPatches" useConfig="Gspread">     
      <gspread>        
         <worksheetnumber>1</worksheetnumber>        
         <startingrow>2</startingrow>        
         <maxrowcount>-1</maxrowcount>        
         <hasheader>false</hasheader>     
      </gspread>     
      <result element="patches" rowName="patch">        
         <element name="PatchName" column="Patch Name" xsdType="xs:string" />        
         <element name="Client" column="Client" xsdType="xs:string" />        
         <element name="Date" column="Date" xsdType="xs:date" />        
         <element name="Description" column="JIRA/Issue Description" xsdType="xs:string" />        
         <element name="SVNRevision" column="SVN Revision" xsdType="xs:string" />        
         <element name="Fixedby" column="Fixed by" xsdType="xs:string" />        
         <element name="Notes" column="Additional Comments" xsdType="xs:string" />        
         <element name="Jars" column="Jars " xsdType="xs:string" />     
      </result>  
   </query>  
   <operation name="getPatchData">     
      <description></description>     
      <call-query href="getPatches" />  
   </operation>
</data>

Click on the try it and check whether operation works properly.
It should return the all patch details when you click on the operation.

Create a query that calls to the 'insertPatchData' stored procedure which is used to insert data into tables.
<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
   <config id="PatchDB">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">root</property>
      <property name="org.wso2.ws.dataservice.minpoolsize"></property>
      <property name="org.wso2.ws.dataservice.maxpoolsize"></property>
      <property name="org.wso2.ws.dataservice.validation_query"></property>
   </config>
   <query id="getPatches" useConfig="Gspread">
      <gspread>
         <worksheetnumber>1</worksheetnumber>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
         <hasheader>true</hasheader>
      </gspread>
      <result element="patches" rowName="patch">
         <element name="Client" column="Client" xsdType="xs:string" />
         <element name="Date" column="Date" xsdType="xs:date" />
         <element name="Description" column="JIRA/Issue Description" xsdType="xs:string" />
         <element name="SVNRevision" column="SVN Revision" xsdType="xs:string" />
         <element name="Fixedby" column="Fixed by" xsdType="xs:string" />
         <element name="Notes" column="Additional Comments" xsdType="xs:string" />
         <element name="Jars" column="Jars " xsdType="xs:string" />
         <element name="PatchName" column="Patch Name" xsdType="xs:string" />
      </result>
   </query>
   <query id="insertPatchTodb" useConfig="PatchDB">
      <sql>CALL insertPatchData(?, ?, ?, ?)</sql>
      <param name="patchName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1" />
      <param name="svnRevision" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="2" />
      <param name="releaseDate" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="3" />
      <param name="customerName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="4" />
   </query>
   <operation name="getPatchData">
      <description></description>
      <call-query href="getPatches" />
   </operation>
</data>


Then you need to give the data retrieved from the google spreadsheet as input to the "insertPatchTodb" query.
To do this use Data Service nested query feature and call insertPatchTodb query inside getPatches query.

<data name="PatchDetailsDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">
   <config id="Gspread">
      <property name="gspread_datasource">https://spreadsheets0.google.com/ccc?key=tXMHzbjQmQy8KHiaSDR8_PA&amp;hl=en#gid=0</property>
      <property name="gspread_visibility">private</property>
      <property name="gspread_username">user@gmail.com</property>
      <property name="gspread_password">password</property>
   </config>
   <config id="PatchDB">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">root</property>
      <property name="org.wso2.ws.dataservice.minpoolsize"></property>
      <property name="org.wso2.ws.dataservice.maxpoolsize"></property>
      <property name="org.wso2.ws.dataservice.validation_query"></property>
   </config>
   <query id="getPatches" useConfig="Gspread">
      <gspread>
         <worksheetnumber>1</worksheetnumber>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
         <hasheader>true</hasheader>
      </gspread>
      <result element="patches" rowName="patch">
         <element name="Client" column="Client" xsdType="xs:string" />
         <element name="Date" column="Date" xsdType="xs:date" />
         <element name="Description" column="JIRA/Issue Description" xsdType="xs:string" />
         <element name="SVNRevision" column="SVN Revision" xsdType="xs:string" />
         <element name="Fixedby" column="Fixed by" xsdType="xs:string" />
         <element name="Notes" column="Additional Comments" xsdType="xs:string" />
         <element name="Jars" column="Jars " xsdType="xs:string" />
         <element name="PatchName" column="Patch Name" xsdType="xs:string" />
         <call-query href="insertPatchTodb">
            <with-param name="patchName" query-param="Patch Name" />
            <with-param name="svnRevision" query-param="SVN Revision" />
            <with-param name="releaseDate" query-param="Date" />
            <with-param name="customerName" query-param="Client" />
         </call-query>
      </result>
   </query>
   <query id="insertPatchTodb" useConfig="PatchDB">
      <sql>CALL insertPatchData(?, ?, ?, ?)</sql>
      <param name="patchName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1" />
      <param name="svnRevision" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="2" />
      <param name="releaseDate" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="3" />
      <param name="customerName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="4" />
   </query>
   <operation name="getPatchData">
      <description></description>
      <call-query href="getPatches" />
   </operation>
</data>

Now when you execute the 'getPatchData' operation both data retrieving from google spreadsheet and inserting data to tables is happenning.
Check the two tables.Data should have entered.
mysql> select * from patch;
mysql> select * from customer;

Split Strings in MySQL

MySQL does not have a built in function to split a comma separated string. But it is important to have such  function in some cases.

Think there is a string that contains several names separated by commas and you want to insert each of them separately into a table.

You can write your own MySQL function to do this.

Syntax for create a function is as follows,

CREATE FUNCTION function_name ([parameterlist]) RETURNS datatype

BEGIN

     RETURN value_to_return;

END

Following function takes three arguments and it returns the pointed element in given string.

CREATE FUNCTION split_string(


stringToSplit (VARCHAR(256), sign VARCHAR(12), position INT) RETURNS VARCHAR(256)
BEGIN
        RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position), 
LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position -1)) + 1), sign, '');
END

Example
Given String = 'Dinusha,Nuwan,Nirosh'
Need to take second name separated by comma. For that, function call will look likes follows,

mysql> select split_string('Dinusha,Nuwan,Nirosh',',', 2) as name;

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

| name      |

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

| Nuwan     |

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

This query can be used to get how many comma signs are in the input string

SELECT LENGTH(stringToSplit) - LENGTH(REPLACE(stringToSplit, ',', '')) INTO noOfCommas;

Example

mysql> SELECT LENGTH('Dinusha,Nuwan,Nirosh') - LENGTH(REPLACE('Dinusha,Nuwan,Nirosh', ',', '')) INTO @noOfCommas;

Query OK, 1 row affected (0.00 sec)



mysql> select @noOfCommas;

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

| @noOfCommas        |

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

|           2        |

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

Using above query and split_string function, we can write a stored procedure that do some SQL operations on each element of given string.

Following stored procedure will split the string and insert the spitted strings into a table.

CREATE PROCEDURE insertEngineer( IN engineerName varchar(256))
BEGIN
    CREATE TABLE IF NOT EXISTS `engineer` (`ID` int(11) NOT NULL auto_increment,`NAME` varchar(256) NOT NULL,
PRIMARY KEY (`ID`)) AUTO_INCREMENT=1 ;
 
    DECLARE x INT DEFAULT 0; 
    DECLARE y INT DEFAULT 0; 
    SET y = 1;  
 
    IF NOT engineerName IS NULL 
    THEN 
           SELECT LENGTH(engineerName) - LENGTH(REPLACE(engineerName, ',', '')) INTO @noOfCommas; 
      
           IF  @noOfCommas = 0 
          THEN 
                 INSERT INTO engineer(NAME) VALUES(engineerName); 
          ELSE 
                SET x = @noOfCommas + 1; 
                WHILE y  <=  x DO 
                   SELECT split_string(engineerName, ',', y) INTO @engName; 
                   INSERT INTO engineer(NAME) VALUES(@engName); 
                   SET  y = y + 1; 
                END WHILE; 
        END IF; 
    END IF; 
END

Example of using stored procedure

mysql> call insertEngineer('dinusha,nuwan,nirosh');

Query OK, 1 row affected (0.12 sec)



mysql> select * from engineer;

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

| ID | NAME     |

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

|  1 | dinusha  |

|  2 | nuwan    |

|  3 | nirosh   |

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