Call MySQL stored procedures using WSAS data services

WSO2 Web services application server (WSAS) is shipped with a lot of useful features which can be used effectively in a production system. One of these cool functionalities is exposing relational data using WSAS data services.

Lets see how we can call an existing stored procedure using an WSAS data service. Here I'm going to use a MySQL DB instance for the demonstration purposes. You may use any DBMS as your preference.

Step 1

Create a data base
mysql>create database employeedb;

Step 2
Create a table and inseert values.
mysql>create table employee (id INTEGER, name varchar(20), address varchar(30));

mysql>insert into employee values (1, 'Charitha', 'Colombo');

Repeat the insert statement with more data.

Step 3

Create a stored procedure.

CREATE PROCEDURE getEmployee(empid INTEGER) select id, name, address from employee where id=empid;

Step 4
We haven't used WSO2 WSAS data service configuration yet. All of the above are basic MySQL database setting up steps. Now, we can create a data service configuration using WSAS management console. Lets start our WSAS instance by running WSAS_HOME/bin/wso2wsas.bat {sh}

Then log in to the management console using default administrator credentials (admin/admin). Next, select 'Services' from the left navigation and click on 'Define data service'

Step 5
Now you are in the first step of the data service configuration wizard. Provide a unique name for your data service. Then, select 'RDBMS' as the data source. You will get a pop-up window as given in the following screen.



Step 6

In the above pop-up window, select 'MySQL' as the database type. Then enter the following values for the remaining fields.
Driver class = com.mysql.jdbc.Driver
JDBC URL = jdbc:mysql://localhost:3306/employeedb
user = root
password = root

Next, click on 'Test Connection' to verify the communication between WSAS and our database.
If the connection is successful, click 'OK' in the pop-up window.

Step 7

Now we are done with the first step of the data service configuration. Click 'Next' to proceed to the step 2.

Click on 'New Query' button to add a query to our data service. You will get 'Add new Query' pop-up window. Fill the values as given in the screen shot.



Note that, the SQL statement field contains the stored procedure call (call getEmployee(?)).

Click 'OK' in the pop-up window. Then select 'Next' to continue through the wizard. You will be directed to the step 3 of the data service configuration.

Step 8

Click on 'Add new operation' button. 'Add new operation' pop-up window will be displayed. Enter a unique name for the operation. Select 'query1' from the 'Query' drop down menu.

Click 'Finish' to save our data service.

If everything is successful, you will see the deployed data service in the 'Services and service group management' page. Now, we need to verify whether the stored procedure call generate correct results. In order to do that, we may use 'Tryit', one of the very helpful tools in WSAS which can be used to verify simple service invocations.

Step 9

Select the newly created data service from the 'Services and service group management' page.
Select 'Tryit'.
Click on the operation button after entering empid.



As you can see above, our MySQL stored procedure has been invoked by data service. We can enhance the data service configuration to handle more complex stored procedures.