Invoking Multiple Operations via Request Box

The request box feature allows you to invoke multiple operations (consecutively) using one single request_box operation. Follow the steps given below to define a data service that can invoke request box operations:

Setting up a datasource

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

Install the MySQL server.
Download the JDBC driver for MySQL and copy it to your directory<EI_HOME>/lib.
Create a database named Company.

Create the following tables:

Offices table:

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

Employees table:

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`,`OfficeCode`));

Insert the following data into the Offices table:

INSERT INTO Offices VALUES (1,"51","Glen Street","Norwich","London","United Kingdom","+441523624");
INSERT INTO Offices VALUES (2,"72","Rose Street","Pasadena","California","United States","+152346343");

Define a data service to invoke request box operations

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

Log into the management console of the ESB profile and click Create under Data Service.
Add a name for the data service.
Select the Enable Boxcarring check box. As a result of selecting this check box, the Disable Legacy Boxcarring Mode check box will appear on your screen as shown below. You can select this new check box (Disable Legacy Boxcarring Mode) as well.
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 and then click Add New Datasource.
Connect to the Company database that you defined above. For instructions, see Exposing an RDBMS as a Data Service.
Click Save, and then click Next to go to the Queries screen.
Click Add New Query to specify the query details:

Enter addEmployeeQuery as the query ID.

Enter the following SQL dialect:

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

Click Generate Input Mapping and input mappings will be generated automatically:
Click Save.
Click Add New Query to Create another query for the Company datasource:

Enter selectOfficebyID as the query ID.

Enter the following SQL dialect:

select OfficeCode,AddressLine1,AddressLine2,City,State,Country,Phone from Offices where OfficeCode=:OfficeCode

Generate input and output mappings:
1. Click Generate Input Mapping and input mappings will be generated automatically for the office code field.
2. Click Generate Response to automatically generate the output mappings for the fields in the Offices table.
Click Save.
Click Next and then click Add New Operation to create an operation for the addEmployeeQuery query as shown below.
Click Save.
Click Add New Operation to create another operation to invoke the selectOfficebyID query:
Click Save and then click Finish.

Invoking the data service

The Deployed Services window allows you to manage data services. You can try the data service you created by using the TryIt tool in this screen, which is in your product by default.

In the Deployed Services screen, click Try this Service to open the data service from the TryIt tool. You will find that there is an additional request box operation.

Select the request box operation and enter the following values:

Enter values for the addEmployeeQuery query.

Enter the same office code you used for the above query in the selectOfficebyID query.

<p:request_box xmlns:p="http://ws.wso2.org/dataservice">
      <!--Exactly 1 occurrence-->
      <addEmployeeQueryOp xmlns="http://ws.wso2.org/dataservice">
         <!--Exactly 1 occurrence-->
         <xs:EmployeeNumber xmlns:xs="http://ws.wso2.org/dataservice">1002</xs:EmployeeNumber>
         <!--Exactly 1 occurrence-->
         <xs:FirstName xmlns:xs="http://ws.wso2.org/dataservice">Peter</xs:FirstName>
         <!--Exactly 1 occurrence-->
         <xs:LastName xmlns:xs="http://ws.wso2.org/dataservice">Parker</xs:LastName>
         <!--Exactly 1 occurrence-->
         <xs:Email xmlns:xs="http://ws.wso2.org/dataservice">peter@wso2.com</xs:Email>
         <!--Exactly 1 occurrence-->
         <xs:OfficeCode xmlns:xs="http://ws.wso2.org/dataservice">2</xs:OfficeCode>
      </addEmployeeQueryOp>
      <!--Exactly 1 occurrence-->
      <selectOfficebyIDOP xmlns="http://ws.wso2.org/dataservice">
         <!--Exactly 1 occurrence-->
         <xs:OfficeCode xmlns:xs="http://ws.wso2.org/dataservice">2</xs:OfficeCode>
      </selectOfficebyIDOP>
</p:request_box>

Execute the request box operation.

First, the addEmployeeQueryOp operation will be executed. The data will be successfully added to the database.

Secondly, the selectOfficebyIDOp operation will be executed and the details of the office attached to the employee will be returned in the result as shown below.

<Entries xmlns="http://ws.wso2.org/dataservice">
      <Entry>
         <OfficeCode>2</OfficeCode>
         <AddressLine1>72</AddressLine1>
         <AddressLine2>Rose Street</AddressLine2>
         <City>Pasadena</City>
         <State>California</State>
         <Country>United States</Country>
         <Phone>+152346343</Phone>
      </Entry>
</Entries>