DBReport Mediator

The dbreport mediator is very similar to the dblookup mediator, but writes information to a Database, using the specified insert SQL statement. For configuration information about db mediators , refer dblookup mediator guide

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

Figure 1: DBReport options

DBReport mediator options field descriptions,

  • Connection Information - Specify 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.
  • User - Username that's used to connect to the database.
  • Password - Password used to connect to the database.

Data sources

Carbon Datasources

If existing data source is choosen, clicking "Load Data Sources" will give the available datasource. Then a data source can be selected.

Figure 2: Carbon Datasources

External Datasources

Like in Pool connection data source configuration properties should be specified.

Figure 3: External Datasources

Adding properties

Properties can be added to the DBReport Mediator by clicking Add Property,

Figure 4: Adding a property

Property field descriptions,

  • 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

  1. Click Add Statement link shown in figure 2 and it'll open up the screen where you could enter SQL statement option. This is shown in figure 3.
  2. More than one statement may be specified.
  • The SQL statement may specify parameters which could be specified as values or XPath expressions.
  • The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored.

Figure 5: Adding SQL statements

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 sample demonstrate simple database write operations. The dbreport mediator writes to a table using the message details. It works the same 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.