DBReport Mediator

The DBReport Mediator is very similar to the DBLookup Mediator, but writes information to a Database, using the specified insert SQL statement.

Syntax

<dbreport>
   <connection>
     <pool>
      (
        <driver/>
        <url/>
        <user/>
        <password/>

        <dsName/>
        <icClass/>
        <url/>
        <user/>
        <password/>
      )
        <property name="name" value="value"/>*
     </pool>
   </connection>
   <statement>
      <sql>insert into something values(?, ?, ?, ?)</sql>
      <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
   </statement>+
</dblreport>

UI Configuration

Connection Information of the DBReport Mediator can be chosen as:

Pool
Data Source
- External
- Carbon Datasource

It is possible to add #Properties and #SQL Statements to the DBReport Mediator as well.

Connection Information - Pool

Field descriptions of the DBReport Mediator as shown in the screenshot above:

Use Transaction - (Yes/No)
Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
Driver - Database driver.
Url - JDBC URL of the database that data will be looked up from.
User - Username used to connect to the database.
Password - Password used to connect to the database.

Connection Information - Data Source

Data Source Type - External

Field descriptions of the DBReport Mediator as shown in the screenshot above:

Use Transaction - (Yes/No)
Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
Database Type:
- External
- Carbon Datasource
Initial Context
Data Source Name
Url - JDBC URL of the database that data will be looked up.
User - Username used to connect to the database.
Password - Password used to connect to the database.

Connection Information - Data Source

Data Source Type - Carbon Datasource

Field descriptions of the DB Report Mediator as shown in the screenshot above:

Use Transaction - (Yes/No)
Connection Information - Specifies whether the connection is taken from a connection pool or from a data source.
Database Type:
- External
- Carbon Datasource
Data Source Name
Tip

You can view the available data sources by clicking the "Load Data Sources" icon.

Adding Properties

Property field descriptions:

Name - Name of the property.
Value - The value of the property.
Action - Deletes the property.

Name	Value
autocommit	true / false
isolation	Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE
initialsize	int
maxactive	int
maxidle	int
maxopenstatements	int
maxwait	long
minidle	int
poolstatements	true / false
testonborrow	true / false
testwhileidle	true / false
validationquery	String

Adding SQL Statements

Tip

The SQL statement may contain parameters which can be specified as values or XPath expressions.
Parameters can be any valid SQL type. Only the first row of a result set will be considered while the others are ignored.

SQL- One or more SQL Statements.
Parameters- Specify how the values of parameters in the SQL will be calculated. A value can be a static literal text and can be calculated at runtime based on the given expression.
- Parameter Type
  - CHAR
  - VARCHAR
  - NUMERIC
  - DECIMAL
  - BIT
  - TINYINT
  - SAMLLINT
  - INTEGER
  - BIGINT
  - REAL
  - DOUBLE
  - DATE
  - TIME
  - TIMESTAMP
- Property Type
  - Value
  - Expression
- Value/Expression
- Namespace - Gives possibility to choose an Expression from the list.

Tip

You can click this link to add namespaces if you are providing an expression. You will be provided another panel named "Namespace Editor," where you can provide any number of namespace prefixes and URL that you have used in the XPath expression.

- Action - Delete the parameter.

Note

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

Example

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

This example demonstrates simple database write operations. The DBReport Mediator writes to a table using the message's details. It works the same way as the DBLookup Mediator. In this sample DBReport Mediator is used for updating the stock price of the company using the last quote value which is calculated by evaluating an XPath against the response message.