Retrieve multiple rows from a database in WSO2 ESB using WSO2 DSS.

When you want to retrieve multiple rows from a database using WSO2 ESB, you will not be able to use the dblookup mediator as it only supports retrieving a single row. In a scenario like this we can use WSO2 DSS.

When you want to run two servers at the same time you have to set the offset in one server. Otherwise both servers will try to be up in the same port. You can do it by editing the carbon.xml file of the ESB or DSS. Go to [product-home]/repository/conf/carbon.xml. Then set the offset to ‘2’. (You can use any number you want). If you set the offset to 2,Server will be up in the port 9445.

Steps

Create a database in mysql server. And define a table including data you want. Here I have created a database named ‘company’ and a table named ‘employee’.

Configure DSS

Start the WSO2 DSS. Then go to the url of the serve and log into the server using username password. (username:admin password:admin).

Click on ‘Create’ under ‘Data Service’. You will be redirected to a page like the image shown below. You can simply give a Data Service Name and proceed. Here I have given the name as ‘Employee’.

When you click ‘Next’ you will see a screen like this. Here we have to add a new data source. Click on ‘Add New Datasource’. Give a datasource id and select the ‘Datasource Type’ as ‘RDBMS’ and ‘Database Engine’ as ‘MySQL’. Give the ‘Driver Class’ as ‘com.mysql.jdbc.Driver’. Give the ‘URL’ as ‘jdbc:mysql://localhost:3306/[database-name]’.You can give the name of the database you created. Then give the username and password of your database.

Save and click ‘Next’.

Click on ‘Add New Query’. Give a query id and select the Datasource you created. Write the sql query including the columns you want to retrieve from database. And click on ‘Generate Response’. It will automatically generate the Result(Output Mapping).

Then save the query and click ‘Next’.

Click on ‘Add New Operation’. Give an operation name and select the query Id of the query.

Save and click ‘Next’ and ‘Finish’. Now you have created the Data Service in DSS. When you refresh the page you will see the service you created.

Click on ‘Try this Service’ and it will redirect you to another page. You can click on ‘Send’ button to see the results you get after retrieving values from database.

If you know how to write the XML configuration you can directly edit the XML without doing the above mentioned steps using the UI. Here is the final XML you get after creating the data service.

<?xml version="1.0" encoding="UTF-8"?>
<data name="Employee" transports="http https local">
   <config enableOData="false" id="EmployeeDetails">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/company</property>
      <property name="username">root</property>
   </config>
   <query id="getEmployees" useConfig="EmployeeDetails">
      <sql>SELECT name,emp_id,department from employee</sql>
      <result element="Entries" rowName="Entry">
         <element column="name" name="name" xsdType="string" />
         <element column="emp_id" name="emp_id" xsdType="string" />
         <element column="department" name="department" xsdType="string" />
      </result>
   </query>
   <operation name="retrieveEmployeeDetails">
      <call-query href="getEmployees" />
   </operation>
</data>

Now we are done with the configuration of DSS.

Configure ESB

Now we will move on to configure ESB. For you to fetch these data to ESB you have to  define an endpoint in ESB. Here I will define a HTTP endpoint. Go back again to DSS. Then click on the data service you created. Then copy the highlighted endpoint as shown in the image. (You have to copy the http endpoint. Not the https one).

Go to ESB to create the HTTP endpoint. Go to ‘Endpoints’ , ‘Add Endpoint’ and select ‘HTTP Endpoint’. Give a name. To define the URI template, paste the endpoint you copied from the DSS data service. And then add the operation name at the end of the URL like this.

http://192.168.1.4:9763/services/Employee/retrieveEmployeeDetails

Create a proxy service in ESB to retrieve data. You can create a proxy and write the XML configuration like this.

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="retrieve" transports="https,http" statistics="disable" trace="disable" startOnLoad="true">
   <target>
      <inSequence>
         <payloadFactory media-type="xml">
            <format>
               <dat:retrieveEmployeeDetails xmlns:dat="http://ws.wso2.org/dataservice" />
            </format>
            <args />
         </payloadFactory>
         <property name="Content-Type" value="application/xml" scope="transport" />
         <call>
            <endpoint key="retrieveEmpDetails" />
         </call>
         <log level="full" />
      </inSequence>
   </target>
   <description />
</proxy>

In this proxy service we define a payload factory mediator before calling the endpoint to give the reference to the namespace of DSS. Inside that payload we can define the namespace of the operation(retrieveEmployeeDetails) of the data service we created like this.

Since we get a XML payload from the data service we invoke in DSS we can set the Content-Type property value to application/xml and the scope to transport.This is optional.

Then we can call the endpoint we created in ESB like this, using the call mediator.

<call>

          <endpoint key="retrieveEmpDetails"/>

</call>

When you run this proxy service you can see the XML payload with the retrieved values from database like this.

[WSO2 ESB] Retrieving values from a payload excluding namespaces into a property.

Assume that we want to retrieve some elements from the payload excluding the namespaces.

We can do this in different ways.

1. If it is a payload defined by ourselves, we can define the payload with an empty namespace.

EX:

<Entries xmlns= "">

                 <Entry>

                    <name>Olivia</name>

                    <userId>1</userId>

        <age>25</age>

                 </Entry>

</Entries>

And we can write a XPATH expression to retrieve the required element into a property like this.

<property name="nameOfThePerson" expression="//Entries/Entry/name"/>

2. If it is a payload we are getting from an outside source, and it contains a namespace defined in the payload.

Here we have to define the namespace reference in our property mediator like this to retrieve only the value of the element without the namespace.

EX:

If the payload we are getting is like this,

<Entries xmlns= "http://wso2.samples.com">

                 <Entry>

                    <name>Olivia</name>

                    <userId>1</userId>

        <age>25</age>

                 </Entry>

</Entries>

And if we want to retrieve the value of the age element into a property.

This can be done in two ways,

i.) We can define the property with a reference to the namespace like this,

<property xmlns:ns="http://wso2.samples.com"

                  name="age"

                  expression="//ns:Entries/ns:Entry/ns:age"/>

ii.) This can be done without defining the namespace in property like this.

        <property name="age" expression="//*[local-name()='age']"/>

This expression will also retrieve the same age value. And here you don’t want to add the namespace reference. So it becomes much more simpler and clearer than the previous one.

Sample proxy service

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="removeNameSpaces" transports="https,http" statistics="disable" trace="disable" startOnLoad="true">
   <target>
      <inSequence>
         <payloadFactory media-type="xml">
            <format>
               <Entries xmlns="http://wso2.samples.com">
                  <Entry>
                     <name>Olivia</name>
                     <userId>1</userId>
                     <age>25</age>
                  </Entry>
               </Entries>
            </format>
            <args />
         </payloadFactory>
         <property xmlns:ns="http://wso2.samples.com" name="age" expression="//ns:Entries/ns:Entry/ns:age" />
         <property name="userId" expression="//*[local-name()='userId']" />
         <log level="custom">
            <property name="age" expression="get-property('age')" />
            <property name="userId" expression="get-property('userId')" />
         </log>
      </inSequence>
   </target>
   <description />
</proxy>

                               

Sample output

[WSO2 ESB] Saving a XML payload as a property and applying XPATH expressions.

Assume that you need to retrieve some values from a XML payload using a XPATH expression.

Let’s assume that this is the payload you are getting from the service (Proxy service or API) you invoke from WSO2 ESB. And you need to retrieve the userId of a particular person when you give the name of that person.

<Entries>

                 <Entry>

                    <name>Olivia</name>

                    <userId>1</userId>

                 </Entry>

                 <Entry>

                    <name>Abby</name>

                    <userId>2</userId>

                 </Entry>

                 <Entry>

                    <name>Bella</name>

                    <userId>3</userId>

                 </Entry>

                 <Entry>

                    <name>Hannah</name>

                    <userId>4</userId>

                 </Entry>

                 <Entry>

                    <name>Sabrina</name>

                    <userId>5</userId>

                 </Entry>

              </Entries>

ESB has property type called “OM” to save property values in message context as XML. This is useful when the expressions we write are associated with XML. For example if we want to write a XPATH expression by referring a property, that property has to be a XML object.  

This is the way of saving the above payload with the data type ‘OM’.

<property name="entries" expression="//Entries" type="OM"/>

Here is the proxy service.

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse" name="test" transports="https,http" statistics="disable" trace="disable" startOnLoad="true">
   <target>
      <inSequence>
         <call>
            <endpoint key="EP" />
         </call>
         <property name="entries" expression="//Entries" type="OM" />
         <property name="userId" expression="$ctx:entries//Entry/name[text()='Bella']//following-sibling::*[1]/text()" />
         <log level="custom">
            <property name="userId" expression="get-property('userId')" />
         </log>
      </inSequence>
   </target>
   <description />
</proxy>

                               

                            

I have created an endpoint in ESB named ‘EP’ which returns the above mentioned payload.

 <property name="userId"

   expression="$ctx:entries//Entry/name[text()='Bella’]//following-sibling::*[1]/text()"/>

From this property we can retrieve the userId element which is the following sibling of the name element which has the value ‘Bella’.

From the log mediator this will return the value as 3.

If we want to pass the name as a parameter without hard coding it in the XPATH expression we can define the property like this.

 <property name="userId"     expression="$ctx:entries//Entry/name[text()=$ctx:nameOfThePerson]//following-sibling::*[1]/text()"/>

In here message context must have a property named ‘nameOfThePerson’. We can define a property with the value of the name we want to retrieve and keep it in the message context. Then we can refer the message context and use it in XPATH expression.

You don’t need to save the payload as this if you want to get the values of elements just after receiving the payload(Just after calling the endpoint). You can simply write the XPATH expression to retrieve those values. This method is useful when you want to refer the payload after a while in your sequence or API or proxy service when the message context does not contain the received payload.