[Tutorial] Calling Stored Procedures with IN and OUT Parameters Using WSO2 Data Services Server

This article will explain how you could call a stored procedure using WSO2 Data Services Server (WSO2 DSS); it will also discuss details of some complex stored procedures with IN and OUT parameters.

Prerequisites

1. WSO2 DSS (wso2dss-3.2.2.zip); download here
2. MySQL Server 5.0 or above
3. Java 1.6 or above
4. Linux or Windows Machine

Sample 1 - Calling a Stored Procedure with IN parameters only

In Sample 1, we will demonstrate calling a stored procedure with only the IN parameters. The following steps explains how to create and populate schemas, how to create the data service using WSO2 DSS, and finally how to invoke the service and retrieve results.

Creating and populating databases

• Login to the MySQL console using the terminal and create the database;

  create database dss_sample;

• Use the created database and create a table called Employee
  

  use dss_sample;

  CREATE TABLE Employee(EmployeeID int PRIMARY KEY, FirstName varchar(255), LastName varchar(255),Team varchar(255));

• Insert some data to the Employee table
  INSERT INTO Employee VALUES (100 , 'Sameera', 'Jayasoma', 'Carbon');

  INSERT INTO Employee VALUES (101 , 'Aruna', 'Karunarathna', 'Carbon');

  INSERT INTO Employee VALUES (102 , 'Kishanthan', 'Thangarajah', 'Carbon');

  INSERT INTO Employee VALUES (103 , 'Kasun', 'Indrasiri', 'ESB');

  INSERT INTO Employee VALUES (104 , 'Sagara', 'Gunathunga', 'GREG');

Create the Stored Procedure

• Create the stored procedure called GetEmployeeByID to get the Employee details for the IN parameter EmployeeID

  CREATE PROCEDURE GetEmployeeByID(IN EmpID int) SELECT FirstName , LastName , Team FROM Employee WHERE EmployeeID = EmpID;

• Verify the Procedure by calling GetEmployeeByID, and check the output.

  call GetEmployeeByID(101);

  FirstName	LastName	Team
  Aruna	Karunarathna	Carbon

WSO2 DSS Product Configuration

Next extract the downloaded product to your local machine. Let’s assume the extracted folder is .

Since we are using the MySQL server, we need the MySQL connector jar. Download the MySQL connector from here - https://dev.mysql.com/downloads/connector/j/

Copy the mysql-connector-java-5.1.29-bin.jar file to the /repository/components/lib folder.

Now start the server using the following command;

sh /bin/wso2server.sh

Login to the server username:password admin:admin using the following URL
https://localhost:9443/carbon/admin/login.jsp

After Login Go to Main → Manage → Services, In the Add select Create.

Figure 01

Give a Data Service Name, e.g. StoredProcedureSample and a namespace www.wso2.org/dss

Then click next. In the Next Window select Add New Data Source.

Figure 02

Thereafter, Add New Data Source, Add a DataSource ID and Select RDBMS; then some columns will be added. Fill in the connection details as follows:

After adding those details click Test Connection to verify the DB Connection details.

Figure 03

Save the Connection details, click next, and click on Add New Query.

Add a Query ID value, e.g. getEmployeeDetailsQuery and select the data source you’ve created in the previous step.

Thereafter, in the SQL parameter, add the Stored Procedure as
call GetEmployeeByID(?);

Figure 04

Now we have to map input and output parameters to the query. Here, we have only one input mapping to be done.

Click on Add New Input Mapping.

Fill out the;

Note that the IN/OUT type is set to IN.

Then click add and click Main Configuration; you will return back to the Add Query page. Now the input mapping has been completed. In the next step, we have to map the output results of the Stored Procedure.

Figure 05

To map the output values we have to add the Group by element and Row Name.

E.g.

Then Click on the Add New Output mapping. It will redirect you to a new page. Select the mapping type as element.

Fill out the output Field Name to your desired name, set the Parameter Type to the real database column type and Data Source Column. Name to the real name of the table. Then, below, you will be able to see the Allowed User Roles section. Here we can define which role can view the defined column. If nothing is selected the column can be viewed by anyone. Then Click Add. Likewise, add the output column mapping for the LastName and Team fields also. After you’ve completed adding the output mappings, Click on Main Configuration, which will direct you to the Add Query page.

Figure 06

After adding the output mapping the query will look like this.

Figure 07

Now save and click Next to add the operation. Click on Add New Operation and Fill out the following details. Set the operation name as getEmployeebyID and select getEmployeeDetails Query for the Query ID.

Figure 08

Click Save and finish. Now you will be able to see the deployed service under the data service list.

Figure 09

The next step is to invoke this service and check whether the data service is working. Click on the Try This Service on the StoredProcedureSample Service. It will open up a new window and you can enter an Employee ID and check the output.

Figure 10

Sample 2 - Calling a Stored Procedure with IN and OUT parameters

In Sample 1, we executed a stored procedure with only IN parameters. In Sample 2, we are going to execute a stored procedure with both IN and OUT parameters. To demonstrate IN and OUT stored procedure, we are going to use a stored procedure that takes the team name as the IN parameter and returns the number of members in the team as the OUT parameter.

Creating and populating databases

• In this sample, we are going to use the same database dss_sample and Employee table. Execute the following SQL command to create the new stored procedure.

  CREATE PROCEDURE GetMemberCount(IN teamName VARCHAR(255), OUT memberCount INT) SELECT count(EmployeeID) INTO memberCount FROM Employee WHERE Team = teamName;

Next, in the DSS management console, click on the StoredProcedureSample and click on the Edit Data Service (Wizard) and move to the Add New Query section.

Figure 11

This time we have to define the query as one with IN parameter and one with OUT parameter in the input mapping; there is one output mapping too.

Figure 12

Now add one IN parameter (teamName), one OUT parameter (memberCount) for input mapping.

Figure 13

Figure 14

Note: Member count IN/OUT type has been set to OUT.

After adding the two IN and OUT input parameters, go to Main Configuration and you will see the added input mapping.

Figure 15

Now let’s add an output mapping to get the member count value. Here too we have to define the Group by element and the Row Name. Click on the Add New Output Mapping. Select mapping type as element and fill out the following details.

Figure 16

Note: The Data Source Column Name has to be the same value as we have defined in the input parameter OUT type; in this case, the Input mapping of memberCount is the same as the output mapping’s Data Source Column Name. If you have not matched these fields, you will get an INCOMPATIBLE_PARAMETERS_ERROR in the console.

Now Add the output parameter and go to the Main Configuration. This is how it looks like after adding input and output parameter mappings.

Figure 17

Click Save and go to the next step; add a new Operation.

Figure 18

Now save the operation and click Finish to deploy the service.

As we have tested the scenario in Sample 1, we can invoke the newly created data service operation by the TryIt tool. Click on the Try This Service and you’ll get two operations; getEmployeeByID and getTeamMemberCount. Click on the getTeamMemberCount and provide a team name and invoke the service.

Figure 19

Conclusion

In this article, we have created MySQL Stored procedures and invoked them through the WSO2 Data Services Server. In Sample 1 we created a data service to invoke a stored procedure with only IN parameters and in Sample 2, we created a data service to invoke a stored procedure with both IN and OUT type parameters.