MyCodeIdeas

Tuesday, August 4, 2020

Moving to Medium

I have recently moved to Medium and my latest writings can be found in https://medium.com/@anupama.pathirage

Wednesday, August 16, 2017

WSO2 DSS - Using Oracle Ref Cursors

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. This sample shows how to use ref cursors as OUT parameter in a stored procedure or as return parameter in a function with WSO2 DSS. The sample is using oracle DB with DSS 3.5.1

SQL Scripts: To create table, insert data and create stored procedure & function.

CREATE TABLE customers (id NUMBER, name VARCHAR2(100), location VARCHAR2(100));

INSERT into customers (id, name, location) values (1, 'Anne', 'UK');
INSERT into customers (id, name, location) values (2, 'George', 'USA');
INSERT into customers (id, name, location) values (3, 'Peter', 'USA');
INSERT into customers (id, name, location) values (4, 'Will', 'NZ');

CREATE PROCEDURE getCustomerDetails(i_ID IN NUMBER, o_Customer_Data  OUT SYS_REFCURSOR) 
IS
BEGIN
  OPEN o_Customer_Data FOR 
  SELECT * FROM customers WHERE id>i_ID;
END getCustomerDetails;
/



CREATE FUNCTION returnCustomerDetails(i_ID IN NUMBER) 
RETURN  SYS_REFCURSOR
AS
o_Customer_Data   SYS_REFCURSOR;
BEGIN
  OPEN o_Customer_Data FOR 
  SELECT * FROM customers WHERE id>i_ID;
  return o_Customer_Data;
END;
/

Data Service

Following data service has two queries and associated operations.

GetDataAsOut - Oracle ref cursor is used as out parameter of a stored procedure.
GetDataAsReturn - Oracle ref cursor is used as return value of a function.

<data name="TestRefCursor" transports="http https local">
   <config enableOData="false" id="TestDB">
      <property name="driverClassName">oracle.jdbc.driver.OracleDriver</property>
      <property name="url">jdbc:oracle:thin:@localhost:1521/xe</property>
      <property name="username">system</property>
      <property name="password">oracle</property>
   </config>
   <query id="    " useConfig="TestDB">
      <sql>call getCustomerDetails(?,?)</sql>
      <result element="CustomerData" rowName="Custmer">
         <element column="id" name="CustomerID" xsdType="string"/>
         <element column="name" name="CustomerName" xsdType="string"/>
      </result>
      <param name="id" sqlType="INTEGER"/>
      <param name="data" sqlType="ORACLE_REF_CURSOR" type="OUT"/>
   </query>
   <query id="GetDataAsReturn" useConfig="TestDB">
      <sql>{? = call returnCustomerDetails(?)}</sql>
      <result element="CustomerDataReturn" rowName="Custmer">
         <element column="id" name="CustomerID" xsdType="string"/>
         <element column="name" name="CustomerName" xsdType="string"/>
      </result>
      <param name="data" ordinal="1" sqlType="ORACLE_REF_CURSOR" type="OUT"/>
      <param name="id" ordinal="2" sqlType="INTEGER"/>
   </query>
   <operation name="GetCustomerDataAsOut">
      <call-query href="GetDataAsOut">
         <with-param name="id" query-param="id"/>
      </call-query>
   </operation>
   <operation name="GetCustomerDataAsReturn">
      <call-query href="GetDataAsReturn">
         <with-param name="id" query-param="id"/>
      </call-query>
   </operation>
</data>

Sample Requests

Operation GetCustomerDataAsOut

Request:

<body>
   <p:GetCustomerDataAsOut xmlns:p="http://ws.wso2.org/dataservice">
      <!--Exactly 1 occurrence-->
      <p:id>2</p:id>
   </p:GetCustomerDataAsOut>
</body>

Response:

<CustomerData xmlns="http://ws.wso2.org/dataservice">
   <Custmer>
      <CustomerID>3</CustomerID>
      <CustomerName>Peter</CustomerName>
   </Custmer>
   <Custmer>
      <CustomerID>4</CustomerID>
      <CustomerName>Will</CustomerName>
   </Custmer>
</CustomerData>

Operation GetCustomerDataAsReturn

Request:

<body>
   <p:GetCustomerDataAsReturn xmlns:p="http://ws.wso2.org/dataservice">
      <!--Exactly 1 occurrence-->
      <p:id>2</p:id>
   </p:GetCustomerDataAsReturn>
</body>

Response:

<CustomerDataReturn xmlns="http://ws.wso2.org/dataservice">
   <Custmer>
      <CustomerID>3</CustomerID>
      <CustomerName>Peter</CustomerName>
   </Custmer>
   <Custmer>
      <CustomerID>4</CustomerID>
      <CustomerName>Will</CustomerName>
   </Custmer>
</CustomerDataReturn>

Thursday, August 10, 2017

WSO2 DSS - Exposing Data as REST Resources

The WSO2 Data Services feature supports exposing data as a set of REST style resources in addition to the SOAP services. This sample demonstrates how to use rest resources for data inserts and batch data inserts via POST requests.

<data enableBatchRequests="true" name="TestBatchRequests" transports="http https local">
   <config enableOData="false" id="MysqlDB">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/testdb</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="InsertData" useConfig="MysqlDB">
      <sql>Insert into Customers(customerId,firstName,lastName,registrationID) values (?,?,?,?)</sql>
      <param name="p0_customerId" sqlType="INTEGER"/>
      <param name="p1_firstName" sqlType="STRING"/>
      <param name="p2_lastName" sqlType="STRING"/>
      <param name="p3_registrationID" sqlType="INTEGER"/>
   </query>
   <resource method="POST" path="InsertDataRes">
      <call-query href="InsertData">
         <with-param name="p0_customerId" query-param="p0_customerId"/>
         <with-param name="p1_firstName" query-param="p1_firstName"/>
         <with-param name="p2_lastName" query-param="p2_lastName"/>
         <with-param name="p3_registrationID" query-param="p3_registrationID"/>
      </call-query>
   </resource>
</data>

Insert Single Row of Data

When you send an HTTP POST request, the format of the JSON object name should be "_post$RESOURCE_NAME", and the child name/values of the child fields should be the names and values of the input parameters in the target query.

Sample Request : http://localhost:9763/services/TestBatchRequests/InsertDataRes
Http Method : POST
Request Headers : Content-Type : application/json
Payload :

{
  "_postinsertdatares": {
    "p0_customerId" : 1,
    "p1_firstName": "Doe",
    "p2_lastName": "John",
    "p3_registrationID": 1
  }
}

Insert Batch of Data

When batch requests are enabled for data services resources, resource paths are created with the "_batch_req" suffix. In the payload content, the single request JSON object becomes one of the many possible objects in a parent JSON array object.

Sample Request : http://localhost:9763/services/TestBatchRequests/InsertDataRes_batch_req
Http Method : POST
Request Headers : Content-Type : application/json
Payload :

{
    "_postinsertdatares_batch_req": {
        "_postinsertdatares": [{
                "p0_customerId": 1,
                "p1_firstName": "Doe",
                "p2_lastName": "John",
                "p3_registrationID": 10
            },
            {
                "p0_customerId": 2,
                "p1_firstName": "Anne",
                "p2_lastName": "John",
                "p3_registrationID": 100
            }
        ]
    }
}

Monday, July 3, 2017

WSO2 DSS - Use User Defined Data types (UDT)

This post explains how to use Oracle UDTs with WSO2 DSS.

SQL For Create UDT and Tables:

CREATE OR REPLACE TYPE  T_Address AS OBJECT (
home_no   NUMBER(5),
city varchar2(20)
);

CREATE TABLE Students (
id    NUMBER(10),
home_addr T_Address);

insert into Students(id, home_addr) values(1, T_Address(10, 'Colombo'));
insert into Students(id, home_addr) values(2, T_Address(30, 'New York'));

Data Service:

<data name="TestUDT" transports="http https local">
   <config enableOData="false" id="OracleDB">
      <property name="driverClassName">oracle.jdbc.driver.OracleDriver</property>
      <property name="url">jdbc:oracle:thin:@localhost:1521/xe</property>
      <property name="username">system</property>
      <property name="password">oracle</property>
   </config>
   <query id="SelectData" useConfig="OracleDB">
      <sql>Select id,home_addr From Students</sql>
      <result element="Entries" rowName="Entry">
         <element column="id" name="id" xsdType="integer"/>
         <element column="home_addr[0]" name="Home_Number" xsdType="integer"/>
         <element column="home_addr[1] " name="Home_City" xsdType="string"/>
      </result>
   </query>
   <query id="InsertData" returnUpdatedRowCount="true" useConfig="OracleDB">
      <sql>Insert into Students (id, home_addr) values (?, T_Address(?,?))</sql>
      <result element="UpdatedRowCount" rowName="" useColumnNumbers="true">
         <element column="1" name="Value" xsdType="integer"/>
      </result>
      <param name="student_id" sqlType="INTEGER"/>
      <param name="home_number" sqlType="INTEGER"/>
      <param name="home_city" sqlType="STRING"/>
   </query>
   <operation name="SelectOp">
      <call-query href="SelectData"/>
   </operation>
   <operation name="InsertOp">
      <call-query href="InsertData">
         <with-param name="student_id" query-param="student_id"/>
         <with-param name="home_number" query-param="home_number"/>
         <with-param name="home_city" query-param="home_city"/>
      </call-query>
   </operation>
</data>

Responses:

For InsertOp:

Request:

<body>
   <p:InsertOp xmlns:p="http://ws.wso2.org/dataservice">
      <!--Exactly 1 occurrence-->
      <p:student_id>50</p:student_id>
      <!--Exactly 1 occurrence-->
      <p:home_number>11</p:home_number>
      <!--Exactly 1 occurrence-->
      <p:home_city>London</p:home_city>
   </p:InsertOp>
</body>

Response:

<UpdatedRowCount xmlns="http://ws.wso2.org/dataservice">
   <Value>1</Value>
</UpdatedRowCount>

For SelectOp:

Response:

<Entries xmlns="http://ws.wso2.org/dataservice">
   <Entry>
      <id>1</id>
      <Home_Number>10</Home_Number>
      <Home_City>Colombo</Home_City>
   </Entry>
   <Entry>
      <id>2</id>
      <Home_Number>30</Home_Number>
      <Home_City>New York</Home_City>
   </Entry>
   <Entry>
      <id>50</id>
      <Home_Number>11</Home_Number>
      <Home_City>London</Home_City>
   </Entry>
</Entries>

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>