How to use Oracle Ref Cursor in WSO2 DSS

Ref cursor in oracle is a data type and WSO2 DSS support this data type to be called. In this blog we will see that how ref cursor is called from DSS, for this I have created a table in oracle which holds the employee details and also created a stored procedure having a cursor into that and will get the employee details based on an input employee id.

Table: EMP_DETAILS

Now insert some data into this table so that from DSS service we can get employee detail based on employee id.

In this table I have inserted 3 records as above and now create a stored procedure which has ref cursor.

Stored Procedure: GETEMPLOYEE

create or replace PROCEDURE GETEMPLOYEE(

EMP_ID  IN VARCHAR2,

RESULT_CODE  OUT VARCHAR2,

EMPLOYEE_DETAILS_RESULT OUT SYS_REFCURSOR

) AS

BEGIN

  OPEN EMPLOYEE_DETAILS_RESULT FOR 

  SELECT * FROM EMP_DETAILS WHERE EMP_ID_N=EMP_ID;

  RESULT_CODE:='0';

  EXCEPTION WHEN OTHERS THEN

  RESULT_CODE:='-1';

END GETEMPLOYEE;

This procedure will be called from the DSS server. I am putting only DSS (.dbs) file code in below section. If you want to complete this section from the UI you can follow this blog which has the details that how DSS is created through UI.

DSS (.dbs) file:

 <data name="EmployeeDatailsService" enableBatchRequests="false" enableBoxcarring="false" serviceStatus="active">  
   <config id="EmployeeDS">  
    <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>  
    <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:SYSTEM/SYSTEM@localhost:1521/xe</property>  
    <property name="org.wso2.ws.dataservice.user">SYSTEM</property>  
    <property name="org.wso2.ws.dataservice.password">SYSTEM</property>  
    <property name="org.wso2.ws.dataservice.minpoolsize"></property>  
    <property name="org.wso2.ws.dataservice.maxpoolsize"></property>  
    <property name="org.wso2.ws.dataservice.validation_query"></property>  
   </config>  
   <query id="EmployeeDatailsQuery" useConfig="EmployeeDS">  
    <sql>call GETEMPLOYEE(?,?,?)</sql>  
        <result element="EmployeesDetails" defaultNamespace="http://shriwithjava.blogspot.co.nz/">    
            <element name="ResultCode" column="RESULT_CODE" xsdType="xs:string" />      
      <element name="EmpID" column="EMP_ID_N" xsdType="xs:string" />       
      <element name="Name" column="EMP_NAME_V" xsdType="xs:string" />       
      <element name="Address" column="EMP_ADD_V" xsdType="xs:string" />     
            <element name="Country" column="COUNTRY_V" xsdType="xs:string" />   
    </result>    
        <param name="EMP_ID" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1" />   
    <param name="RESULT_CODE" paramType="SCALAR" sqlType="STRING" type="OUT" ordinal="2" />   
       <param name="EMPLOYEE_DETAILS_RESULT" sqlType="ORACLE_REF_CURSOR" type="OUT" ordinal="3"/>  
   </query>  
   <operation name="getEmployeeDetails">  
    <description></description>  
    <call-query href="EmployeeDatailsQuery" >  
           <with-param name="EMP_ID" query-param="EMP_ID" />   
           </call-query>  
   </operation>  
 </data> 

Testing from Soap UI:

Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:shr="http://shriwithjava.blogspot.co.nz/">

   <soapenv:Header/>

   <soapenv:Body>

      <shr:getEmployeeDetails>

         <shr:EMP_ID>111</shr:EMP_ID>

      </shr:getEmployeeDetails>

   </soapenv:Body>

</soapenv:Envelope>

Response:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">

   <soapenv:Body>

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

         <ResultCode>0</ResultCode>

         <EmpID>111</EmpID>

         <Name>SHRI</Name>

         <Address>RAEBARELI</Address>

         <Country>INDIA</Country>

      </EmployeesDetails>

   </soapenv:Body>

</soapenv:Envelope>

How to call Stored Procedure with return value from dblookup/dbreport in WSO2 ESB

I have tried many times to call stored procedure with return value from WSO2 ESB but unfortunately didn’t get success, later on I realized that there is no such functionality to call SP with return parameter from dblookup/dbreport (Based on my experience).

The reason could be like to give more support WSO2 DSS.

However you can call stored procedure which does not returns any value. I have written an example for this may help you, click here to get details.

Other option to create a custom mediator and call stored procedure which returns value, click here to see how custom mediator works.

And the best option is to use DSS and in DSS you can run any complex stored procedure and it’s very easy to use, click here to see how to Parameterized Stored procedure is called from WSO2 DSS.

Syntax:

<dblookup xmlns="http://ws.apache.org/ns/synapse">

<connection>

<pool>

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

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

<user>SYSTEM</user>

<password>SYSTEM</password>

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

</pool>

</connection>

<statement>

<sql>call UPDATE_EMP_DETAILS (?,?)</sql>

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

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

</statement>

</dblookup>

If you have any example or solution to call return value Stored Procedure from dblookup, please share with us, your response will be highly appreciated. 

How to create Data Source in WSO2 ESB and use in dblookup/dbreport mediator

As we have already seen in the previous blogs that, how we can use dblookup and dbreport mediator. We have to create a database connection string to use database from dblookup and dbreport. Instead of using connection string, we can create a data source so that connection string cannot be written again and again for every dblookup and dbreport mediator.

To create a data source just logic to ESB console UI.

In left hand side you can see a Data Source link highlighted in Yellow. Once you click on this you can see at right hand side there is an option to create a new Data Source. Click on this and you will see below window.

In above screen, we are trying to create an oracle data source. Complete the required field and then check the connection by clicking Test Connection. A popup will appear saying that connection is healthy otherwise show you an error.

Now use the same data source connection string as below example.

Synapse Code:

<dbreport xmlns="http://ws.apache.org/ns/synapse">

<connection>

<pool>

<dsName>OracleDatasource</dsName>

</pool>

</connection>

<statement>

<sql>call UPDATE_EMP_DETAILS (?,?)</sql>

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

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

</statement>

</dbreport>

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.

How to use send mediator in WSO2 ESB

Send mediator is used to send current message to a particular given endpoint or basically out of the synapse code. If you are not using any endpoint and also using send mediator then same message is send to the initiator because it also makes a copy of the current message.

To send a message to a particular destination this mediator must be used even you want same message as a reply.

Syntax:

When no Address:

<send/> syntax is used when no endpoint is there, if this is written in out sequence of the service, means same message will be replied back to the requester.

With Endpoint Address:

<send>

   (endpointref | endpoint)+

</send>

Above syntax will send current message to defined endpoint and same message can be used for the further logic.

With Service Chaining:

<send receive="ManagerDetailSEQ">

    <endpoint key=" ManagerDetailsEPR"/>

</send>

Above syntax is used when we need to have service chaining in our composite service logic. Above syntax shows that current message will be directed to the ManagerDetails endpoint, for which a particular key has been defined and ManagerDetailSEQ will be executed. So message can be captured for the logic in this ManagerDetailSEQ sequence.

Use from UI Configuration:

Send mediator can be found inside of Core option category as mentioned in below image.

One you click on the send mediator, you will get the below screen in which you have to provide some information.

None: As already mentioned above, if you don’t select anything then it means same message will be replied back to initiator.

Define Inline: In this option you can add a defined endpoint, there is an Add option appears in which you can add an endpoint in which you have to send the message.

Pick from Registry: In this option you can choose an endpoint from governance registry.

Receiving sequence Type: Just after the sending the message to the endpoint, which sequence you want to execute, you can define.

In the latest version you can get one more option like mentioned below:

Build Message before sending:

If you put Yes in it, it stores the message in memory and then send to endpoint otherwise does not store. To performance point of view select it to NO.

Example:

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

        <target inSequence="DSS_EmployeeDetailDataService_IN" outSequence="CommonSequence_OUT" faultSequence="DSS_EmployeeDetailDataService_Fault"/>

        <publishWSDL key="DSS_EmployeeDetailDataService_wsdl"/>

    </proxy>

                           <localEntry key="DSS_EmployeeDetailDataService_wsdl" src="file:repository/conf/employee/EmployeeDetailDataService.wsdl"/>

                           <sequence name="DSS_EmployeeDetailDataService_IN">

        <log level="full"/>

        <script language="js">java.lang.Thread.sleep(50000);</script>

        <cache id="GetEmpDetailCache" scope="per-host" collector="false" hashGenerator="org.wso2.caching.digest.DOMHASHGenerator" timeout="20" maxMessageSize="500">

            <implementation type="memory" maxSize="1000"/>

        </cache>

        <send>

            <endpoint key="DSS_EmployeeDetailDataService_EPR"/>

        </send>

    </sequence>

                           <endpoint name="DSS_EmployeeDetailDataService_EPR">

        <address uri="http://10.251.162.192:9763/services/EmployeeDataService1">

            <timeout>

                <duration>10000</duration>

                <responseAction>fault</responseAction>

            </timeout>

            <suspendOnFailure>

                <errorCodes>101500,101501,101506,101507,101508</errorCodes>

                <progressionFactor>0.0</progressionFactor>

            </suspendOnFailure>

            <markForSuspension>

                <errorCodes>101504,101505</errorCodes>

            </markForSuspension>

        </address>

    </endpoint>

                            <sequence name="CommonSequence_OUT">

        <log level="full"/>

        <cache id="GetEmpDetailCache" scope="per-host" collector="true"/>

        <send/>

    </sequence>

How to create a qualified WSDL

When we have to create a WSDL then it can be qualified or normal WSDL. There is only one property which defines the qualified WSDL or normal WSDL. For example, if we get a WSDL from the WSO2 DSS server it gives you a normal WSDL and the structure of the WSDL is as mention below.

Attribute in WSDL:

<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://shriwithjava.blogspot.co.nz/managerdetails/">

elementFormDefault can be qualified or unqualified.

Unqualified WSDL’s Structure:

If you see below WSDL image we have kept elementFormDefault as unqualified.

WSDL:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:man="http://shriwithjava.blogspot.co.nz/managerdetails/">

   <soapenv:Header/>

   <soapenv:Body>

      <man:ManagerDetailsRequest>

         <Id>?</Id>

         <Name>?</Name>

         <Address>?</Address>

         <Departments>

            <!--1 or more repetitions:-->

            <DepartmentDetails>

               <DepartmentName>?</DepartmentName>

               <NoOfPeople>?</NoOfPeople>

               <OfficeLocation>?</OfficeLocation>

            </DepartmentDetails>

         </Departments>

      </man:ManagerDetailsRequest>

   </soapenv:Body>

</soapenv:Envelope>

If you see the above example, namespace is coming only in operation but not in all elements.

If we create it as a qualified WSDL then WSDL will as below. Load this in SOAP UI, namespace will be there in all the elements.

Qualified WSDL’s Request Structure:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:man="http://shriwithjava.blogspot.co.nz/managerdetails/">

   <soapenv:Header/>

   <soapenv:Body>

      <man:ManagerDetailsRequest>

         <man:Id>?</man:Id>

         <man:Name>?</man:Name>

         <man:Address>?</man:Address>

         <man:Departments>

            <!--1 or more repetitions:-->

            <man:DepartmentDetails>

               <man:DepartmentName>?</man:DepartmentName>

               <man:NoOfPeople>?</man:NoOfPeople>

               <man:OfficeLocation>?</man:OfficeLocation>

            </man:DepartmentDetails>

         </man:Departments>

      </man:ManagerDetailsRequest>

   </soapenv:Body>

</soapenv:Envelope>

How to use Fault Mediator in WSO2 ESB

When there is a requirement like to transform current message to fault message we can use fault mediator. This is the same as we have in throw exception in java. Fault mediator converts current message to fault message. You can send this message to client by using the send mediator. You can create the fault message as a SOAP 1.1, SOAP 1.2, or plain-old XML (POX) fault.

In below example I have created a same proxy which returns a transaction number but in IN sequence we are trying to implement fault message.

We have created FaultMediatorTest service and in IN sequence (FaultMediatorTest_IN) we will apply fault mediator. Go to edit sequence.

You can see Fault mediator comes inside the transform category because it is used to transform the message into fault message.

Once you click on Fault mediator you will get the above screen, in which you have to pass some parameter.

• Version: There are only 2 versions in ESB 4.0.3 but in latest version you can see one more option POX.

• Code: Basically it’s a fault code value you pass by selecting dropdown.
• Version Mismatch: it shows the version mismatch of the SOAP
• Must Understand: It’s a SOAP “Must understand” error, if SOAP is not there
• Client: To show client side error.
• Server: Server side error.
• Value or Expression: If it’s a hard coded value you need to pass select Value or for variable you can select expression which you need to provide.
• Fault String: detailed fault string, here we have chosen the ERROR_MESSAGE expression get error message.
• Detail:  You can pass any message details which want to show.
• ROLE: This option appear when you select version as SOAP1.2, you can provide SOAP role name.
• Node: This option appears when you select version as SOAP1.2 you can provide SOAP Node name.

Synapse Code:

      <proxy name="FaultMediatorTest" transports="http" startOnLoad="true" trace="disable" statistics="enable">  
     <target inSequence="FaultMediatorTest_IN" faultSequence="CommonFaultHandler"/>  
     <publishWSDL key="FaultMediatorTest_WSDL"/>  
   </proxy>  
      <localEntry key="FaultMediatorTest_WSDL" src="file:repository/conf/makefault/resources/proxy/makefault.wsdl"/>  
   <sequence name="FaultMediatorTest_IN">  
     <makefault version="soap12">  
       <code xmlns:soap12Env="http://www.w3.org/2003/05/soap-envelope" value="soap12Env:MustUnderstand"/>  
       <reason xmlns:ns="http://org.apache.synapse/xsd" xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope" xmlns:ns3="http://org.apache.synapse/xsd" expression="get-property('ERROR_MESSAGE')"/>  
       <node/>  
       <role/>  
                <detail>This is a simple test for Fault Mediator</detail>  
     </makefault>  
     <send/>  
   </sequence>  

Result: