How to call a Data Service operation with in-clause from WSO2 EI

Megala Uthayakumar
Apr 20, 2018 · 2 min read

WSO2 DSS is shipped with many pre-build samples. I have used RDBMS sample, to showcase this use case. In this sample, we have an operation called setSalaryForEmployees. This operation can be called to update the salary of set of employees.

<query id="setSalaryForEmployeesQuery" useConfig="default">
<sql>update Employees set salary=:salary where employeeNumber in (:employeeNumbers)</sql>
<param name="salary" ordinal="1" paramType="SCALAR" sqlType="DOUBLE" type="IN"/>
<param name="employeeNumbers" ordinal="2" paramType="ARRAY" sqlType="INTEGER" type="IN"/>
</query>

This particular query includes in clause. In this blog, I will be presenting two solutions to call this particular operation from EI.

Solution 1

For the solution 1, we have created an API resource with POST method and the relevant list of employeeNumbers are passed in the request body in the following format,

<uniqename>
<value>111</value>
<value>112</value>
</uniqename>

In API level, we can use xslt mediator as follow to convert this to the format expected by the DSS,

<localEntry xmlns="http://ws.apache.org/ns/synapse" key="test_xslt">
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:p="http://ws.wso2.org/dataservice/samples/rdbms_sample"
version="1.0">
<xsl:output indent="yes" method="xml" omit-xml-declaration="yes"/>
<xsl:template match="*">
<p:setSalaryForEmployees>
<p:salary>20000</p:salary>
<xsl:for-each select="/uniqename/value">
<p:employeeNumbers><xsl:value-of select="."/> </p:employeeNumbers>
</xsl:for-each>
</p:setSalaryForEmployees>
</xsl:template>
</xsl:stylesheet>
</localEntry>

Solution 2

In this second approach we are using GET API resource and relevant employee numbers will be passed in the URL with the space separated format as follow,

http://localhost:8281/api/test/20000/111 112

Resource definition is follow,

<resource methods="GET" protocol="http" uri-template="/test/{salary}/{uniqname}">

In the API level, we can use a script mediator to convert this to the expected format of DSS as follow,

<script language="js">
<![CDATA[
var property = mc.getProperty("uniqname");
var salary = mc.getProperty("uri.var.salary");
var properties = property.split(" ");
var clientvarPayload = <p:setSalaryForEmployees xmlns:p="http://ws.wso2.org/dataservice/samples/rdbms_sample"><p:salary>{salary}</p:salary></p:setSalaryForEmployees>;
var payload;
for (var x = 0; x < properties.length; x++) {
clientvarPayload.setSalaryForEmployees+= <p:employeeNumbers xmlns:p=”http://ws.wso2.org/dataservice/samples/rdbms_sample">{properties[x]}</p:employeeNumbers>;
}
mc.setPayloadXML(clientvarPayload);
]]></script>

References

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade