How to call stored procedure from dbreport in WSO2 ESB

dbreport in WSO2 ESB has capability to execute sql queries, in a same way we can call stored procedure from dbreport mediator. To see how dbreport mediator works for normal query click here.

In below example, I have created a stored procedure which updates the employee records (basically address) based on given Employee Id.

Table: EMP_DETAILS

Table Data:

Stored Procedure:

create or replace PROCEDURE UPDATE_EMP_DETAILS

(

  EMP_ID       IN VARCHAR2,

  EMP_ADDRESS  IN VARCHAR2

)

AS

BEGIN

  update EMP_DETAILS set EMP_ADD_V=EMP_ADDRESS where EMP_ID_N=EMP_ID;

END UPDATE_EMP_DETAILS;

ESB Synapse Code:

<proxy name="DBreportProc" transports="http" startOnLoad="true" trace="disable" statistics="enable">

<target inSequence="DBreportProc_IN" outSequence="CommonSequence_OUT" faultSequence="DBreportProc_Fault"/>

<publishWSDL key="DBreportProc_wsdl"/>

</proxy>

<localEntry key="DBreportProc_wsdl" src="file:repository/conf/dbreportProc/DBReportSP.wsdl"/>

<sequence name="DBreportProc_Fault">

<log level="full"/>

<payloadFactory>

<format>

<shr:UpdateEmployeeResponse xmlns:shr="http://shriwithjava.blogspot.co.nz/">

<shr:ResultCode>-1</shr:ResultCode>

<shr:ResultDescription>Exception</shr:ResultDescription>

</shr:UpdateEmployeeResponse>

</format>

</payloadFactory>

<property name="RESPONSE" value="true"/>

<header name="To" action="remove"/>

<send/>

<drop/>

</sequence>

<sequence name="DBreportProc_IN">

<property xmlns:shr="http://shriwithjava.blogspot.co.nz/" name="EmpId" expression="//shr:EmpId/text()"/>

<property xmlns:shr="http://shriwithjava.blogspot.co.nz/" name="EmpAddress" expression="//shr:EmpAddress/text()"/>

<dbreport>

<connection>

<pool>

<password>SYSTEM</password>

<user>SYSTEM</user>

<url>jdbc:oracle:thin:SYSTEM/SYSTEM@localhost:1521/xe</url>

<driver>oracle.jdbc.OracleDriver</driver>

<property name="autocommit" value="false"/>

</pool>

</connection>

<statement>

<sql><![CDATA[call UPDATE_EMP_DETAILS (?,?)]]></sql>

<parameter expression="synapse:get-property('EmpId')" type="VARCHAR"/>

<parameter expression="synapse:get-property('EmpAddress')" type="VARCHAR"/>

</statement>

</dbreport>

<payloadFactory>

<format>

<shr:UpdateEmployeeResponse xmlns:shr="http://shriwithjava.blogspot.co.nz/">

<shr:ResultCode>0</shr:ResultCode>

<shr:ResultDescription>Success</shr:ResultDescription>

</shr:UpdateEmployeeResponse>

</format>

</payloadFactory>

<property name="RESPONSE" value="true"/>

<header name="To" action="remove"/>

<log level="full" separator=","/>

<send/>

<drop/>

</sequence>

Call Web Service:

The above service we have created now needs to be called from SOAP UI to see whether it’s working fine or not.

In above request I am trying to change my address from NOIDA to RAEBARELI city.

After getting success response, now check in table data.

So in EMP_DETAILS table data has been changed for EMP ID 111.