Thursday, November 11, 2010

Creating a simple data service using WSO2 Data Services Server

Here I am going to illustrate, how to use WSO2 Data Services Server for exposing data in a mysql database as a service.

Prerequisites

  • WSO2 Data Services Server - download it from here 

  • MySQL 5 or later version - download it from here

  • JDK 1.5 or Higher

Let's create a database with a single table. Below I have given a mysql script for creating database with some data.


--
-- Create schema AccountDB
--

CREATE DATABASE IF NOT EXISTS AccountDB;
USE AccountDB;

--
-- Definition of table `AccountDB`.`account`
-- 

CREATE TABLE  `AccountDB`.`account` (
  `account_no` int(10) unsigned NOT NULL,
  `name` varchar(45) NOT NULL,
  `balance` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `AccountDB`.`account`
--

INSERT INTO `AccountDB`.`account` VALUES  (1,'Michael',2000),
 (2,'David',3000),
 (3,'Kasun',1500),
 (4,'Mike',1500),
 (5,'Lucas',1500),
 (6,'John',1500),
 (7,'Robin',1500),
 (8,'Daniel',1500),
 (9,'Paul',1500),
 (10,'Oliver',1500);

Here I have given the data services configuration file that I used.  You can refer this  documentation for creating the data service using data services server user interface, or you can write the dbs configuration file and upload it to data services server. Since we are using a MySQL database, you should copy mySQL JDBC driver to DSS_HOME/repository/components/lib


Here I have given my data services configuration file

<data name="AccountDataService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">  
   <config id="Account">     
      <property name="org.wso2.ws.dataservice.driver">

          com.mysql.jdbc.Driver
     </property>     
      <property name="org.wso2.ws.dataservice.protocol">

          jdbc:mysql://localhost:3306/AccountDB
     </property>   
      <!-- use your own mysql database username -->      
      <property name="org.wso2.ws.dataservice.user">root</property>    

      <!-- use your own mysql database password -->     
      <property name="org.wso2.ws.dataservice.password">password</property>      </config>  
   <query id="AccountDetails" useConfig="Account">     
      <sql>select account_no, name, balance from account </sql>     
      <result element="account_details" rowName="account"> 

         <!-- mapping database columns to output field-->       
         <element name="accountNo" column="account_no" xsdType="xs:integer" />        
         <element name="name" column="name" xsdType="xs:string" />        
         <element name="balance" column="balance" xsdType="xs:integer" />     
      </result>  
   </query>  
   <operation name="getAccountDetails">     
      <description></description>     
      <call-query href="AccountDetails" />  
   </operation>
</data> 


After uploading the dbs file to the Data Services Server, Now you can see the Newly added AccountDataService in the services page. (Go to Manage->Services->List). Then you can try the service using "Try it".




 

2 comments:

Anonymous said...

That is the easiest case. What will happens ifthe data returned by the SQL query have some columns set to null. I gat a Null pointerException. Would be interested if you can explore that case as well.

Anonymous said...

Hi Kasun's,

I have one Query
Here i am sending the my GIT URL which contain my Query

https://gist.github.com/anonymous/5300686

Could you please help me.
Thanks in Advance
Anil