MyCodeIdeas: July 2017

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>