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

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>