Query UDTs(User Defined Types) with WSO2 Data Service Server

In this brief tutorial I ll be guiding you through the process of developing a simple dataservice which is capable of retrieving an Oracle UDT (User Defined Type) from a database using WSO2 Data Services Server.

First, login to your preferred Oracle database via your favourite SQL client tool or the sqlplus command line utility and run the following script. This will create necessary UDT structures, a table to store the UDT type as well as some sample data which will later be queried via dataservice.

CREATE OR REPLACE TYPE address_t AS OBJECT(num NUMBER, street VARCHAR2(100), city VARCHAR2(100), STATE VARCHAR2(100), country VARCHAR2(100));
/
CREATE OR REPLACE TYPE customer_t AS OBJECT(id NUMBER, name VARCHAR2(50));
/
CREATE TABLE customer_tbl (customer_id NUMBER, customer_name VARCHAR2(100), customer_address ADDRESS_T);
/
INSERT INTO customer_tbl
VALUES (1,
        'john',
        address_t(25, 'flower road', 'Brooklyn', 'Western London', 'United Kingdom'));

INSERT INTO customer_tbl
VALUES (2,
        'peter',
        address_t(25, 'flower road', 'El Camino Real', 'Palo Alto', 'California'));

CREATE OR REPLACE PROCEDURE getCustomer(cust OUT customer_t) IS BEGIN cust := customer_t(1, 'prabath'); END;
/

Next, download the latest version of WSO2 Data Service Pack from here and extract it to a proper location in your file system. Let's call it DSS_HOME. Then copy the Oracle JDBC jar downloaded from here to DSS_HOME/repository/components/lib directory.

Now we're done with preparing the surroundings for creating the dataservice.

Let's now start the WSO2 Data Service Server and start building up the dataservice. (You can find more detailed information about developing a simple dataservice from here.). Depicted below is a sample dataservice descriptor file (.dbs) that carries a dataservice queries for retrieving and inserting UDT values in customer_address column of the customer_tbl table.

<?xml version="1.0" encoding="UTF-8"?>
<data name="UDTSample">
   <config id="default">
      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:@localhost:1521:XE</property>
      <property name="org.wso2.ws.dataservice.user">djpro</property>
      <property name="org.wso2.ws.dataservice.password">admin</property>
   </config>
   <query id="q1" useConfig="default">
      <sql>SELECT customer_id, customer_name, customer_address FROM customer_tbl</sql>
      <result element="Entries" rowName="Entry">
         <element name="ID" column="customer_id" xsdType="xs:integer" />
         <element name="Name" column="customer_name" xsdType="xs:string" />
         <element name="Number" column="customer_address[0]" xsdType="xs:integer" />
         <element name="Street" column="customer_address[1]" xsdType="xs:string" />
         <element name="City" column="customer_address[2]" xsdType="xs:string" />
         <element name="State" column="customer_address[3]" xsdType="xs:string" />
         <element name="Country" column="customer_address[4]" xsdType="xs:string" />
      </result>
   </query>
   <query id="q2" useConfig="default">
      <sql>INSERT INTO customer_tbl VALUES(?,?,address_t(?,?,?,?,?))</sql>
      <param name="ID" sqlType="INTEGER" />
      <param name="Name" sqlType="STRING" />
      <param name="Number" sqlType="INTEGER" />
      <param name="Street" sqlType="STRING" />
      <param name="City" sqlType="STRING" />
      <param name="State" sqlType="STRING" structType="null" />
      <param name="Country" sqlType="STRING" />
   </query>
   <query id="q3" useConfig="default">
      <sql>call getCustomer(?)</sql>
      <result element="customers" rowName="customer">
         <element name="id" column="cust[0]" xsdType="xs:integer" />
         <element name="name" column="cust[1]" xsdType="xs:string" />
      </result>
      <param name="cust" sqlType="STRUCT" type="OUT" structType="CUSTOMER_T" />
   </query>
   <operation name="op1">
      <call-query href="q1" />
   </operation>
   <operation name="op2">
      <call-query href="q2">
         <with-param name="ID" query-param="ID" />
         <with-param name="Name" query-param="Name" />
         <with-param name="Number" query-param="Number" />
         <with-param name="Street" query-param="Street" />
         <with-param name="City" query-param="City" />
         <with-param name="State" query-param="State" />
         <with-param name="Country" query-param="Country" />
      </call-query>
   </operation>
   <operation name="op3">
      <call-query href="q3" />
   </operation>
</data>

NOTE: 

If you carefully look at the input mappings defined for the dataservice query "q1", the UDT attributes that are being retrieved are specified in the format of "database_column_name[UDT_attribute_index]"

Once you deploy the "UDTSample" dataservice in the WSO2 Data Services Server it will be displayed under the service list. 

You can then click on the Tryit client functionality using which you will be able to test the data service operations that manipulate the aforementioned UDT structures. 

I trust this simple tutorial helps you understand the basics of manipulating UDTs with WSO2 Data Services Server. Further, I'm hoping to come up with more complex samples explaining scenarios such as how to retrieve UDTs as OUT parameters of stored procedures, how to query SQL Arrays via stored procedures/ordinary SQL queries/Ref cursors, etc soon.