Saturday, March 4, 2017

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>


4 comments:

  1. Hi,

    Nice post about DSS. Can we insert Chinese or any other language characters as data? Right now the data is getting inserted as ????

    ReplyDelete
    Replies
    1. Hi Prabhakaran,

      Chinese characters can't be given. Write custom mediator where it is possible to insert chinese characters.

      Delete
  2. Hi,

    I have the stored procedure and I want to insert parent child record. How to make the array in input mapping.

    ReplyDelete
  3. Thanks for sharing the codes here. Very useful. I would like to share about https://wso2.com/products/data-services-server/
    Oracle training in Chennai

    ReplyDelete