Tuesday, December 15, 2015

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.