Monday, August 12, 2013

Call Stored Procedure from WSO2 DSS



We have already created a Data Service for the mysql database. Now we will use Stored Procedure in Oracle and call them in our data service.
Let’s first create database and Stored Procedure.


Create a database, in this example I have used database name is “shri”.
Enter Username/Password – shri/shri
Create a table with the name of “EMPLOYEE”


If you want to insert some values in this table, then use insert command.


Now in this service we have to use Stored Procedure, so let’s create a stored procedure with the name of “EMP_INFO_INSERT”.


Click “OK” button.
And write the stored procedure for insert values in employee table as given below.

create or replace
PROCEDURE EMP_INFO_INSERT(
               p_emp_id IN EMPLOYEE.EMP_ID%TYPE,
               p_emp_name IN EMPLOYEE.EMP_NAME%TYPE,
               p_emp_add IN EMPLOYEE.EMP_ADD%TYPE,
               p_department_id IN EMPLOYEE.DEPARTMENT_ID%TYPE,
     p_designation_id IN EMPLOYEE.DESIGNATION_ID%type)
IS

BEGIN

  INSERT INTO employee (EMP_ID, "EMP_NAME", "EMP_ADD", DEPARTMENT_ID, DESIGNATION_ID )
  VALUES (p_emp_id, p_emp_name,p_emp_add, p_department_id, p_designation_id);

  COMMIT;
 END EMP_INFO_INSERT;




Compile it.
We are done with the database end, now need to create a service to call this stored procedure at DSS end.

DSS:-
Start the server from below location (double click on wso2server.bat file)
D:\...\wso2dataservices-2.5.1\bin\wso2server.bat
Server will be running on below address.
https://10.167.19.32:9443/carbon/admin/login.jsp





Enter Username/Password – admin/admin


You can see all the WebServices on clicking the of List link under the service tab.


Let’s create a new DSS service, click on create link under dataservice.
Enter “StoredProcedureExample” in dataservice textbox, click next.




Click Next.

In next page we need to add a Data Source.
Fill all required details as given below.
dataSource Id – OracleSPExample
DataSource Type – RDBMS
Database Engine - Generic
Driver Class - oracle.jdbc.driver.OracleDriver
JDBC URL - jdbc:oracle:thin:shri/shri@localhost:1521/xe
User Name – shri
Password - shri



Click on “Test Connection” Button, if connection is ok, will give successfully connected popup message otherwise give an error.


Click on “Save” button.
OracleSPExample data source is created. You can also edit or delete this data source.




If we need to create or add more data Source, repeat the above process again otherwise click “Next” button.
In next Page we have to add query which will fetch records from the database.

In this Page fill all required fields.
Query Id – selectEmployees
Data Source – OracleSPExample (It comes after creating data source in previous page)
SQl - select * from employee
Now we need give input or output mapping.
When we have to take output according to given input that we need to give here. In this example we are not giving any input criteria, just getting the output of the select query.


We have to define only output mapping here

In the “Result (Output Mapping)” block define below property.

Grouped by element – employees
Row name – employee
Row namespace - http://test.org



Click ‘Add Output Mapping‘.
Map all elements as defined in database table.
First element is added (EMP_ID).



EMP_NAME is added


Add EMP_ADD in output mapping


DEPARTMENT_ID is added


Last element in table is DESIGNATION_ID click add button to add this element.



Click ‘Main Configuration’ button, click yes when popup is open.



Result (Output Mapping) will look like this.


Click Save button.





selectEmployees query has been added. Let’s add one more query to call stored procedure.

In this Page fill all required fields.
Query Id – employees
Data Source – OracleSPExample (It comes after creating data source in previous page)
SQl - call EMP_INFO_INSERT(?,?,?,?,?)
Now we need give input or output mapping.
When we have to take output according to given input that we need to give here. In this example we are passing input elements to add data in the table



Click Add New Input Mapping button



In above image map EMP_ID, it is numeric type in database so sql type ‘Numeric’ has been selected
Click add button

Map EMP_NAME



Click add button

Next is EMP_ADD




Click add button

DEPARTMENT_ID has been mapped


Click add button
Last is designation_id 


Click Add button

Click main configuration button, click yes when popup is opened.


Input mapping will look like this (as below image)


On Clicking Save button below page should be shown.


Click Next button.


Click ‘Add New Operation’



Give addEmployee in Operation Name
Select Query Id – employees
Click Save button
Next page should be shown as below.



here we need to add one more operation
One for the adding record in the database (addEmployee)
Second is ‘selectEmployee’ which will fetch record from database.





Give Operation Name is selectEmployee
queryId – selectEmployees
click save button

Next page would look like this.



Click finish.
New service must be show in service list with the name of ‘StoredProcedureExample’.



Click ‘try this service’.


Click selectEmployee.


Whole record has been fetched from the database.


Now click addEmployee Operation.



Enter fields


Enter values and click ‘addEmployee’ button. Inserted record will be saved in the database, to show this value you can perform selectEmployee button again.