DB Report Mediator

The DB Report Mediator is similar to the DBLookup Mediator. The difference between the two mediators is that the DB Report mediator writes information to a database using the specified insert SQL statement.

The DB Report mediator is a content-aware mediator.

Syntax

The syntax of the DB Report mediator changes depending on whether you connect to the database using a connection pool, or using a data source. Click on the relevant tab to view the required syntax.

Configuration

The configuration of the DBQuery mediator changes depending on whether you connect to the database using a connection pool, or using a data source. Click on the relevant tab to view the required UI.

Adding SQL statements to the DB Report Mediator

If you click Add Statement , the page will be expanded to display the following parameters.

Parameter Name	Description
SQL	This parameter is used to enter one or more SQL statements.
Parameters	This section is used to specify how the values of parameters in the SQL will be determined. A parameter value can be static or calculated at runtime based on a given expression.
Parameter Type	The data type of the parameter. Possible values are as follows. CHAR VARCHAR LONGVARCHAR NUMERIC DECIMAL BIT TINYINT SAMLLINT INTEGER BIGINT REAL DOUBLE DATE TIME TIMESTAMP
Property Type	This determines whether the parameter value should be a static value or calculated at run time via an expression. Value: If this is selected, a static value would be considered as the property value and this value should be entered in the Value/Expression parameter. Expression: If this is selected, the property value will be determined during mediation by evaluating an expression. This expression should be entered in the Value/Expression parameter.
Value/Expression	This parameter is used to enter the static value or the XPath expression used to determine the property value based on the option you selected for the Property Type parameter. You can click NameSpaces to add namespaces if you are providing an expression. Then the Namespace Editor panel would appear where you can provide any number of namespace prefixes and URLs used in the XPath expression.
Action	This allows you to delete a parameter.

Examples

Simple database write operation

This example demonstrates simple database write operations. The DB Report mediator writes to a table using the details of the message. It updates the stock price of the company using the last quote value, which is calculated by evaluating an XPath expression against the response message.

<dbreport xmlns="http://ws.apache.org/ns/synapse">
    <connection>
        <pool>
            <driver>org.apache.derby.jdbc.ClientDriver</driver>
            <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
            <user>esb</user>
            <password>esb</password>
        </pool>
    </connection>
    <statement>
        <sql><![CDATA[update company set price=? where name =?]]></sql>
        <parameter expression="//m0:return/m1:last/child::text()" type="DOUBLE" xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"/>
        <parameter expression="//m0:return/m1:symbol/child::text()" type="VARCHAR" xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"/>
    </statement>
</dbreport>

Performing a database write operation within a transaction

In this example, <transaction action="new"/> is a Transaction Mediator configuration that starts a new transaction. The DBReport mediator configuration performs a few write operations incuding deleting records when the name matches a specific value derived via an expression as well as a few insertions. Once the database operations are complete, they are committed via <transaction action="commit"/>, which is another Transaction Mediator configuration.

<definitions xmlns="http://ws.apache.org/ns/synapse">
   <sequence name="myFaultHandler">
        <log level="custom">
            <property name="text" value="** Rollback Transaction**"/>
        </log>
        <transaction action="rollback"/>
        <send/>
    </sequence>
    <sequence name="main" onError="myFaultHandler">
        <in>
            <send>
                <endpoint>
                    <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
                </endpoint>
            </send>
        </in>
         <out>
            <transaction action="new"/>
            <log level="custom">
                <property name="text" value="** Reporting to the Database EIdb**"/>
            </log>
            <dbreport useTransaction="true" xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <dsName>java:jdbc/XADerbyDS</dsName>
                        <icClass>org.jnp.interfaces.NamingContextFactory</icClass>
                        <url>localhost:1099</url>
                        <user>EI</user>
                        <password>EI</password>
                    </pool>
                </connection>
                <statement>
                     <sql>delete from company where name =?</sql>
                     <parameter expression="//m0:return/m1:symbol/child::text()"
                       xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd"
                                 type="VARCHAR"/>
                </statement>
            </dbreport>
            <log level="custom">
                <property name="text" value="** Reporting to the Database EIdb1**"/>
            </log>
            <dbreport useTransaction="true" xmlns="http://ws.apache.org/ns/synapse">
                <connection>
                    <pool>
                        <dsName>java:jdbc/XADerbyDS1</dsName>
                        <icClass>org.jnp.interfaces.NamingContextFactory</icClass>
                        <url>localhost:1099</url>
                        <user>EI</user>
                        <password>EI</password>
                    </pool>
                </connection>
                <statement>
                    <sql>INSERT into company values (?,'c4',?)</sql>
                    <parameter expression="//m0:return/m1:symbol/child::text()"
         xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               type="VARCHAR"/>
                    <parameter expression="//m0:return/m1:last/child::text()"
         xmlns:m1="http://services.samples/xsd" xmlns:m0="http://services.samples"
                               type="DOUBLE"/>
                </statement>
            </dbreport>
            <transaction action="commit"/>
            <send/>
        </out>
    </sequence>
</definitions>

Samples

For more examples of the DB Report mediator, see:

/wiki/spaces/EI6xx/pages/49611225
/wiki/spaces/EI6xx/pages/49611230
/wiki/spaces/EI6xx/pages/49611235
/wiki/spaces/EI6xx/pages/49611071 (moves files into a database using the VFS transport and the DB Report mediator)