Use DBReport Mediator in WSO2 ESB

DBReport mediator is same as DBLookup mediator with having small difference, dbreport mediator can write something into the database. You can use insert or update query in dbreport mediator.

UI Configuration:

This is the UI screen of the DB Report Mediator where you can put some required value and use it. You can even write this code to synapse itself.

In UI you have to pass database details to make connection like Driver call, URL, user name and the database password. You can use internal connection or external connection in that. If you have created a datasourse that can be used into that.

In above screen you need to choose the Connection info, Datasource type etc.

In the above screen you need to pass sql statement which you need to pass to insert information in database or update the database and the same way you have to pass required parameter and its type.

Synapse Code:

<dbreport>

        <connection>

                <pool>

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

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

                        <user>USER_XXX</user>

                        <password>PWD_XXX</password>

                        <url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST=<HOST_NAME>)(PORT=<PORT_NO>)) (ADDRESS=(PROTOCOL=TCP)(HOST=<HOST_NAME>)(PORT=<PORT_NO>))(FAILOVER=on) (LOAD_BALANCE=on)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=<SERVICE_NAME>) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) )))</url>

                </pool>

        </connection>

        <statement>

                <sql><![CDATA[INSERT INTO EMP_DETAILS (EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DOB) VALUES (?, ?, ?, ?)]]></sql>

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

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

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

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

        </statement>

</dbreport>

In above example I have used Oracle database so given driver and other required details of the oracle database, this is used to insert the employee detail into EMP_DETAILS table.