This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.

DB Report Mediator

Owned by Former user (Deleted)
Last updated: Mar 27, 2018 by Former user (Deleted)
10 min read

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.



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 NameDescription
SQLThis parameter is used to enter one or more SQL statements.
ParametersThis 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.

ActionThis 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: