This documentation is for WSO2 Enterprise Service Bus version 4.9.0 . View documentation for the latest release.

All docs This doc

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Table of Contents
location
maxLevel3
minLevel3
locationtop
styleborder:1
toptypeflat
separatorpipe

...

Syntax

...

Localtab Group
Localtab
activetrue
titleConnection Pool
Code Block
XML
XML
<DB<dbreport>
Report>    <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>+
</DBLookup>dbreport>
Localtab
titleData source

The syntax of the DBLookup mediator further differs based on whether the connection to the database is made using an external datasource or a Carbon datasource. Click on the relevant tab to view the required syntax.

Localtab Group
Localtab
titleExternal Datasource
Code Block
XML
XML
<DB Report><dbreport>
   <connection>
      <pool>
        <dsName/>
        <icClass/>
        <url/>
        <user/>
        <password/>
        <property name="name" value="value"/>*
      </pool>
   </connection>
   <statement>
      <sql>select something from table where something_else = ?</sql>
      <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
   </statement>+
</DBdbreport>
Report>
Localtab
titleCarbon Datasource
Code Block
XML
XML
<DB Report><dbreport>
   <connection>
      <pool>
        <dsName/>
      </pool>
   </connection>
   <statement>
      <sql>select something from table where something_else = ?</sql>
      <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
   </statement>+
</DBdbreport>
Report>

 

...

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.

...

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.

Code Block
XML
XML
<DB<dbreport Report 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>
</DB Report>dbreport>

Samples

For more examples of the DB Report mediator, see:

...