This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.
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.
Step 1: Setting up an RDBMS
Follow the steps given below to set up a MySQL database for this tutorial.
- Install the MySQL server.
- Download the JDBC driver for MySQL from here and copy it to your
<EI_HOME>/lib
directory.
- Create the following database:
trainingdb
Create the following table:
CREATE TABLE ACCOUNT(AccountID int NOT NULL,Branch varchar(255) NOT NULL, AccountNumber varchar(255),AccountType ENUM('CURRENT', 'SAVINGS') NOT NULL, Balance FLOAT,ModifiedDate DATE,PRIMARY KEY (AccountID));
Enter the following data into the table:
INSERT INTO ACCOUNT VALUES (1,"AOB","A00012","CURRENT",231221,'2014-12-02');
Step 2: Adding an RDBMS datasource
Follow the steps given below.
Log in to the management console and select Create under Data Service.
In the Create Data Service screen, enter AccountDetailsService as the data service name.
Click Next.
When you get to the Add New Data Source screen, details as shown below.
Field Value Datasource ID AccountDetails Datasource type RDBMS Database Engine Select MySQL URL jdbc:mysql://localhost:3306/trainingdb User Name root Password (Leave this field blank) 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.Be sure to 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'.
- Enter the URL to your MySQL database in the URL field.
- Enter the username and password to connect to your MySQL database. By default the username is root and the password is blank.
Click Save and then Next to start defining a query.
Step 3: Defining a query
Now let's start writing a query for getting data from the datasource. The query will specify the data that should be fetched and the format that should be used to display data when the query is invoked.
- Click Add New Query to open the Add New Query screen.
- Enter the following values:
- Query ID: Enter an ID for the query.
- Datasource: Select the datasource for which you are going to write a query. Select the RDBMS datasource that you created previously.
SQL: In this field, enter the SQL statement describing the data that should be retrieved from the RDBMS datasource.
SELECT AccountID,Branch,AccountNumber,AccountType,Balance,ModifiedDate FROM ACCOUNT WHERE AccountID=:AccountID
Add input mappings: Click Generate Input Mapping to automatically create a mapping for the AccountID field in the database.
Input mappings allow you to add parameters to a query so that you can set the parameter value when executing the query. According to the above definition, you need to provide the account ID as an input in order to retrieve the data corresponding to the account ID.
Find out more about defining Input Mappings.
Add output mappings: Click Generate Response to automatically create mappings for the fields that should show in the output.
Output mapping is used to specify how the data that is fetched from your query will be shown in the response. Note that, by default, the output type is XML . Find out more about defining Output Mappings.
- Click Save, to save the query.
- Click Next to go to the Operations screen.
Step 4: Defining an operation to invoke the query
Data service operations are written to expose data as SOAP services.
- Click Add New Operation to open Add New Operation screen.
- Add a name for your operation in the Operation Name field.
- In the Query ID field, select the query that you defined previously.
- Save the operation.
Step 5: Finish creating the data service
In this tutorial, we are only defining a SOAP service to expose the data. Therefore, you can click Finish to complete the data service creation process, after defining the operation. You will now be taken to the Deployed Services screen, which shows all the data services deployed on the server.
If you click Next after defining the operation, you will be taken to the Add Resources screen, which allows you to expose the data as a REST resource. If you want to create a REST resource, see Exposing Data as a REST Resource for instructions.
Step 6: Invoking your data service
You can try the data service you created by using the TryIt tool that is in your product by default.
- Go to the Deployed Services screen.
- Click the Try this service link for the AccountDetailsService. The TryIt Tool will open with the data service.
- Select the operation you created earlier. You need to provide the account ID as an input. Enter '1'.
Click Send to see the details of the account.
<Entries xmlns="http://ws.wso2.org/dataservice"> <Entry> <AccountID>1</AccountID> <Branch>AOB</Branch> <AccountNumber>A00012</AccountNumber> <AccountType>CURRENT</AccountType> <Balance>231221</Balance> <ModifiedDate>2014-12-02+05:30</ModifiedDate> </Entry> </Entries>