Page Comparison

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. 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.

...

1. Install the MySQL server.
2. Download the JDBC driver for MySQL from here and copy it to your <EI_HOME>/lib directory.
   
3. Set up a database for storing information of offices:

   1. Create a database called called OfficeDetails: CREATE DATABASE OfficeDetails;

   2. Create the the Offices table:
      

      Code Block
      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`));

4. Set up a database for storing information of employees:
   1. Create a database called called EmployeeDetails: CREATE DATABASE EmployeeDetails CREATE DATABASE EmployeeDetails;

   2. Create the the Employees table:
      

      Code Block
      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`));

...

1. Log into the management console of WSO2 EI and the ESB profile and click Create under Data Service.
2. Add a name for the data service and go to the next step.

3. Select the Enable Boxcarring and Disable Legacy Boxcarring Mode check boxes.

   Note
   The Enable boxcarring option enables both the legacy boxcarring and the new request box methods. You can disable the legacy boxcarring method and use the request box method by selecting the Disable Legacy Boxcarring Mode option.

4. Click Click Next to  to go to the the Datasources screen screen.
5. Create a datasource connection for the the CompanyDetails database  database follows:
   1. Enter Enter XAoffices as  as the datasource ID.
   2. Select Select RDBMS as  as the datasource type.
   3. Select Select External Datasource from  from the adjoining field.
   4. Select Select MySQL as  as the database engine.
   5. Enter the following database class corresponding to MySQL: com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
      Click

   6. Click Add Properties

      and create

       and specify the following connection settings for

      the OfficeDetails database.
      URL: jdbc

      the OfficeDetails database:

      PropertyName	Value
      url	jdbc:mysql://localhost:3306/OfficeDetails

      
      USER: root
   Image RemovedSave the

   1. user	root

   The New Datasource screen will now look as follows:
   Image Added
   
6. Save the XAoffices datasource.
7. Create a datasource connection for the the EmployeeDetails database as follows:
   1. Enter XAemployees as the datasource ID.
   2. Select RDBMS as the datasource type.
   3. Select External Datasource from the adjoining field.
   4. Select MySQL as the database engine.
   5. Enter the following database class corresponding to MySQL: com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
      

   6. Click Add Properties and

      create

      specify the following connection settings for the 

      OfficeDetails database.
      URL: jdbc

      EmployeeDetails database:

      Property Name	Value
      url	jdbc:mysql://localhost:3306/EmployeeDetails

      
      USER: root
   Image RemovedSave the

   1. user	root

   The New Datasource screen will now look as follows:
   Image Added
   
8. Save the XAemployees datasource.

...

Defining queries for the datasources

1. Click Add New Query to specify an insert query for the the XAoffices datasource:

   1. Enter InsertOfficeQuery as the query ID.

   2. Enter the following SQL dialect:

      Code Block
      insert into Offices (OfficeCode,AddressLine1,AddressLine2,City,State,Country,Phone) values(:OfficeCode,:AddressLine1,'test','test','test','USA','test')

2. Click Generate Input Mapping and an input mapping will be generated automatically for the fields in the datsource:
   
3. Save the query.
4. Click Add New Query to specify an insert query for the the XAemployees datasource:

   1. Enter Enter InsertEmployeeQuery as the query ID.

   2. Enter the following SQL dialect:

      Code Block
      insert into Employees (employeeNumber,firstName,lastName,email,jobtitle,OfficeCode) values(:employeeNumber,:firstName,:lastName,'test','test',:OfficeCode)

5. Click Generate Input Mapping and an input mapping will be generated automatically for the fields in the datasource:
   
6. Save the query.
7. Click Click Next to  to go to the the Operations section section. Define operations to invoke the two queries defined above.
   • Create the the InsertOfficeOp for the  for the InsertOfficeQuery.
   • Create the the InsertEmployeeOp for the  for the InsertEmployeeQuery.
8. Finish creating the data service.

...

Inserting data into the distributed RDBMSs

1. Go to the the Deployed Services page  page and you will see the data service listed.
2. Click Click Try this service to  to open the TryIt tool.
3. Select the batch operation that is created by default (request_box_operation).
4. Specify the values that should be inserted to the the OfficeDetails database and  database and EmployeeDetails database  database respectively.
5. Invoke the operation.
6. See that the data is successfully inserted into the two databases.
7. Now, enter another set of values for the two operations. However, you can enter an erroneous value for one fieldmake one operation erroneous. For example, leave the OfficeCode field of the InsertEmployeesOp blank.
8. Invoke the request box operation.
9. See that no records have been entered into either database.