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

Versions Compared

Old Version 5

changes.mady.by.user Jackie Wheeler

Saved on

compared with

New Version 6

changes.mady.by.user Rukshani Weerasinha

Saved on

Key

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

The DBReport DB Report Mediator is very similar to the DBLookup Mediator, but writes . The difference between the two mediators is that the DB Report mediator writes information to a Database, database using the specified insert SQL statement.

Info

The DB Report mediator is a content-aware mediator.

...

Table of Contents
maxLevel3
minLevel3
styleborder:1
locationtop
typeflat
separatorpipe

...

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.

Localtab Group
Localtab
activetrue
titleConnection Pool
Code Block
XML
XML
<dbreport>
<DB Report>
   <connection>
     <pool>
      (
        <driver/>
        <url/>
        <user/>
        <password/>

        <dsName/>
        <icClass/>
        <url/>
        <user/>
        <password/>
      )
        <property name="name" value="value"/>*
     </pool>
   </connection>
   <statement>
<sql>insert
<sql>select something
into
from
something values(?, ?, ?, ?)
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"/>*
      <result name="string" column="int|string"/>*
   </statement>+
</
dblreport>

UI Configuration

Connection Information of the DBReport Mediator can be chosen as:

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

...

Image Removed

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

...

DBLookup>
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>
   <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"/>*
      <result name="string" column="int|string"/>*
   </statement>+
</DB Report>
Localtab
titleCarbon Datasource
Code Block
XML
XML
<DB Report>
   <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"/>*
      <result name="string" column="int|string"/>*
   </statement>+
</DB 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.

Localtab Group
Localtab
titlePool

The following UI is displayed when you select the Pool option for the Connection Information parameter, indicating that you want the connection to be made via a connection pool.

Image Added

The parameters available to configure the DB Report mediator are as follows.

Parameter NameDescription
Use TransactionThis parameter specifies whether the database operation should be performed within a transaction or not. Click Yes or No as relevant.
DriverThe class name of the database driver.
UrlThe JDBC URL of the database that data will be

...

written to.
User

...

The user name used to connect to the database.
Password

...

The password used to connect to the database

...

Data Source Type - External

Image Removed

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.

...

Data Source Type - Carbon Datasource

Image Removed

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

    Info
    titleTip

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

...

Image Removed

Property field descriptions:

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

...

Name

...

Value

...

autocommit

...

true / false

.
Adding properties to the DB Report mediator

If you click Add Property, the page will expand to display the following parameters.

Image Added

The parameters available to manage properties are as follows.

Parameter NameDescription
NameThe name of the property.
ValueThe value of the property.
ActionThis parameter enables a property to be deleted.

The available properties are as follows.

Name

Value

Description

autocommit

true / false

The auto-commit state of the connections created by the pool.

isolation

Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE

The isolation state of the connections created by the pool.

initialsize

int

The initial number of connections created when the pool is started.

maxactive

int

The maximum number of active connections that can be allocated from this pool at a given time. When this maximum limit is reached, no more active connections will be created by the connection pool. Specify 0 or a negative value if you do not want to set a limit.

maxidle

int

The maximum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.

maxopenstatements

int

The maximum number of open statements that can be allocated from the statement pool at a given time. When this maximum limit is reached, no more new statements will be created by the statement pool. Specify 0 or a negative value if you do not want to set a limit.

maxwait

long

The maximum number of milliseconds that the connection pool will wait for a connection to return before throwing an exception when there are no connections available in the pool. Specify 0 or a negative value if you want the pool to wait indefinitely.

minidle

int

The minimum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.

poolstatements

true/ false

If the value is true, statement pooling is enabled for the pool.

testonborrow

true/ false

If the value is true, objects are validated before they are borrowed from the pool. An object which fails the validation test will be dropped from the pool and another object in the pool will be picked instead.

testwhileidle

true/ false

If the value is true, the objects in the pool will be validated using an idle object evictor (if any exists). Any object which fails this validation test would be dropped from the pool.

validationquery

String

The SQL query that will be used to validate connections from this pool before returning them to the caller.
Localtab
titleData Source

The UI configuration 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 UI configuration.

Localtab Group
Localtab
titleExternal

The following UI is displayed if you select the External option for the Datasource Type parameter, indicating that you want the connection to the database to be made using an external datasource.

Image Added

The parameters available to configure the DB Report mediator are as follows.

Parameter NameDescription
Use TransactionThis parameter specifies whether the database operation should be performed within a transaction or not. Click Yes or No as relevant.
Initial ContextThe initial context factory class. The corresponding Java environment property is java.naming.factory.initial.
Datasource NameThe naming service provider URL . The corresponding Java environment property is java.naming.provider.url.
URLThe JDBC URL of the database that data will be written to.
UserThe user name used to connect to the database.
PasswordThe password used to connect to the database.

 

Adding properties to the DB Report mediator

If you click Add Property, the page will expand to display the following parameters.

Image Added

The parameters available to manage properties are as follows.

Parameter NameDescription
NameThe name of the property.
ValueThe value of the property.
ActionThis parameter enables a property to be deleted.

 

The available properties are as follows.

Name

Value

Description

autocommit

true / false

The auto-commit state of the connections created by the pool.

isolation

Connection.TRANSACTION_NONE / Connection.TRANSACTION_READ_COMMITTED / Connection.TRANSACTION_READ_UNCOMMITTED / Connection.TRANSACTION_REPEATABLE_READ / Connection.TRANSACTION_SERIALIZABLE

The isolation state of the connections created by the pool.

initialsize

int

The initial number of connections created when the pool is started.

maxactive

int

The maximum number of active connections that can be allocated from this pool at a given time. When this maximum limit is reached, no more active connections will be created by the connection pool. Specify 0 or a negative value if you do not want to set a limit.

maxidle

int

The maximum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.

maxopenstatements

int

The maximum number of open statements that can be allocated from the statement pool at a given time. When this maximum limit is reached, no more new statements will be created by the statement pool. Specify 0 or a negative value if you do not want to set a limit.

maxwait

long

The maximum number of milliseconds that the connection pool will wait for a connection to return before throwing an exception when there are no connections available in the pool. Specify 0 or a negative value if you want the pool to wait indefinitely.

minidle

int

The minimum number of idle connections to be allowed in the connection pool at a given time. Specify 0 or a negative value if you want the pool to wait indefinitely.

poolstatements

...

true/ false

If the value is true, statement pooling is enabled for the pool.

testonborrow

...

true/ false

...

testwhileidle

...

true / false

If the value is true, objects are validated before they are borrowed from the pool. An object which fails the validation test will be dropped from the pool and another object in the pool will be picked instead.

testwhileidle

true/ false

If the value is true, the objects in the pool will be validated using an idle object evictor (if any exists). Any object which fails this validation test would be dropped from the pool.

validationquery

String

...

Image Removed

Info
titleTip

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.

...

The SQL query that will be used to validate connections from this pool before returning them to the caller.
Localtab
titleCarbon Datasource

The following UI is displayed if you select the Carbon Datasource option for the Datasource Type parameter, indicating that you want the connection to the database to be made using an Carbon datasource.

Image Added

 

Parameter NameDescription
Use TransactionThis parameter specifies whether the database operation should be performed within a transaction or not. Click Yes or No as relevant.
DatasourceThis parameter is used to selected a specific Carbon datasource you want to use to make the connection. All the Carbon datasources which are currently available are included in the list.

Adding SQL statements to the DB Report Mediator

If you click  Add Statement , the page will be expanded to display the following parameters.

Image Added

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

Tip

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.

...

Action

...

This allows you to delete a parameter.
Info
titleNote

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

Image Modified

 

...

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.

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

...

DB Report>

Samples

For more examples of the DB Report mediator, see:

Excerpt
hiddentrue

Description of the DBReport DB Report Mediator in WSO2 ESB.