WSO2 Data As a Service - Have your own data storage in cloud!

WSO2 StratosLive, the most complete open PaaS (platform-as-a-service), includes multiple types of SOA middleware services which can be used to build and host your SOA based solution. One of the newest additions to WSO2 middleware platform is, the functionality which enables you to have your own database in cloud for free!

This post guides you through the steps of creating your own data storage in cloud using WSO2 StratosLive.

Step 1

First, we need to create an account in stratoslive. You can have a demo account free of charge.

Open a browser and access WSO2 StratosLive landing page. Click on Get Started Now for Free

You will be directed to domain registration page.

Fill the form and follow the instructions in registration email to activate the account.

Once you logged into WSO2 StratosLive, you will be shown all of the cloud services which are available for use. Out of those services, click on Data Services Server link.







You will be directed to the home page of WSO2 Data Service Server.



Step 2

Now, we have accessed the home page WSO2 Data service server in cloud. You will notice Databases menu item in the left navigation pane as shown below.







Click on Databases --> Add option in the above screen. You will be directed to the New Database page. In this page, you will be asked to provide Database Server Instance Name and a name for the database which you are going to add. By default, WSO2 Data Services Server provides you with a pre-configured Amazon RDS instance, in which you can create your database. You also add your own database server instance and use that instead as you preferred. But in this example, we will use the default database server instance. Lets select the default DB server instance and enter "qa" as the name of the database.

Once you click on create, you will be prompted a confirmation dialog, "Database has been successfully created".



Your newly created database will be listed in database list as shown below. Note that, the name of the database is suffixed with tenant domain name.







Step 3



We have created our first database successfully in cloud using WSO2 Data Service Server. Lets, add a new user to the database and create table so that we can use the database for any DB related operation.



Click on Manage icon in the above table. Database User Management page will be shown.







Before adding any user, you should add a database user privilege group. Click on Add Privilege Group and add a new privilege group. Specify select, insert, update, delete and create privileges for the group as shown below.







The newly created privilege group will be shown in Database User Privilege Groups page as follows.







Now, go back to the previous page and click on add new database users icon to access Database Users screen where you can add DB users. Click on add new user.

Specify "test" as username and password and select "group1" as the privilege group.







Step 4



Once you create a new DB user, it will be listed in Database Users page with the options to explore database using the given user credentials, edit, drop and create carbon datasource.

Click on explore database. Database console screen will be displayed as follows. This console allows you to issue any SQL statement and act as an SQL editor for your database.

You may type any SQL statement and hit run button so that the query will be executed on the database which has been created in the default amazon RDS instance provided by WSO2.







Now, you can use this DB as your storage media in your SOA platform in cloud. For example, you may deploy a webapp in WSO2 Stratos AppServer which talks to this particular DB. Or you may simply create a data service using this database.

How to call Oracle Stored Procedures from data services

An Oracle stored procedure is a program stored in an Oracle database which allows business logic to be embedded inside database as an API. You can expose the stored procedures as web services using wso2 data services server. This example takes you through creating a simple stored procedure in an Oracle DB and expose it as a data service using WSO2 Data Services Server.

Pre-requisites:

Download wso2 data services server from here

Oracle 10g or later

Step 1: Create and Populate sample database

First, open sqlplus shell and log in to DB as follows.

sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;

Enter password:

Connected.

We should create a user and grant him the necessary privileges as follows.

SQL> create user sample identified by sample account unlock;

User created.

SQL> grant connect to sample;

Grant succeeded.

SQL> grant create session, dba to sample;

Grant succeeded.

SQL> exit;

Now logged in as the new schema user as follows.

sqlplus sample/sample@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 23 22:52:01 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba;

Enter password:

Connected.

SQL>

Create a table and insert data as follows.

SQL> create table employee (id number primary key, name varchar(100), address varchar(100));

SQL> insert into employee(id, name, address) values (1, 'charitha', 'colombo');

SQL> insert into employee(id, name, address) values (2, 'john', 'galle');

SQL> insert into employee(id, name, address) values (3, 'michel', 'otawa');

SQL> insert into employee(id, name, address) values (4, 'carl', 'dallas');

SQL> insert into employee(id, name, address) values (5, 'chanmira', 'colombo');

SQL> commit;

Now we are ready with oracle database and schema.

Lets write a simple stored procedure to insert more data to employee table.

Step 2: Writing a simple stored procedure

SQL> create or replace procedure addEmployeeSP(id number, name varchar, address varchar) is

2 begin

3 insert into employee (id, name, address) values (id, name, address);

4 end;

5 /

Step 3: Copy oracle jdbc driver

In order for wso2 data services server to communicate to oracle DB, we should download oracle jdbc driver (ojdbc14.jar) and copy in to DS_HOME/repository/components/lib directory.

Step 4: Create the data service through UI wizard

First, start wso2 data services server by running wso2server.sh {bat} which can be found at DS_HOME/bin

Then access management console through https://localhost:9443/carbon and log in using default admin credentials (admin/admin)

Click on Data Service --> Create in the left menu which will bring up data service creation wizard.

Enter a name for the data service and click on next. (For this example, I have specified "BlogExampleDataService" as the service name)

In Data Sources screen, click on Add new data source link and specify a data source ID and select RDBMS as the data source type. Then select Oracle as the Database engine and enter the db info as follows.

Driver Class = oracle.jdbc.driver.OracleDriver
JDBC URL = jdbc:oracle:thin:sample/sample@10.100.1.10:1521/orcl
User Name = sample
Password = sample



Click on "Test Connection" to see whether you can connect to oracle server correctly. Then click on save. Now we have created a data source hence we can proceed through the wizard.

Click on Next to move to query definition page. Select Add New Query to create a new query for our data service.

Enter a query ID (ex:- employees) and select the data source we just created from the Data Source drop down.

We can specify our SQL statement in the SQL text area. We need to call the oracle stored procedure which has been created earlier. You can enter "call addEmployeeSP(?,?,?)" as the query to call oracle stored procedure. ? denotes the input parameters which should be passed to the stored procedure.



Next, click on Add new input mapping and add three new input parameters since our query accepts 3 different parameters.



After adding all 3 input parameters, the query will be looked as follows.



We can save the query now. Our stored procedure inserts employee records in to the table therefore it does not return anything.
Because of that, the output mappings are not required for the query.

In order to check whether the records are correctly added to the database, lets create another query, selectEmployees as shown in the following screen.



Now, we have created both queries. Lets add operations which are necessary to run these queries.

Click Next in the Queries screen to proceed through the wizard which will bring up Operations page. Click on Add New Operation link. Specify operation name, addEmployee and select Query ID, employees.



Similarly, add an operation for selectEmployee query and name it as selectAllEmployees.

Click on Finish to deploy the data service. Once it is deployed, the service will be shown in the service list. Click on Try this service link to test the service.
In there, you will find two operations, addEmployee and selectAllEmployees. First invoke addEmployee operation by specifying id, name and address.
id=6
name=bloguser
address=notown

Now, if you invoke selectAllEmployees operation, you will see that the employee table has been updated with a new record.



Thats all! Drop me a mail or post your question at wso2.org forum if you have any questions about WSO2 Data Services Server.

Input validation of data services

Input validation is a new feature included in the latest version of WSO2 Data Services Server (WSO2 DSS). With this, the further processing of a data service request message can be stopped at the service layer without reaching the backend data source based on a pre-defined input validation logic.

This is achieved either using a set of built in validators or custom validator implemented by the service author.

There are four different built-in validators.

1. Long range validator

This can be used to validate an integer input parameter value as follows.

<param name="id" paramType="SCALAR" sqlType="INTEGER" type="IN" ordinal="1">

<validateLongRange minimum="1" maximum="40" />

</param>

2. Double range validator

This is useful when a validity of a float value has to be checked.

<param name="distance" paramType="SCALAR" sqlType="DOUBLE" type="IN" ordinal="2">

<validateDoubleRange minimum="1.5" maximum="850.45" />

</param>

3. Length validator

This can be used to check whether the input parameter value conform to the speficied string length.

<param name="name" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="3">

<validateLength minimum="2" maximum="20" />

</param>

4. Pattern validator

This validates the string value of the input parameter against a given regular expression.

<param name="indexno" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="4">

<validatePattern pattern="(?:[a-z0-9]" />

</param>

Finally, you can write your own validator based on your requirements and use it in the data service query definition. In order to do that, you must implement org.wso2.carbon.dataservices.core.validation.Validator interface in your custom validator class as follows.

import org.wso2.carbon.dataservices.core.validation.Validator;

import org.wso2.carbon.dataservices.core.validation.ValidationContext;

import org.wso2.carbon.dataservices.core.validation.ValidationException;

import org.wso2.carbon.dataservices.core.engine.ParamValue;

public class MyCustomValidator implements Validator {

public void validate(ValidationContext validationContext, String s, ParamValue paramValue)

throws ValidationException {

if (!paramValue.getScalarValue().startsWith("2")) {

throw new ValidationException("Not starting with 2!!",s,paramValue);

}

}

}

Then, you can build a jar with the custom validator class and place it in CARBON_HOME/repository/components/lib directory. After restarting the server, you can use it inside query definition as follows.

<param name="id" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1">

<validateCustom class="org.test.MyCustomValidator" />

</param>

WSO2 Data Services plugin for Eclipse WTP

WSO2 Data Services 1.0 provides you with a IDE plugin which can be integrated to Eclipse WTP. You can create and deploy data services to a remote or local data services server from IDE. This post demonstrates how a simple MySQL data service is created and deployed using Eclipse WTP.

Pre-requisites

Download and install WSO2 Data Services 1.0
Download and install Eclipse WTP

We will create a data service using MySQL. Please follow the instructions provided in WSO2 Data Services Quick Start Guide to create a data base and populate data.

Step 1

Download wso2 data services eclipse plugin (org.wso2.ws.dataservices.ide_1.0.0) from here and copy it to Eclipse_home/plugins directory.

Start Eclipse and select a new workspace. Select Workbench to go to the work bench.

Step 2
Open the new wizard selection window (File --> New -- Other). If you have copied org.wso2.ws.dataservices.ide_1.0.0.jar to the eclipse plugins directory correctly, you will see WSO2 Data service wizard option in there.



Select wso2 dataservice creation wizard and click on Next. The initial configuration screen of the Data Service Creation wizard will be displayed as follows.



Select Try it after deploying check box.
We are going to create a data service through IDE wizard and deploy it to a wso2 dataservice server running in localhost. (You can deploy data services to a remote server as well).
Therefore, you need to provide truststore and administrator credentials in that server.

Click on Set truststore. Browse for wso2carbon.jks which is located at your WSO2DS_Home/conf directory. (WSO2DS_HOME is the location where you unzipped wso2 dataservice 1.0 binary distribution)
You will be prompted to enter password for the trust store. Enter wso2carbon and click ok.

Click on Set Credentials to enter username and password of the target data service server.
Enter admin as the user name and password.

Click on Next to proceed through the wizard.

Step 3

Now you are at the step one of data service configuration wizard.
Give a name for the service (i.e:-IDEDataService)

Click on Add button to add a new data source. Since we use MySQL data source, select RDBMS as the data source type and click ok.

You will get a message saying "Currently typed database driver class does not reside in the class path. Please provide correct path to the jar file". Click ok.

Now you will be directed to the mySQL database configuration window.



Enter the following values for MySQL database configuration.

Driver Class = com.mysql.jdbc.Driver
JDBC URL = jdbc:mysql://localhost:3306/employeedb
Enter the user name and password of your MySQL db.

Enter jar path to driver class (mysql-connector-java-*-bin.jar). Then click on Test Connection button. You should get "Test Connection Successful" message.
Click "OK" to finish the database configuration.

Click "Next" in the WSO2 Data service wizard Step one to move to the next step.

Step 4

Now you should be in the step 2 of the wizard. Click on Add New Query. Following window will be displayed.



Select default data source. Enter the follwoing values for the rest of the fields.

Query ID = allEmployees

SQL Statement = select * from employee

Grouped by Element = employees

Row name = employee

Row namespace = http://test.org

Now click on 'New' button under 'Result (Output mapping)' 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

2. Mapping Type = Element

   output field name = name

   SQL Column Name = name

3. Mapping Type = Attribute

   output field name = address

   SQL Column Name = address

We have added one query to our data service configuration.

Click on 'OK' button at the "Query Configuration" window. You will be directed back to the step two of data service wizard and your new query will be listed there.

Click Next button to continue. Step three of the data service configuration wizard will be displayed.

Step 5

Click on 'Add New Operation' button. Enter a name for the operation (i.e: getAll) and leave the default settings and click ok.

By now, we have completed all data service cinfiguration steps. Click Finish to deploy the service.

(Make sure wso2 data service server instance is running in your host before finishing the IDE wizard. You can start it by running wso2server.bat located at WSO2DS_HOME/bin directory)

If all are correct you will get "Data Service deployed successfully" message. Also, a browser will be popped up withTryit page where you can easily test the service.

Throttling request messages with WSO2 Data Services

WSO2 Data services 1.0 has been released now. This free and open source product consists of a lot of useful features which can be used to implement your SOA based systems with minimum effort.
Access throttling can be considered as a new and vital feature included in it.
In web services interactions, we usually have requirements to restrict number of requests our web services serve for a given client. Also, we need to allow service access to a specified domain or IP range.
WSO2 Data Services provides an easily configurable UI based utility to throttle messages targeted to a particular data service or a set of services.
This post will take you through a basic throttling configuration.

Pre-requisites
Download and install WSO2 Data Services 1.0

Step 1
Start server by running WSO2DS_HOME/bin/wso2server.bat{sh}
Access wso2 data service management console (https://localhost:9443/ds)
Log in to console with default admin credentials (admin/admin)
Select Data Services link in the left navigation menu. You will be directed to the Deployed Services page.
Click on the default service, CustomerService
You will see the following page.



Step 2

Click on Throttling link which is placed under Quality of Service in the above page.
Throttling Configuration for CustomerService Service page will be displayed.
Select "Yes" from Enable Throttling drop down.
Default throttling configuration will be displayed as follows.



In the above table, Range denotes the IP addresses and domains to be restricted.
Remove the second row of configuration table.
In this demonstration we will restrict accessing the default CustomerService to the requests originating from local host. Therefore you can leave "other" as the range "IP" as type.

There are three access levels.
Allow - All requests are allowed without applying any restriction for the specified range
Deny - Access is denied for all requests comes from the given range
Control - Restrictions are applied based on the given constraints (Max Request Count, unit time and prohibit time period)

In this demonstration, we are going to use "Control" access level. Hence, Change the Access level to "Control". Max Request Count, Unit time and prohibit time period text boxes will be enabled.

Enter 10 as Max Request Count. 100000 as Unit time and 20000 as prohibit time period.
This means, our sample data service (CustomerService) accepts only 10 requests from localhost during 100 seconds (100000ms) . If all 10 requests hits the server before unit time expires (before 100 seconds), no more requests are allowed in to the service within 20 seconds (20000ms) as specified in prohibit time period.

A more detailed explanation on these terms can be found in the inline help of WSO2 Data services management console.

Click on Finish button to save the throttle configuration.


Step 3

Our throttling configuration is over by now. Lets invoke the sample service 10 times and see how throttling works.
Go back to the service management page of the CustomerService and click on Try this Service link.
Select "customersInBoston" operation and click on "customersinBoston" button. Hit this button 10 times to send 10 requests to CustomerService.
Send one more request. You will receive a fault similar to the below.

Fault: Access deny for a caller with IP 127.0.0.1 : Reason : You cannot access this service since you have exceeded the allocated quota.

Getting started with WSO2 Data Services

As I mentioned in a previous post, WSO2 Data Services is now available for you to download and try out. WSO2 Data Services is an open source product and it is available under apache license.

The objective of this post is to get you start with WSO2 Data Services with minimum configuration steps. You can find more details of WSO2 Data Services solution from wso2.org online documentation

Step 1
Download and install wso2 data service solution. You may download the latest beta2 version from here. Just extract wso2-dataservices-1.0-xx.zip in to a directory in your file system.
We refer this directory as WSO2DS_HOME.

Step 2
Start wso2 data services solution. Go to WSO2DS_HOME/bin and run wso2server.bat{sh}

Step 3
WSO2 Data services provides a web service interface for data stored in relational data bases, csv or Microsoft Excel files. Since our intention is to get our first data service running as quicker as possible I will use a simple csv file as the data source.
Open a text editor and create a csv file as follows.

id,name,address
01,charitha,colombo
02,Michel,Dallas
03,John,Westborough

Save this file as employee.csv.

Step 4
Access WSO2 Data Services management console. Open a browser and go to http://localhost:9443/ds
Click on Sign-in link at the top right corner. Enter username = admin and password = admin as login credentials.
You will be directed to the home page of Data Services management console.



Step 5

Click on Add from the left navigation menu. The step one of data service creation wizard will be shown.
Enter csvservice as the Service name and click on Next.

Step 6

Select csv as the data source type and click on Add.


Step 7

In CSV Data source configuration window, enter the following values.
Id = 1
CSV File location = D:\temp\employee.csv
Column Seperator = ,
Starting Row = 2
Max. no of rows to read = -1
Headers Available = TRUE
Column Selection (You can click on the column names specified in the drop down. Then it will add column numbers according to the sequence of selection)
id as column no:1
name as column no:2
address as column no:3



Step 8

Click "Next" from the data service wizard step 2. You will be directed to the step 3 where you can create queries for the service.
Click on "New Query" button.
Add New Query window will be popped up. Enter the following values.

QueryID = q1
DataSource = 1
Grouped by element = emps
Rowname = emp

Click on "Add New output mapping" button. In "Add new Output mapping" window, enter the followings. (Repeat this with name and address columns as well)
Mapping Type = Element
Output field name = id
CSV Column ordinal = 1



Step 9

If query is configured as in the previous step, you should notice the following elements in the "Preview of Data service configuration" section.
<query id="q1" useConfig="1">

<result element="emps" rowName="emp">

<element name="id" column="1" />

<element name="name" column="2" />

<element name="address" column="3" />

</result>

</query>

Click on next button of the Data Service Step 3 to continue with the wizard. Step 4 will be displayed where the service operations are configured.

Click on Add New Operation button.
"Add New Operation" pop-up window will be displayed. Enter the following values.
Operation name = op1
Query = q1
Click OK

Now all the data service configuration steps are done. Click on Finsh button to deploy our CSV data service.

Step 10
csvservice will be added to the deployed services list in Deployed Services page.
Lets invoke our data service using the simplest service invocation mechanism and see we get the data stored in csv file as the output.

Click on csvservice. Service Management page will be displayed.
Select Try this service link.
Click on op1 button and you will get the results as follows.

Data Services-1.0Beta - The latest member of WSO2 SOA middleware suite

We have been working hard over the last few weeks to launch the newset addition to the WSO2's SOA middleware platform, WSO2 Data Services -1.0beta. Now you can download it and explore the power of web services with relational data, data stored in csv or Microsoft Excel files. 

WSO2 Data Services provides a convenient and user-friendly mechanism to expose data as web services and utilize a lot of QOS features such as WS-Reliable Messaging, WS-Security etc..

It consists of a management UI which allows you to create, deploy and invoke data services without writing a line of code. Also, Eclipse IDE plugin brings the Data servce functionality into Eclipse IDE in which you can configure data services very easily. 

There are lot more things you can do with this new product and some of the helpful references were already published. You may begin with quick start guide.

Stay tuned. This blog will be updated with many how-tos on WSO2 data services soon.

Data services and mediating SOAP messages with Synapse DBReport mediator

Apache Synapse is an ESB that has been designed to be simple to configure, very fast, and effective at solving many integration and gatewaying problems. It comes with a set of ready-to-use transports and mediators.
Visit Apache synapse web for more information about message mediation.

WSO2 data services is a convenient mechanism to provide a Web service interface for data stored in some data source. Data sources such as relational databases, CSV files & MS-Excel files can be easily service enabled using Data Services.
You can find more details about WSO2 Data services from here

I got a lot of positive feedback about my previous posts (securing web services) since they helped beginners to understand and experiment with simple examples without digging in to complex details.
I thought to describe a simple scenario which demonstrates the usage of data services and synapse together. I hope the following step-by-step instructions will help most of the novice users to get started with WSO2 data services and Apache synapse in a fairly simple manner.

I am going to use the following open source software tools in this demonstration. So, please make sure you have configured them in your environment.

Apache Synapse 1.1.1 (Download)
WSO2 WSAS 2.2.1 (Download)
Apache derby 10.3.2.1 (Download)
Apache JMeter (Download)

Scenario:
A SOAP request will be sent to WSO2 WSAS to get the net salary of an employee which resides in a database. The messages will be transferred via synapse. The response SOAP message will be subjected to mediation by synapse DBreport mediator. In which, the net salary of a different employee will be updated by evaluating the net salary of the requested employee. In other words, we will get the net salary of employee A and instruct synapse to update employee B's net salary to the net salary of employee A.

Step 1

Database preparation

We should prepare the necessary database and tables as the first step. Apache derby will be used in this example.

• Start derby network server

Go to derby_home/bin and run startNetworkServer.bat

• Create a database

start ij utility (derby_home/bin/ij.bat) and enter the following command.
CONNECT 'jdbc:derby://localhost:1527/employeedb;user=wsas;password=wsas;create=true';

• Create a table and insert data

create table employee(name varchar(10), empid varchar(10), netsalary double);
insert into employee values ('Sean','1',1000.00);
insert into employee values ('John','1',3000.00);
insert into employee values ('David','1',2000.00);

Next, I'm going to create a data service using the above data source. Data services allow users to expose the relational data as web services so that they can be accessed and manipulated in programming language independent manner.

Step 2

Create a data service

Please make sure the derby client driver is available in your WSAS instance. Copy derby_home/lib/derbyclient.jar to WSAS_HOME/lib.

Install WSO2 WSAS if it is not already installed. start WSO2 WSAS using WSAS_HOME/bin/wso2wsas.bat

Access WSAS management console (https://localhost:9443) where we can configure our data service through UI.

Go to 'Services and service group management' page and click on 'Define data service' link. You will get a page as given below.


Enter 'Synapsedataservice' as the service name. Select 'RDBMS' as the data source. A pop-up window will be displayed where we can configure data base details.
Driver Class = org.apache.derby.jdbc.ClientDriver
JDBC URL = jdbc:derby://localhost:1527/employeedb
user name= wsas
password = wsas

Click on the 'Next' after configuring data source details. The second step of the data service configuration will be displayed. Click on 'New query' button. The following pop-up window will appear.


Enter the following details in the above window.

Query ID=Empsal
SQL Statement = select * from employee where name = ?


Click on ' add new input mapping' and enter followings.

Name=name
sqlType=STRING

Grouped by element = employees
Row name = employee

Click on 'add new Output mapping' and enter the following values.
Mapping Type = element
Output field name = name, SQL column Name = name
Output field name = empid, SQL column Name = empid
Output field name = netsalary, SQL column Name = netsalary

After entering all of the above values, click 'Next' button in the data service - step 2.

Step 3 of the wizard will be displayed. Click on 'Add new operation' button and enter the following values.
Operation Name = EmpSalOp
Query= Empsal

Click 'Finish' to deploy the data service.

Step 3

Test the data service

You can test your data service simply by invoking it in RESTful manner. Issue the following url.
http://localhost:9762/services/Synapsedataservice/EmpSalOp?name=Sean

You should get the details of employee, Sean.

<datas2:employees>
<datas2:employee>
<datas2:name>Sean</datas2:name>
<datas2:empid>1</datas2:empid>
<datas2:netsalary>1000.0</datas2:netsalary>
</datas2:employee>
</datas2:employees>

You should observe the simplicity of exposing relational data using WSO2 Data services. Lets see how we can use Apache Synapse to do some mediation in messages passing through it.

Step 4

Creating Synapse configuration

Install Synapse1.1.1 (just unzip the binary distribution). Synapse provides a xml configuration file to define the mediation rules using synpase configuration language. Open Synapse_home/repository/conf/synapse.xml.

Remove the existing contents of that file and add the following configuration.

<definitions xmlns="http://ws.apache.org/ns/synapse">
<sequence name="main">
<in>
<send>
<endpoint>
<address uri="http://localhost:9762/services/Synapsedataservice"/>
</endpoint>
</send>
</in>
<out>
<log level="custom">
<property name="text"
value="** Reporting to the Database **"/>
</log>
<dbreport>
<connection>
<pool>
<driver>org.apache.derby.jdbc.ClientDriver</driver>
<url>jdbc:derby://localhost:1527/employeedb;create=false</url>
<user>wsas</user>
<password>wsas</password>
</pool>
</connection>
<statement>
<sqlupdate employee set netsalary=? where name='John'</sql>
<parameter expression="//datas2:employees/datas2:employee/datas2:netsalary"
xmlns:datas2="http://ws.wso2.org/dataservice" type="DOUBLE"/>

</statement>
</dbreport>
<send/>
</out>
</sequence>
</definitions>

Here, the request message pass through synapse will be directed to the data service endpoint without doing any mediation. It is defined in the <in/> element.
The SOAP response will be transferred via two different mediators.

Log - when the soap response reaches synapse, it just logs the message as defined.
dbreport - writes information to a Database, using the specified insert SQL statement.

In this configuration, we evaluate the response SOAP message using an XPath expression and get the net salary of the employee. Then we update the net salary of employee John with that.

Now you can start synapse with the above configuration by running synapse.bat.

To see how this works, we need to send a SOAP request message to Synapse. Apache Jmeter can easily be used to transmit a soap message.

Step 5

Test message mediation

Install jmeter in your system (Just unzip jmeter binary distribution). Run jmeter.bat.
Create a thread group and add SOAP/XML-RPC request sampler. (Please read http://wso2.org/library/1085 if you are not familiar with testing web services using Jmeter)

Add the following soap request in to the SOAP/XML-RPC data section.

<soapenv:envelope soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:body>
<axis2ns5:empsalop axis2ns5="http://ws.wso2.org/dataservice">
<name>Sean</name>
</axis2ns5:empsalop>
</soapenv:body>
</soapenv:envelope>

You may have wondered how I captured the above request Soap message. There are several different approaches.
1. write a java client and invoke the data service. Capture the message using apache Tcpmon
2. Enable Soap Tracing in the WSAS management console. As we did above, invoke the service in RestFul manner. Then access the SoapTracer in WSAS management console and copy the soap request. (This will be the easiest method)

Enter 'http://localhost:8080' as the URL in SOAP/XML-RPC request.
Enable 'Send SoapAction' and enter 'urn:EmpSalOp'

If everything is correct, you should see the following in your Jmeter console.



Run the sampler. You may check the synapse console where you can monitor the message mediation.

Lets see whether the net salary of John is updated as expected. Open the ij utility of Derby and issue the following SQL statement.
select * from employee;

You should notice that the net salary of John and Sean are same.

In this post, we looked at the WSO2 data services and Apache Synapse using a simple scenario. You will be able to improve the above scenario by applying more mediation rules.