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>