Data Services

Content

Introduction

Data Services is a convenient mechanism to provide a Web service interface for data stored in some data sources. Data sources such as relational databases, CSV files and Microsoft Excel files can be easily service enabled using Data Services. Now, the data can be exposed and accessed in a secure(using WS-Security) and reliable(using WS-ReliableMessaging) manner, and is also available for mashing-up with other Web services.

Creating a sample database and populate data

This guide assumes MySQL is configured in your system. If not, please download MySQL 5 or later version from here.

• From the command prompt, run MySQL_HOME/bin/mysqld.bat{sh} to start MySQL server
• Lets create a simple data base with one table. Open a command prompt and type 'mysql -u root -p' to access mySQL prompt. If you installed mySQL default configurations, you may enter blank password and access mySQL prompt. Enter the following commands to create a sample database, create a table and populate sample data.

  mysql>create database employeedb;

  mysql>use employeedb;

  mysql>create table employee(id VARCHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(100), address VARCHAR(100));

  mysql> insert into employee values('01','john','Boston');

  mysql> insert into employee values('02','Micheal','Dallas');

  mysql> insert into employee values('03','richard','Chicago');

  mysql> exit;

Since we are using a MySQL database for our demonstration, we should copy mySQL JDBC driver to WSAS_HOME/lib/extensions directory (e.g:- cp mysql-connector-java-5.0.3-bin.jar /home/user/wso2wsas/wso2wsas-3.1.0/lib/extensions).

After copying the necessary jdbc driver, make sure to restart the server.

Creating your first data service

We are ready to create our first data service. We will make use of the sample MySQL 'employeedb' database we have created in the previous step.

• Select 'Add' from the left navigation menu of Solution management console. Data Service Step-1 will be displayed.
• Provide a name for the data service (e.g:- EmployeeDataService)
• Select 'RDBMS' from the 'Data Source' drop down. 'Data Source Type: RDBMS' popup window will be displayed

Enter the following values in 'Data Source Type: RDBMS' popup window.

DataBase Type = MySQL

Driver Class = com.mysql.jdbc.Driver

JDBC URL = jdbc:mysql://localhost:3306/employeedb

User name = root

• Click on 'Test Connection' button. You should get 'Database connection is successfull with driver class com.mysql.jdbc.Driver , jdbc url jdbc:mysql://localhost:3306/employeedb and user name root' message if the database connection is successful.
• Click 'OK' button. You will see a preview of data service configuration as follows.

• Next, click on 'Next' button at the Data Service Step-1. Data Service Step-2 will be displayed. Initilally your service does not include any queries.
• Click on 'New Query' to add a new query to our service. 'Add New Query' pop up window will be displayed. Enter the following values there.

Query ID = DataQueryEmp

SQL Statement = select id, name, address from employee

Grouped by Element = employees

Row name = employee

Row namespace = http://test.org

• Now click on 'Add New Output Mapping' button in 'Add New Query' window. 'Add New Output Mapping' window will be popped up. Add three output mappings as follows

1. Mapping Type = Element output field name = id

   SQL Column Name = id

   Schema Type = xs:string

   Allowed User Roles = "admin"

2. Mapping Type = Element

   output field name = name

   SQL Column Name = name

   Schema Type = xs:string

   Allowed User Roles = "admin"

3. Mapping Type = Attribute

   output field name = address

   SQL Column Name = address

   Schema Type = xs:string

   Allowed User Roles = "admin"

We have added one query to our data service configuration. In this example, we do not use an SQL statement which accepts input parameters. Therefore, we can ignore 'Input Mappings'.

• Click on 'Next' button at the Data Service Step-2. You will be directed to the Data Service Step-3 where you can add new operations to the data service configuration.
• Click on 'Add New Operation' button.

You will notice that the query we have created in the previous step will be shown in 'Query' dropdown of the 'Add New Operation' popup window. Provide a name for the operation (e.g:- getAllEmployees) and click on 'OK'.

• Step 3 of the data service creation wizard will be displayed. You may go through the preview of data service configuration at this step. After verifying the configuration, click on 'Finish' to deploy the service

Your new data service will be listed in 'Deployed Services' page as follows.

Invoking Data Service

In this section, we'll look at two different ways to invoke our data service without writing single line of code. First we will invoke our data service using 'Tryit' utility which integrated in to Solution Management console. Then we invoke it using a simple HTTP GET request.

• Click on 'EmployeeDataService' in 'Deployed Services' page. You will be directed to 'Service Management' page as follows.

• Select 'Try This Service' link. Following page will be displayed.

Click on 'getAllEmployees' button to invoke our data service. You will get the response message in the same page. Note that employee id and name will be returned as xml elements and address will be wrapped as an attribute. This is due to our output mapping configuration at step 2 of the wizard. We have configured id and name as element mapping types and address as attribute mapping type. You can edit them and observe the associated changes in response very easily using Tryit.

• Now, we will invoke the same data service using our second approach, HTTP GET request. Copy and paste the dollowing URL in your browser and hit enter.

http://10.100.1.150:9763/services/EmployeeDataService/getAllEmployees

You will get the same result back as in 'Tryit' approach

For more details on the Data services see User Guide