MyCodeIdeas: May 2017

Sunday, May 28, 2017

WSO2 DSS - Using Microsoft SQL Server Stored Procedures

The following example shows how to use Microsoft SQL Server stored procedure which has input parameter, output parameter and a return value with WSO2 DSS.

SQL for create table and procedure

CREATE TABLE [dbo].[tblTelphoneBook](

  [name] [varchar](50) NULL,

  [telephone] [varchar](50) NULL

)

insert into tblTelphoneBook values('Jane','4433');

CREATE PROCEDURE [dbo].[PhoneBookSP3]  

  @name varchar(50),  

  @telephone varchar(50)  OUTPUT  

AS  

BEGIN  

SELECT @telephone = telephone from tblTelphoneBook where name = @name;

return 13;
END;

Data Service

<data name="TestMSSQLService" transports="http https local">

  <config enableOData="false" id="MSSQLDB">

     <property name="driverClassName">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>

     <property name="url">jdbc:sqlserver://localhost:1433;databaseName=Master</property>

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

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

  </config>

  <query id="testQuery3" useConfig="MSSQLDB">

     <sql>{? = call [dbo].[PhoneBookSP3](?,?)}</sql>

     <result element="Entries" rowName="record">

        <element column="telephone" name="telephone" xsdType="string"/>

        <element column="status" name="status" xsdType="integer"/>

     </result>

     <param name="status" sqlType="INTEGER" type="OUT"/>

     <param name="name" sqlType="STRING"/>

     <param name="telephone" sqlType="STRING" type="OUT"/>

  </query>

  <operation name="opTestQuery3">

     <call-query href="testQuery3">

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

     </call-query>

  </operation>
</data>

Request and Response

Request

<body>
  <p:opTestQuery3 xmlns:p="http://ws.wso2.org/dataservice">

     <!--Exactly 1 occurrence-->

     <p:name>Jane</p:name>

  </p:opTestQuery3>
</body>

Response

<Entries xmlns="http://ws.wso2.org/dataservice">

  <record>

     <telephone>4433</telephone>

     <status>13</status>

  </record>

</Entries>