WSO2 DSS - Calling Stored Procedures with IN and OUT Parameters

This article will explain how we can call a stored procedure using WSO2 Data Services Server (WSO2 DSS). It also include details on how a stored procedures with IN and OUT parameters work with DSS. This example uses MySQL DB with DSS 3.5.1.

SQL Script for table and procedure

CREATE TABLE ALERT_DETAILS (ALERT_ID integer,OWNER VARCHAR(50),ALERT_TYPE integer);
INSERT INTO ALERT_DETAILS(ALERT_ID,OWNER,ALERT_TYPE) values (1, 'Peter',2);
INSERT INTO ALERT_DETAILS(ALERT_ID,OWNER,ALERT_TYPE) values (2, 'James',0);

CREATE PROCEDURE GET_ALERT_DETAILS (IN VIN_ALERT_ID INT, OUT VOUT_ALERT_TYPE INT,OUT VOUT_OWNER VARCHAR(50))
BEGIN
SELECT ALERT_TYPE,OWNER INTO VOUT_ALERT_TYPE, VOUT_OWNER FROM ALERT_DETAILS WHERE ALERT_ID = VIN_ALERT_ID ;
END

DSS Service 

<data name="ProcedureTest" transports="http https local">
   <config enableOData="false" id="TestMySQL">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/ActivitiEmployee</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="getAlertIds" useConfig="TestMySQL">
      <sql>call GET_ALERT_DETAILS(?,?,?)</sql>
      <result element="AlertDetails" rowName="Alerts">
         <element column="QPARAM_ALERT_TYPE" name="TYPE" xsdType="integer"/>
         <element column="QPARAM_OWNER" name="ALERTOWNER" xsdType="string"/>
      </result>
      <param name="QPARAM_ALERT_ID" sqlType="INTEGER"/>
      <param name="QPARAM_ALERT_TYPE" sqlType="INTEGER" type="OUT"/>
      <param name="QPARAM_OWNER" sqlType="STRING" type="OUT"/>
   </query>
   <operation name="getAlertOp">
      <call-query href="getAlertIds">
         <with-param name="QPARAM_ALERT_ID" query-param="SEARCH_ALERT_ID"/>
      </call-query>
   </operation>
</data>

Request and Response

Request 

http://localhost:9763/services/ProcedureTest.SOAP11Endpoint/

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://ws.wso2.org/dataservice">
   <soapenv:Header/>
   <soapenv:Body>
      <dat:getAlertOp>
         <dat:SEARCH_ALERT_ID>1</dat:SEARCH_ALERT_ID>
      </dat:getAlertOp>
   </soapenv:Body>
</soapenv:Envelope>

Response

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <AlertDetails xmlns="http://ws.wso2.org/dataservice">
         <Alerts>
            <TYPE>2</TYPE>
            <ALERTOWNER>Peter</ALERTOWNER>
         </Alerts>
      </AlertDetails>
   </soapenv:Body>
</soapenv:Envelope>