<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
      "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
  <title>WSO2 ESB - DBReport Mediator </title>
  <link href="css/esb-docs.css" rel="stylesheet" />
  <link href="styles/dist-docs.css" rel="stylesheet" type="text/css"
  media="all" />
</head>

<body>
<h2>DBReport Mediator</h2>

<p>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 <a
href="dblookup.html">dblookup mediator guide</a></p>

<h3>Syntax</h3>
<pre xml:space="preserve">&lt;dbreport&gt;
   &lt;connection&gt;
     &lt;pool&gt;
      (
       &lt;driver/&gt;
       &lt;url/&gt;
       &lt;user/&gt;
       &lt;password/&gt;
     |
       &lt;dsName/&gt;
       &lt;icClass/&gt;
       &lt;url/&gt;
       &lt;user/&gt;
       &lt;password/&gt;
     )
       &lt;property name="name" value="value"/&gt;*
     &lt;/pool&gt;
   &lt;/connection&gt;
   &lt;statement&gt;
     &lt;sql&gt;insert into something values(?, ?, ?, ?)&lt;/sql&gt;
     &lt;parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/&gt;*
    &lt;/statement&gt;+
&lt;/dblreport&gt;</pre>

<h3>UI Configuration</h3>

<div class="image">
<img src="../dbreport-mediator/docs/images/dbreport_mediator_screenshot.png" alt="" />

<p>Figure 1: DBReport options </p>
</div>

<p>DBReport mediator options field descriptions, </p>
<ul>
  <li>Connection Information - Specify whether the connection is taken from a
    connection pool or from a data source </li>
  <li>Driver - Database driver</li>
  <li>Url - JDBC URL of the database that data will be looked up.</li>
  <li>User - Username that's used to connect to the database.</li>
  <li>Password - Password used to connect to the database. </li>
</ul>

<h3>Data sources</h3>

<h4>Carbon Datasources</h4>

<p>If existing data source is choosen, clicking "Load Data Sources" will give
the available datasource. Then a data source can be selected.</p>
<div class="image">
<img src="../dbreport-mediator/docs/images/ds.png" alt="" />
<p>Figure 2: Carbon Datasources</p>
</div>


<h4>External Datasources</h4>

<p>Like in Pool connection data source configuration properties should be
specified.</p>
<div class="image">
<img src="../dbreport-mediator/docs/images/ds-external.png" alt="" />
<p>Figure 3: External Datasources</p>
</div>


<h4>Adding properties</h4>

<p>Properties can be added to the DBReport Mediator by clicking Add Property, </p>

<div class="image">
<img src="../dbreport-mediator/docs/images/dbreport_property.png" alt="" />
<p>Figure 4: Adding a property </p>
</div>

<p>Property field descriptions,</p>
<ul>
  <li>autocommit = true | false </li>
  <li>isolation = Connection.TRANSACTION_NONE |
    Connection.TRANSACTION_READ_COMMITTED |
    Connection.TRANSACTION_READ_UNCOMMITTED |
    Connection.TRANSACTION_REPEATABLE_READ |
    Connection.TRANSACTION_SERIALIZABLE </li>
  <li>initialsize = int </li>
  <li>maxactive = int </li>
  <li>maxidle = int </li>
  <li>maxopenstatements = int </li>
  <li>maxwait = long </li>
  <li>minidle = int </li>
  <li>poolstatements = true | false </li>
  <li>testonborrow = true | false </li>
  <li>testwhileidle = true | false </li>
  <li>validationquery = String </li>
</ul>

<h4>Adding SQL statements</h4>
<ol>
  <li>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. </li>
  <li>More than one statement may be specified.</li>
</ol>
<ul>
  <li>The SQL statement may specify parameters which could be specified as
    values or XPath expressions. </li>
  <li>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. </li>
</ul>

<div class="image">
<img src="../dbreport-mediator/docs/images/dbreport_sql.png" alt="" />

<p>Figure 5: Adding SQL statements </p>
</div>

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

<p>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.
</p>



<p></p>
</body>
</html>
