Exposing an RDBMS as a Data Service

In this tutorial we will run through the process of service enabling an RDBMS as a data service. Also, see the samples in Data Integration Samples.

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.

   
   

   If the driver class does not exist in the relevant folders when you create the datasource, you will get an exception such as 'Cannot load JDBC driver class com.mysql.jdbc.Driver'.
3. Create the following database: Employees

4. Create the Employee table inside the Employees database:
   

   CREATE TABLE Employees (EmployeeNumber int(11) NOT NULL, FirstName varchar(255) NOT NULL, LastName varchar(255) DEFAULT NULL, Email varchar(255) DEFAULT NULL, Salary varchar(255));

Creating the data service

Follow the steps given below.

1. Log in to the product's management console.
2. Click Data Service → Create, to start creating a data service.

3. Enter the following name for the data service.

   Data Service Name	RDBMSDataService

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

   Datasource ID	Datasource
   Datasource Type	RDBMS
   Datasource Type (Default/External)	Leave Default selected.
   Database Engine	MySQL
   Driver Class	com.mysql.jdbc.Driver
   URL	jdbc:mysql://localhost:3306/Employees
   User Name	root

   If you enter External instead of the Default datasource type, your datasource should be supported by an external provider class, such as com.mysql.jdbc.jdbc2.optional.MysqlXADataSource. You can select the External option and enter the name and value of connection properties by clicking Add Property. For example,
   
   After an external datasource is created, it can be used as a usual datasource in queries. See the tutorial on handling distributed transactions for more information on using external datasources.

2. Save the datasource.
3. Click Next, to start creating queries.

Creating a query to GET data

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

1. Click Add New Query to start creating a new query.

2. Enter the following details:

   Query Name	GetEmployeeDetails
   Datasource	Datasource
   SQL	select EmployeeNumber, FirstName, LastName, Email from Employees where EmployeeNumber=:EmployeeNumber

3. Click Generate Input Mapping to create the input mapping. The employee number is the input as shown below.

   

4. Click Generate Response to create the output mapping. This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 
   

   If required, you can choose RDF, or JSON as the output type. See Using JSON with Data Services for more information on exposing data in JSON format.

   

5. Save the query.

Creating a query to Post data

Let's create another query to post new employee data to the datasource.

1. Click Add New Query to start creating a new query.

2. Enter the following details:

   Query Name	AddEmployeeDetails
   Datasource	Datasource
   SQL	insert into Employees (EmployeeNumber, FirstName, LastName, Email, Salary) values(:EmployeeNumber,:FirstName,:LastName,:Email,:Salary)

3. Click Generate Input Mapping to create the input mapping. You need to specify values for the following elements when posting new employee data.

   

4. Save the query.

Creating a query to Update data

Now, let's create a query that can update an existing employee record in the datasource.

1. Click Add New Query to start creating a new query.

2. Enter the following details:

   Query Name	UpdateEmployeeDetails
   Datasource	Datasource
   SQL	update Employees set LastName=:LastName, FirstName=:FirstName, Email=:Email, Salary=:Salary where EmployeeNumber=:EmployeeNumber

3. Click Generate Input Mapping to create the input mapping. You need to specify values for the following elements when posting new employee data.

   

4. Save the query.

Create SOAP operations to invoke queries

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 as shown below.

   Operation Name	GetEmployeeOp
   Query ID	GetEmployeeDetails

2. Save the operation.

3. Click Add New Operation and enter the details as shown below.

   Operation Name	AddEmployeeOp
   Query ID	AddEmployeeDetails

4. Save the operation.

5. Click Add New Operation and enter the details as shown below.

   Operation Name	UpdateEmployeeOp
   Query ID	UpdateEmployeeDetails

6. Save the operation.

You can now invoke the data service query using SOAP.

Create REST resources to invoke queries

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.

   Resource Path	Employee/{EmployeeNumber}
   Resource Method	Get
   Query ID	GetEmployeeDetails

2. Save the resource.

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

   Resource Path	Employee
   Resource Method	POST
   Query ID	AddEmployeeDetails

4. Save the resource.

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

   Resource Path	Employee
   Resource Method	PUT
   Query ID	UpdateEmployeeDetails

6. Save the resource.
7. Click Finish to complete creating the data service.

You can now invoke the data service query using REST.

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.

curl -X GET http://localhost:8280/services/RDBMSDataService.HTTPEndpoint/Employee/3