Defining Nested Queries

Nested queries help you to use the result of one query as an input parameter of another, and the queries executed in a nested query works in a transactional manner. Follow the steps given below to add a nested query to a data service.

---

Prerequisites

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

1. Install the MySQL server.

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

3. Create the following database: Company

4. 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`), CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`OfficeCode`) REFERENCES `OFFICES` (`OfficeCode`));

5. Insert the following data into the tables:

   • Add to 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");

   • Add to the Employees table:

     INSERT INTO EMPLOYEES VALUES (1,"John","Gardiner","john@office1.com","Manager",1); INSERT INTO EMPLOYEES VALUES (2,"Jane","Stewart","jane@office2.com","Head of Sales",2); INSERT INTO EMPLOYEES VALUES (3,"David","Green","david@office1.com","Manager",1);

You will now have two tables in the Company database as shown below:

•  Offices table:
  

  
   

• Employees table: 
  

  

 

---

Creating the data service

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

1. Log into the management console and click Create under Data Service.

2. Enter the following name for the data service

3. Click Next to enter the datasource connection details.

Connecting to the datasource

Follow the steps given below.

1. Click Add New Datasource and enter the following details:

2. Save the datasource.

3. Click Next to start creating queries.

Creating a query to GET employee details by office

Let's create a query that can retrieve employee data, based on the office code. That is, when the office code is provided as an input, the data service should get the relevant employee details and present the result.

1. Click Add New Query to specify the query details.

2. Enter the following details:

3. Generate input and output mappings:

   1. Click Generate Input Mapping and an input mapping will be generated automatically for the OfficeCode field:

   2. Click Generate Response to automatically generate output mappings for the EmployeeNumber, FirstName, LastName, Email, Job Title and Office Code fields.
      

4. Save the EmployeeOfficeSQL query.

Creating a nested query to GET office details

Let's create a query that can retrieve details of an office based on the office code. That is, when the query is invoked, the data service should get the relevant details of the office premises. Additionally, we will nest the EmployeeOfficeSQL query that was created previously to make sure that the details of the employees attached to each office code are also included in the office details.

Creating a SOAP operation to invoke the query

Now, let's create SOAP operations to invoke the queries created above. Alternatively, you can create REST resources to invoke the queries. See the next section for instructions.

1. Click Add New Operation and enter the details given below.

2. Save the listOfficeSQLOP operation.

Creating a REST resource to invoke the query

Now, let's create REST resources to invoke the queries created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section, for instructions.

1. Click Add New Resource and enter the details as shown below.

2. Save the resource.

Finish creating the data service

Once you have defined the operation, click  Finish to complete the data service creation process. You will now be taken to the  Deployed Services screen, which shows all the data services deployed on the server.

---

Invoking the data service using SOAP

You can try the data service you created by using the TryIt tool that is in your product by default. 

1. Go to the Deployed Services screen.

2. Click Try this Service to open the data service from the TryIt tool.

3. Execute the listOfficeSQLOP operation and you will see the result.

Invoking the data service using REST

The service can be invoked in REST-style via curl (http://curl.haxx.se). Shown below is the curl command to invoke the GET resource:

You will now see the following result: