Handling Distributed Transactions

The data integration feature in the ESB profile of WSO2 EI supports data federation, which means that a single data service can expose data from multiple datasources. However, if you have multiple RDBMSs connected to your data service, and if you need to perform IN-ONLY operations (operations that can insert data and modify data in the datasource) in a coordinated manner, the RDBMSs need to be defined as XA datasources.

Let's consider a scenario where you have two MySQL databases. You can define a single data service for these databases and insert data into both as explained below.

Setting up distributed MySQL databases

Follow the steps given below to set up a MySQL database for this tutorial.

Download the product installer from here, and run the installer.
Let's call the installation location of your product the <EI_HOME> directory.

If you installed the product using the installer, this is located in a place specific to your OS as shown below:

OS	Home directory
Mac OS	`/Library/WSO2/EnterpriseIntegrator/6.5.0`
Windows	`C:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\`
Ubuntu	`/usr/lib/wso2/EnterpriseIntegrator/6.5.0`
CentOS	`/usr/lib64/EnterpriseIntegrator/6.5.0`

Install the MySQL server.
Download the JDBC driver for MySQL from here and copy it to your <EI_HOME>/lib directory.

Set up a database for storing information of offices:

Create a database called OfficeDetails.
```
CREATE DATABASE OfficeDetails;
```

Create the Offices table:

USE OfficeDetails;

CREATE TABLE `Offices` (`OfficeCode` int(11) NOT NULL, `AddressLine1` varchar(255) NOT NULL, `AddressLine2` varchar(255) DEFAULT NULL, `City` varchar(255) DEFAULT NULL, `State` varchar(255) DEFAULT NULL, `Country` varchar(255) DEFAULT NULL, `Phone` varchar(255) DEFAULT NULL, PRIMARY KEY (`OfficeCode`));

Set up a database to store the employee information:

Create a database called EmployeeDetails.
```
CREATE DATABASE EmployeeDetails;
```

Create the Employees table:

USE EmployeeDetails;

CREATE TABLE `Employees` (`EmployeeNumber` int(11) NOT NULL, `FirstName` varchar(255) NOT NULL, `LastName` varchar(255) DEFAULT NULL, `Email` varchar(255) DEFAULT NULL, `JobTitle` varchar(255) DEFAULT NULL, `OfficeCode` int(11) NOT NULL, PRIMARY KEY (`EmployeeNumber`));

Adding the datasources to a data service

Let's create a data service using the Create Data Service wizard:

Start the WSO2 ESB profile.
Access the management console of the ESB profile: https://localhost:9443/carbon
Sign in using admin as the username and password.
On the Data Service menu click Create.
Add a name for the data service and click Next.
Select the Enable Boxcarring and Disable Legacy Boxcarring Mode check boxes.
What is Boxcarring and Request Box?
Boxcarring is a method of grouping a set of service calls so that they can be executed as a group (i.e., the individual service calls will be executed consecutively in the specified order). Note that we have now deprecated the boxcarring method for data services. Instead, we have replaced boxcarring with a new request type called request_box.
Request box is simply a wrapper element (request_box), which wraps the service calls that need to be invoked. When the request_box is invoked, the individual service calls will be executed in the specified order, and the result of the last service call in the list will be returned. In this tutorial, we are using the request box to invoke the following two service calls:
1. Add a new employee to the database
2. Get details of the office of the added employee
When you click the Enable Boxcarring check box for the data service, both of the above functions (Boxcarring and Request box) are enabled. However, since boxcarring is deprecated in the product, it is recommended to disable boxcarring by clicking the Disable Legacy Boxcarring Mode check box.
Click Next to go to the Datasources screen.

Click Add New Datasource to create a datasource connection for the OfficeDetails database:

Datasource Id Enter XAoffices as the datasource ID.

Datasource Type

Select RDBMS as the datasource type and s elect External Datasource from the adjoining field.

Database Engine

Select MySQL.

Datasource Class Name

Enter the database class that corresponds to MysqlXADataSource.class.
Example: For MySQL 5, enter com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

Make sure to check the path of the MysqlXADataSource.class and enter the updated path here. Else, you run into errors.

Add New Property

Specify the following connection settings for the OfficeDetails database:

Property Name	Value
`url`	`jdbc:mysql://localhost:3306/OfficeDetails`
`user`	Enter your MySQL server's username. Example: root
`password`	Optionally: Enter your MySQL server's password. If you have not assigned a password, do not add this property.

Example: The New Datasource screen will now look as follows:

Save the XAoffices datasource.

Click Add New Datasource to create a datasource connection for the EmployeeDetails database as follows:

Datasource Id Enter XAemployees as the datasource ID.

Datasource Type

Select RDBMS as the datasource type and select External Datasource from the adjoining field.

Database Engine

Select MySQL.

Datasource Class Name

Enter the database class that corresponds to MysqlXADataSource.class.
Example: For MySQL 5, enter com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.

Make sure to check the path of the MysqlXADataSource.class and enter the updated path here. Else, you run into errors.

Add New Property

Specify the following connection settings for the EmployeeDetails database:

Property Name	Value
`url`	`jdbc:mysql://localhost:3306/EmployeeDetails`
`user`	Enter your MySQL server's username. Example: root
`password`	Optionally:Enter your MySQL server's password. If you have not assigned a password, do not add this property.

Example: The New Datasource screen now look as follows:

Save the XAemployees datasource and click Next.

Defining queries for the datasources

Click Add New Query to specify an insert query for the XAoffices datasource:
1. Enter InsertOfficeQuery as the query ID.
2. Enter the following SQL dialect:
```
insert into Offices (OfficeCode,AddressLine1,AddressLine2,City,State,Country,Phone) values(:OfficeCode,:AddressLine1,'test','test','test','USA','test')
```
3. Click Generate Input Mapping and the input mappings are generated for the fields in the datasource:
Save the query.

Click Add New Query to specify an insert query for the XAemployees datasource:

Enter InsertEmployeeQuery as the query ID.

Enter the following SQL dialect:

insert into Employees (EmployeeNumber,FirstName,LastName,Email,JobTitle,OfficeCode) values(:EmployeeNumber,:FirstName,:LastName,'test','test',:OfficeCode)

Click Generate Input Mapping and the input mappings are generated for the fields in the datasource:
Save the query.
Click Next to go to the Operations section. Define operations to invoke the two queries defined above.
- Create the InsertOfficeOp for the InsertOfficeQuery.
- Create the InsertEmployeeOp for the InsertEmployeeQuery.
Finish creating the data service.

Inserting data into the distributed RDBMSs

Go to the Deployed Services page and you will see the data service listed.
Click Try this service to open the TryIt tool.
Select the batch operation that is created by default (request_box).
Specify the values that should be inserted to the OfficeDetails database and EmployeeDetails database respectively.
Click Send to invoke the operation.
See that the data is successfully inserted into the two databases.
Go to the MySQL terminal and run the following commands:
- Check the office details in the offices table:
```
USE OfficeDetails;
SELECT * FROM Offices;
```
- Check the employee details in the employees table.
```
USE EmployeeDetails;
SELECT * FROM Employees;
```
Now, enter another set of values for the two operations but enter an erroneous value for one field.
Invoke the operation.
Check the database tables.
You see that no records have been entered into either database.