MyCodeIdeas: 2017

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>

Sunday, March 5, 2017

WSO2 DSS - Exposing Excel Data in Non Query Mode

If query mode is disabled for the spreadsheet, you cannot use SQL statements to query data in the excel sheet. Note that in non-query mode, you can only get data from the sheet and you cannot insert, update or modify any data.

The below sample use DSS 3.5.1 with DSSTest.xls excel file. Download the DSSTest.xls from [1] and update the file system location in the URL field.

Data Service

<data name="ExcelTestService" transports="http https local">
   <config enableOData="false" id="ExcelDS">
      <property name="excel_datasource">/home/anupama/DSSTest.xls</property>
   </config>
   <query id="SelectData" useConfig="ExcelDS">
      <excel>
         <workbookname>Alerts</workbookname>
         <hasheader>true</hasheader>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
         <headerrow>1</headerrow>
      </excel>
      <result element="AlertDetails" rowName="Alert">
         <element column="AlertID" name="Alert_ID" xsdType="string"/>
         <element column="Owner" name="OwnerName" xsdType="string"/>
         <element column="AlertType" name="Alert_Type" xsdType="string"/>
      </result>
   </query>
   <operation name="getdata">
      <call-query href="SelectData"/>
   </operation>
</data>

Request

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

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

Response

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <AlertDetails xmlns="http://ws.wso2.org/dataservice">
         <Alert>
            <Alert_ID>1</Alert_ID>
            <OwnerName>Peter</OwnerName>
            <Alert_Type>3</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>2</Alert_ID>
            <OwnerName>James</OwnerName>
            <Alert_Type>4</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>3</Alert_ID>
            <OwnerName>Anne</OwnerName>
            <Alert_Type>1</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>4</Alert_ID>
            <OwnerName>Jane</OwnerName>
            <Alert_Type>11</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>30</Alert_ID>
            <OwnerName>Smith</OwnerName>
            <Alert_Type>1</Alert_Type>
         </Alert>
      </AlertDetails>
   </soapenv:Body>
</soapenv:Envelope>

References:

[1] https://github.com/anupama-pathirage/DemoFiles/raw/master/Blog/Excel/DSSTest.xls

Saturday, March 4, 2017

WSO2 DSS - Exposing Excel Data in Query Mode

In Query mode you can query data in the spreadsheet using SQL statements. The query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. The org.wso2.carbon.dataservices.sql.driver.TDriver class is used internally as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google spreadsheets, Excel sheets etc. Internally it use the Apache POI - the Java API for Microsoft Documents to read and modify documents [1].

The below sample use DSS 3.5.1 with DSSTest.xls excel file. Download the DSSTest.xls from [2] and update the file system location in the URL field.

Data Service

<data name="ExcelTest" transports="http https local">
   <config enableOData="false" id="ExcelDS">
      <property name="driverClassName">org.wso2.carbon.dataservices.sql.driver.TDriver</property>
      <property name="url">jdbc:wso2:excel:filePath=/home/Anupama/DSSTest.xls</property>
   </config>
   <query id="QueryData" useConfig="ExcelDS">
      <sql>Select AlertID, Owner, AlertType from Alerts where AlertType &gt; 3</sql>
      <result element="Entries" rowName="Entry">
         <element column="AlertID" name="AlertID" xsdType="string"/>
         <element column="Owner" name="Owner" xsdType="string"/>
         <element column="AlertType" name="AlertType" xsdType="string"/>
      </result>
   </query>
   <query id="InsertData" useConfig="ExcelDS">
      <sql>Insert into Alerts(AlertID, Owner, AlertType) values (?,?,?)</sql>
      <param name="ID" sqlType="INTEGER"/>
      <param name="Owner" sqlType="STRING"/>
      <param name="Type" sqlType="INTEGER"/>
   </query>
   <operation name="GetData">
      <call-query href="QueryData"/>
   </operation>
   <operation name="InsertData" returnRequestStatus="true">
      <call-query href="InsertData">
         <with-param name="ID" query-param="ID"/>
         <with-param name="Owner" query-param="Owner"/>
         <with-param name="Type" query-param="Type"/>
      </call-query>
   </operation>
</data>

Request and Response

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

For Get Data

Request :

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

Response

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <Entries xmlns="http://ws.wso2.org/dataservice">
         <Entry>
            <AlertID>2.0</AlertID>
            <Owner>James</Owner>
            <AlertType>4.0</AlertType>
         </Entry>
         <Entry>
            <AlertID>4.0</AlertID>
            <Owner>Jane</Owner>
            <AlertType>11.0</AlertType>
         </Entry>
      </Entries>
   </soapenv:Body>
</soapenv:Envelope>

For Insert Data:

Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://ws.wso2.org/dataservice">
   <soapenv:Header/>
   <soapenv:Body>
      <dat:InsertData>
         <dat:ID>30</dat:ID>
         <dat:Owner>Smith</dat:Owner>
         <dat:Type>1</dat:Type>
      </dat:InsertData>
   </soapenv:Body>
</soapenv:Envelope>

Response :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <axis2ns3:REQUEST_STATUS xmlns:axis2ns3="http://ws.wso2.org/dataservice">SUCCESSFUL</axis2ns3:REQUEST_STATUS>
   </soapenv:Body>
</soapenv:Envelope>

References :

[1] https://poi.apache.org/spreadsheet/index.html
[2] https://github.com/anupama-pathirage/DemoFiles/raw/master/Blog/Excel/DSSTest.xls

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>