The data integration feature in 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. 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.
...
- 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:
Code Block useUSE 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 for storing information of employees:
- Create a database called EmployeeDetails:
CREATE database EmployeeDetails
.;
Create the Employees table:
Code Block useUSE 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`));
- Create a database called EmployeeDetails:
...
- Log into the management console of WSO2 EI and click Create under Data Service.
- Add a name for the data service and go to the next step.
- Select the Disable Legacy Boxcarring Mode check box.
- Click Next to go to the Datasources screen.
- Create a datasource connection for the CompanyDetails database follows:
- Enter XAoffices as the datasource ID.
- Select RDBMS as the datasource type.
- Select External Datasource from the adjoining field.
- Select MySQL as the database engine.
- Enter the following database class corresponding to MySQL:
com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
- Click Add Properties and create the connection settings for the OfficeDetails database.
URL: jdbc:mysql://localhost:3306/OfficeDetails
USER: root
- Save the XAoffices datasource.
- Create a datasource connection for the EmployeeDetails database as follows:
- Enter XAemployees as the datasource ID.
- Select RDBMS as the datasource type.
- Select External Datasource from the adjoining field.
- Select MySQL as the database engine.
- Enter the following database class corresponding to MySQL:
com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
- Click Add Properties and create the connection settings for the OfficeDetails database.
URL: jdbc:mysql://localhost:3306/EmployeeDetails
USER: root
- Save the XAemployees datasource.
...