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.

By default, the maximum number of active transactions is 50. To override this, create a file named transaction.properties by including the following property and add it to the <EI_HOME>/lib directory:

com.atomikos.icatch.max_actives=-1

Specifying the value as -1 allows unlimited transactions. Change the value accordingly to limit the number of active transactions based on your environment and the concurrency level of the service.

UI Configuration

The UI 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.

Note

You can configure the mediator using XML. Click switch to source view in the Mediator window.

Example

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>update company set price=? where name =?</sql>
        <parameter expression="//m0:return/m1:last/child::text()"
                   xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="DOUBLE"/>
        <parameter expression="//m0:return/m1:symbol/child::text()"
                   xmlns:m0="http://services.samples" xmlns:m1="http://services.samples/xsd" type="VARCHAR"/>
    </statement>
</dbreport>

Samples

For more examples of the DB Report mediator, see:

Sample 361: Introduction to DB Report Mediator
Sample 362: DB Report and DBLookup Mediators Together
Sample 363: Reusable Database Connection Pools
Sample 271: File Processing (moves files into a database using the VFS transport and the DB Report mediator)